Documentation Source Text

is the only way that one database connection can see uncommitted changes
on a different database connection.  In all other circumstances, separate
database connections are completely isolated from one another.
</p>

<p>Except in the case of [shared cache] database connections with
[PRAGMA read_uncommitted] turned on, all transactions in SQLite show
"serializable" isolation.  SQLite implements serializable tranactions
by actually serializing the writes.  There can only be a single writer
at a time to an SQLite database.  There can be multiple database connections
open at the same time, and all of those database connections can write
to the database file, but they have to take turns.  SQLite uses locks
to serialization of the writes automatically; this is not something that
the applications using SQLite need to worry with.</p>

transaction is being written to disk.  Only after the transaction is
complete written and synced to disk and commits are the readers allowed
back into the database.  Hence readers never get a chance to see partially
written changes.
</p>

<p>
WAL mode permits simultenous readers and writers.  It can do this because
changes do not overwrite the original database file, but rather go
into the separate write-ahead log file.  That means that readers can continue
to read the old, original, unaltered content from the original database file
at the same time that the writer is appending to the write-ahead log.
In [WAL mode], SQLite exhibits "snapshot isolation".  When a read transaction
starts, that reader continues to see an unchanging "snapshot" of the database
file as it existed at the moment in time when the read transaction started.

X will see the older unmodified entries because Y's changes are all
invisible to X while X is holding a read transaction.  If X wants to see
the changes that Y made, then X must ends its read transaction and
start a new one (by running [COMMIT] followed by another [BEGIN].)
</p>

<p>
Another example: X starts a read tranaction using [BEGIN] and [SELECT], then
Y makes a changes to the database using [UPDATE].  Then X tries to make a
change to the database using [UPDATE].  The attempt by X to escalate its
transaction from a read transaction to a write transation fails with an
[SQLITE_BUSY_SNAPSHOT] error because the snapshot of the database being
viewed by X is no longer the latest version of the database.  If X were
allowed to write, it would fork the history of the database file, which is
something SQLite does not support.  In order for X to write to the database,
it must first release its snapshot (using [ROLLBACK] for example) then
start a new transaction with a subsequent [BEGIN].
</p>

or not the query will see those changes.
</p>

<li><p>
If changes occur on the same database connection after a query 
starts running but before the query completes, then the query might return
a changed row more than once, or it might return a row that was previously
deleted, or not; the behavior is undefined.
</p>

<li><p>
For the purposes of the previous four items, two database connections that 
use the same [shared cache] and which enable [PRAGMA read_uncommitted] are
considered to be the same database connection, not separate database
connections.
</p>
</ol>