This page gives an overview of how a transaction works in SQLite.
Many details are omitted in this overview.  This is not intended
to be the ultimate guide to how transactions work in SQLite.
The intent of this page is to give the reader a roadmap for how
transactions work in SQLite so that the code for implementing
transactions can be more easily read and understood.

----
{image: commit-0.gif}
*Initial State*

The diagram at the right shows the initial
state of the system.  All database information
is held on disk.  Nothing is in the operating
sytems's cache.  An since the database has not
yet been opened, nothing has been loading into
user memory either.

{clear}

----
{image: commit-1.gif}
*Step 1*

Before attempting to read the databse, SQLite
obtains a "shared" lock on the database file.
The shared lock insures that no other process
will be writing to the file at the same time our
process is trying to read it.  If another process
were to write to the file at the same time we are
reading it, then we might read some old data together
with some new data and the two would not necessary
be consistent.  The shared lock prevents this.

{clear}

----
{image: commit-2.gif}
*Step 2*

Some subset of the database (3 pages in this example)
is read into user memory.  The operating system also
usually makes copies of these pages in its disk cache.

{clear}

----
{image: commit-3.gif}
*Step 3*

The previous steps show what happens when SQLite only
wants to read information from the database.  Here
we begin the process of trying to write.  The first
thing that occurs is that SQLite obtains a "reserved"
lock on the database file.  The reserved lock does
little more than a "shared" lock.  No other process
can write to the database while we hold a reserved
lock but other processes can continue to read from
the database.  The difference between a shared lock
and a reserved lock is that many processes can hold
a shared lock at once but only a single process at a
time can hold a reserved lock.  Another way of thinking
about a reserved lock is that it signals that the current
process intends to modify the database but has not yet
actually gotten around to doing any modifications so
no other process can being the writing process but
other processes can continue to read for the time
being.

{clear}

----
{image: commit-4.gif}
*Step 4*

Before any changes are made to the database, the original
content of all pages that going to change is written
into a rollback journal file.  The name of the rollback
journal is the same as the name of the original database
with "-journal" appended.  The rollback journal contains
a short header and some other formatting information but
is basically just a list of the content of the pages that
are to be changed.  The rollback journal contains everything
that is needed to restore the database to its original
state if the transaction is aborted.

{clear}

----
{image: commit-5.gif}
*Step 5*

Once the original page content has been written to the
rollback journal, pages of the database in user memory
can be modified.  Notice that no part of the database
has yet been modified on disk or in the OS disk cache.
Also notice that the content of the rollback journal
might yet only be in the operating systems write cache
and not yet reached the disk surface.

{clear}

----
{image: commit-6.gif}
*Step 6*

Before going further, we have to make sure that the
content of the rollback journal has been written to
the disk surface.  This insures that a rollback will
be possible even if there is an unexpected powerloss
during subsequent parts of the commit process.  This
is the first "sync" operation that occurs during commit.

{clear}

----
{image: commit-7.gif}
*Step 7*

We are now ready to write the changes to the database
file.  But before continuing, we first need to obtain
an exclusive lock on the database file.  An exclusive
lock guarantees that no other processes are reading
from the database.  We do not want to write while other
processes are reading since our writes might interfere
with the reads of the other processes.

{clear}

----
{image: commit-8.gif}
*Step 8*

Once an exclusive lock is obtained, the modified pages
of the database can be written.  As with all writes,
the information is normally stored only in the operating
systems write cache and is not immediately written onto
the disk surface.

{clear}

----
{image: commit-9.gif}
*Step 9*

The database changes must be flushed to disk.  Otherwise
the information might be lost if the operating system
crashes or if there is an unexpected power failure.
this is the second "sync" of a commit.

{clear}

----
{image: commit-A.gif}
*Step 10*

After all database file changes have been written to
disk, the rollback journal is deleted.  This is the
moment when the transaction commits.  If a power loss
occurs at any prior point during the transaction, then
the transaction will automatically rollback the next
time the database is accessed.  After the rollback
journal has been deleted, however, the transaction
persists even if power is lost.

{clear}

----
{image: commit-B}
*Step 11*

Finally, the locks on the database file are removed, allowing
other processes to once again read the database.

{clear}

----