Documentation Source Text
Check-in [f64ef462b2d8da4941618850fbd202d1c5d5b1de]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
SHA1 Hash:f64ef462b2d8da4941618850fbd202d1c5d5b1de
Date: 2013-07-12 20:39:10
User: drh
Comment:Fix typos in the isolation document.
Tags And Properties
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/isolation.in

44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
..
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
...
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
...
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
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>







|







 







|







 







|


|







 







|









44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
..
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
...
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
...
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
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 transactions
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 simultaneous 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 transaction 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 transaction 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.
</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>