1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
3
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
4
<title>File Locking And Concurrency In SQLite Version 3</title>
5
<style type="text/css">
8
font-family: Verdana, sans-serif;
13
a:visited { color: #734559 }
15
.logo { position:absolute; margin:3px; }
31
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
32
.toolbar a:visited { color: white; }
33
.toolbar a:hover { color: #044a64; background: white; }
35
.content { margin: 5%; }
36
.content dt { font-weight:bold; }
37
.content dd { margin-bottom: 25px; margin-left:20%; }
38
.content ul { padding:0px; padding-left: 15px; margin:0px; }
41
.se { background: url(images/se.gif) 100% 100% no-repeat #044a64}
42
.sw { background: url(images/sw.gif) 0% 100% no-repeat }
43
.ne { background: url(images/ne.gif) 100% 0% no-repeat }
44
.nw { background: url(images/nw.gif) 0% 0% no-repeat }
46
/* Things for "fancyformat" documents start here. */
47
.fancy img+p {font-style:italic}
48
.fancy .codeblock i { color: darkblue; }
49
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
50
.fancy h2 { margin-left: 10px }
51
.fancy h3 { margin-left: 20px }
52
.fancy h4 { margin-left: 30px }
53
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
54
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
55
.fancy #toc a { color: darkblue ; text-decoration: none }
56
.fancy .todo { color: #AA3333 ; font-style : italic }
57
.fancy .todo:before { content: 'TODO:' }
58
.fancy p.todo { border: solid #AA3333 1px; padding: 1ex }
59
.fancy img { display:block; }
60
.fancy :link:hover, .fancy :visited:hover { background: wheat }
61
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
62
.fancy li p { margin: 1em 0 }
63
/* End of "fancyformat" specific rules. */
69
<div><!-- container div to satisfy validator -->
72
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
74
<div><!-- IE hack to prevent disappearing logo--></div>
75
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
77
<table width=100% style="clear:both"><tr><td>
78
<div class="se"><div class="sw"><div class="ne"><div class="nw">
79
<table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
82
<a href="about.html">About</a>
83
<a href="sitemap.html">Sitemap</a>
84
<a href="docs.html">Documentation</a>
85
<a href="download.html">Download</a>
86
<a href="copyright.html">License</a>
87
<a href="news.html">News</a>
88
<a href="support.html">Support</a>
91
gMsg = "Search SQLite Docs..."
92
function entersearch() {
93
var q = document.getElementById("q");
94
if( q.value == gMsg ) { q.value = "" }
95
q.style.color = "black"
96
q.style.fontStyle = "normal"
98
function leavesearch() {
99
var q = document.getElementById("q");
100
if( q.value == "" ) {
102
q.style.color = "#044a64"
103
q.style.fontStyle = "italic"
108
<div style="padding:0 1em 0px 0;white-space:nowrap">
109
<form name=f method="GET" action="http://www.sqlite.org/search">
110
<input id=q name=q type=text
111
onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
112
<input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
116
</div></div></div></div>
118
<div class=startsearch></div>
122
<h2>1.0 File Locking And Concurrency In SQLite Version 3</h2>
124
<p>SQLite <a href="releaselog/3_0_0.html">Version 3.0.0</a> introduced a new locking and journaling
125
mechanism designed to improve concurrency over SQLite version 2
126
and to reduce the writer starvation
127
problem. The new mechanism also allows atomic commits of transactions
128
involving multiple database files.
129
This document describes the new locking mechanism.
130
The intended audience is programmers who want to understand and/or modify
131
the pager code and reviewers working to verify the design
135
<p>This document was originally created in early 2004 when SQLite version 2
136
was still in widespread use and was written to introduce
137
the new concepts of SQLite version 3 to readers who were already familiar
138
with SQLite version 2. But these days, most readers of this document have
139
probably never seen SQLite version 2 and are only familiar with SQLite
140
version 3. Nevertheless, this document continues to serve as an
141
authoritative reference to how database file locking works in SQLite
144
<a name="overview"></a>
145
<h2>2.0 Overview</h2>
148
Locking and concurrency control are handled by the
149
<a href="http://www.sqlite.org/src/finfo?name=src/pager.c">
151
The pager module is responsible for making SQLite "ACID" (Atomic,
152
Consistent, Isolated, and Durable). The pager module makes sure changes
153
happen all at once, that either all changes occur or none of them do,
154
that two or more processes do not try to access the database
155
in incompatible ways at the same time, and that once changes have been
156
written they persist until explicitly deleted. The pager also provides
157
a memory cache of some of the contents of the disk file.</p>
159
<p>The pager is unconcerned
160
with the details of B-Trees, text encodings, indices, and so forth.
161
From the point of view of the pager the database consists of
162
a single file of uniform-sized blocks. Each block is called a
163
"page" and is usually 1024 bytes in size. The pages are numbered
164
beginning with 1. So the first 1024 bytes of the database are called
165
"page 1" and the second 1024 bytes are call "page 2" and so forth. All
166
other encoding details are handled by higher layers of the library.
167
The pager communicates with the operating system using one of several
170
<a href="http://www.sqlite.org/src/finfo?name=src/os_unix.c">
172
<a href="http://www.sqlite.org/src/finfo?name=src/os_win.c">
174
that provides a uniform abstraction for operating system services.
177
<p>The pager module effectively controls access for separate threads, or
178
separate processes, or both. Throughout this document whenever the
179
word "process" is written you may substitute the word "thread" without
180
changing the truth of the statement.</p>
182
<a name="locking"></a>
186
From the point of view of a single process, a database file
187
can be in one of five locking states:
191
<table cellpadding="20">
192
<tr><td valign="top">UNLOCKED</td>
194
No locks are held on the database. The database may be neither read nor
195
written. Any internally cached data is considered suspect and subject to
196
verification against the database file before being used. Other
197
processes can read or write the database as their own locking states
198
permit. This is the default state.
201
<tr><td valign="top">SHARED</td>
203
<a name="shared_lock"></a>
205
The database may be read but not written. Any number of
206
processes can hold SHARED locks at the same time, hence there can be
207
many simultaneous readers. But no other thread or process is allowed
208
to write to the database file while one or more SHARED locks are active.
211
<tr><td valign="top">RESERVED</td>
213
<a name="reserved_lock"></a>
215
A RESERVED lock means that the process is planning on writing to the
216
database file at some point in the future but that it is currently just
217
reading from the file. Only a single RESERVED lock may be active at one
218
time, though multiple SHARED locks can coexist with a single RESERVED lock.
219
RESERVED differs from PENDING in that new SHARED locks can be acquired
220
while there is a RESERVED lock.
223
<tr><td valign="top">PENDING</td>
225
<a name="pending_lock"></a>
227
A PENDING lock means that the process holding the lock wants to write
228
to the database as soon as possible and is just waiting on all current
229
SHARED locks to clear so that it can get an EXCLUSIVE lock. No new
230
SHARED locks are permitted against the database if
231
a PENDING lock is active, though existing SHARED locks are allowed to
235
<tr><td valign="top">EXCLUSIVE</td>
237
<a name="excl_lock"></a>
239
An EXCLUSIVE lock is needed in order to write to the database file.
240
Only one EXCLUSIVE lock is allowed on the file and no other locks of
241
any kind are allowed to coexist with an EXCLUSIVE lock. In order to
242
maximize concurrency, SQLite works to minimize the amount of time that
243
EXCLUSIVE locks are held.
249
The operating system interface layer understands and tracks all five
250
locking states described above.
251
The pager module only tracks four of the five locking states.
252
A PENDING lock is always just a temporary
253
stepping stone on the path to an EXCLUSIVE lock and so the pager module
254
does not track PENDING locks.
257
<a name="rollback"></a>
258
<h2>4.0 The Rollback Journal</h2>
260
<p>When a process wants to change a database file (and it is not
261
in <a href="wal.html">WAL</a> mode), it
262
first records the original unchanged database content
263
in a <em>rollback journal</em>. The rollback journal is an ordinary
264
disk file that is always located
265
in the same directory or folder as the database file and has the
266
same name as the database file with the addition of a <tt>-journal</tt>
267
suffix. The rollback journal also records the initial
268
size of the database so that if the database file grows it can be truncated
269
back to its original size on a rollback.</p>
271
<p>If SQLite is working with multiple databases at the same time
272
(using the <a href="lang_attach.html">ATTACH</a> command) then each database has its own rollback journal.
273
But there is also a separate aggregate journal
274
called the <em>master journal</em>.
275
The master journal does not contain page data used for rolling back
276
changes. Instead the master journal contains the names of the
277
individual database rollback journals for each of the ATTACHed databases.
278
Each of the individual database rollback journals also contain the name
279
of the master journal.
280
If there are no ATTACHed databases (or if none of the ATTACHed database
281
is participating in the current transaction) no master journal is
282
created and the normal rollback journal contains an empty string
283
in the place normally reserved for recording the name of the master
286
<a name="hotjrnl"></a>
288
<p>A rollback journal is said to be <em>hot</em>
289
if it needs to be rolled back
290
in order to restore the integrity of its database.
291
A hot journal is created when a process is in the middle of a database
292
update and a program or operating system crash or power failure prevents
293
the update from completing.
294
Hot journals are an exception condition.
295
Hot journals exist to recover from crashes and power failures.
296
If everything is working correctly
297
(that is, if there are no crashes or power failures)
298
you will never get a hot journal.
302
If no master journal is involved, then
303
a journal is hot if it exists and has a non-zero header
304
and its corresponding database file
305
does not have a RESERVED lock.
306
If a master journal is named in the file journal, then the file journal
307
is hot if its master journal exists and there is no RESERVED
308
lock on the corresponding database file.
309
It is important to understand when a journal is hot so the
310
preceding rules will be repeated in bullets:
314
<li>A journal is hot if...
316
<li>It exists, and</li>
317
<li>Its size is greater than 512 bytes, and</li>
318
<li>The journal header is non-zero and well-formed, and</li>
319
<li>Its master journal exists or the master journal name is an
320
empty string, and</li>
321
<li>There is no RESERVED lock on the corresponding database file.</li>
326
<a name="hot_journals"></a>
327
<h3>4.1 Dealing with hot journals</h3>
330
Before reading from a database file, SQLite always checks to see if that
331
database file has a hot journal. If the file does have a hot journal, then
332
the journal is rolled back before the file is read. In this way, we ensure
333
that the database file is in a consistent state before it is read.
336
<p>When a process wants to read from a database file, it followed
337
the following sequence of steps:
341
<li>Open the database file and obtain a SHARED lock. If the SHARED lock
342
cannot be obtained, fail immediately and return SQLITE_BUSY.</li>
343
<li>Check to see if the database file has a hot journal. If the file
344
does not have a hot journal, we are done. Return immediately.
345
If there is a hot journal, that journal must be rolled back by
346
the subsequent steps of this algorithm.</li>
347
<li>Acquire a PENDING lock then an EXCLUSIVE lock on the database file.
348
(Note: Do not acquire a RESERVED lock because that would make
349
other processes think the journal was no longer hot.) If we
350
fail to acquire these locks it means another process
351
is already trying to do the rollback. In that case,
352
drop all locks, close the database, and return SQLITE_BUSY. </li>
353
<li>Read the journal file and roll back the changes.</li>
354
<li>Wait for the rolled back changes to be written onto
355
persistent storage. This protects the integrity of the database
356
in case another power failure or crash occurs.</li>
357
<li>Delete the journal file (or truncate the journal to zero bytes in
358
length if <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=TRUNCATE</a> is
359
set, or zero the journal header if
360
<a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=PERSIST</a> is set).</li>
361
<li>Delete the master journal file if it is safe to do so.
362
This step is optional. It is here only to prevent stale
363
master journals from cluttering up the disk drive.
364
See the discussion below for details.</li>
365
<li>Drop the EXCLUSIVE and PENDING locks but retain the SHARED lock.</li>
368
<p>After the algorithm above completes successfully, it is safe to
369
read from the database file. Once all reading has completed, the
370
SHARED lock is dropped.</p>
372
<a name="stale_master_journals"></a>
373
<h3>4.2 Deleting stale master journals</h3>
375
<p>A stale master journal is a master journal that is no longer being
376
used for anything. There is no requirement that stale master journals
377
be deleted. The only reason for doing so is to free up disk space.</p>
379
<p>A master journal is stale if no individual file journals are pointing
380
to it. To figure out if a master journal is stale, we first read the
381
master journal to obtain the names of all of its file journals. Then
382
we check each of those file journals. If any of the file journals named
383
in the master journal exists and points back to the master journal, then
384
the master journal is not stale. If all file journals are either missing
385
or refer to other master journals or no master journal at all, then the
386
master journal we are testing is stale and can be safely deleted.</p>
388
<a name="writing"></a>
389
<h2>5.0 Writing to a database file</h2>
391
<p>To write to a database, a process must first acquire a SHARED lock
392
as described above (possibly rolling back incomplete changes if there
394
After a SHARED lock is obtained, a RESERVED lock must be acquired.
395
The RESERVED lock signals that the process intends to write to the
396
database at some point in the future. Only one process at a time
397
can hold a RESERVED lock. But other processes can continue to read
398
the database while the RESERVED lock is held.
401
<p>If the process that wants to write is unable to obtain a RESERVED
402
lock, it must mean that another process already has a RESERVED lock.
403
In that case, the write attempt fails and returns SQLITE_BUSY.</p>
405
<p>After obtaining a RESERVED lock, the process that wants to write
406
creates a rollback journal. The header of the journal is initialized
407
with the original size of the database file. Space in the journal header
408
is also reserved for a master journal name, though the master journal
409
name is initially empty.</p>
411
<p>Before making changes to any page of the database, the process writes
412
the original content of that page into the rollback journal. Changes
413
to pages are held in memory at first and are not written to the disk.
414
The original database file remains unaltered, which means that other
415
processes can continue to read the database.</p>
417
<p>Eventually, the writing process will want to update the database
418
file, either because its memory cache has filled up or because it is
419
ready to commit its changes. Before this happens, the writer must
420
make sure no other process is reading the database and that the rollback
421
journal data is safely on the disk surface so that it can be used to
422
rollback incomplete changes in the event of a power failure.
423
The steps are as follows:</p>
426
<li>Make sure all rollback journal data has actually been written to
427
the surface of the disk (and is not just being held in the operating
428
system's or disk controllers cache) so that if a power failure occurs
429
the data will still be there after power is restored.</li>
430
<li>Obtain a PENDING lock and then an EXCLUSIVE lock on the database file.
431
If other processes still have SHARED locks, the writer might have
432
to wait until those SHARED locks clear before it is able to obtain
433
an EXCLUSIVE lock.</li>
434
<li>Write all page modifications currently held in memory out to the
435
original database disk file.</li>
439
If the reason for writing to the database file is because the memory
440
cache was full, then the writer will not commit right away. Instead,
441
the writer might continue to make changes to other pages. Before
442
subsequent changes are written to the database file, the rollback
443
journal must be flushed to disk again. Note also that the EXCLUSIVE
444
lock that the writer obtained in order to write to the database initially
445
must be held until all changes are committed. That means that no other
446
processes are able to access the database from the
447
time the memory cache first spills to disk until the transaction
452
When a writer is ready to commit its changes, it executes the following
458
Obtain an EXCLUSIVE lock on the database file and
459
make sure all memory changes have been written to the database file
460
using the algorithm of steps 1-3 above.</li>
461
<li>Flush all database file changes to the disk. Wait for those changes
462
to actually be written onto the disk surface.</li>
463
<li>Delete the journal file. (Or if the <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode</a> is TRUNCATE or
464
PERSIST, truncate the journal file or zero the header of the journal file,
465
respectively.) This is the instant when the changes are
466
committed. Prior to deleting the journal file, if a power failure
467
or crash occurs, the next process to open the database will see that
468
it has a hot journal and will roll the changes back.
469
After the journal is deleted, there will no longer be a hot journal
470
and the changes will persist.
472
<li>Drop the EXCLUSIVE and PENDING locks from the database file.
476
<p>As soon as the PENDING lock is released from the database file, other
477
processes can begin reading the database again. In the current implementation,
478
the RESERVED lock is also released, but that is not essential. Future
479
versions of SQLite might provide a "CHECKPOINT" SQL command that will
480
commit all changes made so far within a transaction but retain the
481
RESERVED lock so that additional changes can be made without given
482
any other process an opportunity to write.</p>
484
<p>If a transaction involves multiple databases, then a more complex
485
commit sequence is used, as follows:</p>
489
Make sure all individual database files have an EXCLUSIVE lock and a
491
<li>Create a master-journal. The name of the master-journal is arbitrary.
492
(The current implementation appends random suffixes to the name of the
493
main database file until it finds a name that does not previously exist.)
494
Fill the master journal with the names of all the individual journals
495
and flush its contents to disk.
496
<li>Write the name of the master journal into
497
all individual journals (in space set aside for that purpose in the
498
headers of the individual journals) and flush the contents of the
499
individual journals to disk and wait for those changes to reach the
501
<li>Flush all database file changes to the disk. Wait for those changes
502
to actually be written onto the disk surface.</li>
503
<li>Delete the master journal file. This is the instant when the changes are
504
committed. Prior to deleting the master journal file, if a power failure
505
or crash occurs, the individual file journals will be considered hot
506
and will be rolled back by the next process that
507
attempts to read them. After the master journal has been deleted,
508
the file journals will no longer be considered hot and the changes
511
<li>Delete all individual journal files.
512
<li>Drop the EXCLUSIVE and PENDING locks from all database files.
516
<a name="writer_starvation"></a>
517
<h3>5.1 Writer starvation</h3>
519
<p>In SQLite version 2, if many processes are reading from the database,
520
it might be the case that there is never a time when there are
521
no active readers. And if there is always at least one read lock on the
522
database, no process would ever be able to make changes to the database
523
because it would be impossible to acquire a write lock. This situation
524
is called <em>writer starvation</em>.</p>
526
<p>SQLite version 3 seeks to avoid writer starvation through the use of
527
the PENDING lock. The PENDING lock allows existing readers to continue
528
but prevents new readers from connecting to the database. So when a
529
process wants to write a busy database, it can set a PENDING lock which
530
will prevent new readers from coming in. Assuming existing readers do
531
eventually complete, all SHARED locks will eventually clear and the
532
writer will be given a chance to make its changes.</p>
534
<a name="how_to_corrupt"></a>
535
<h2>6.0 How To Corrupt Your Database Files</h2>
537
<p>The pager module is very robust but it can be subverted.
538
This section attempts to identify and explain the risks.
539
(See also the <a href="atomiccommit.html#sect_9_0">Things That Can Go Wrong</a> section of the article
540
on <a href="atomiccommit.html">Atomic Commit</a>.</p>
543
Clearly, a hardware or operating system fault that introduces incorrect data
544
into the middle of the database file or journal will cause problems.
546
if a rogue process opens a database file or journal and writes malformed
547
data into the middle of it, then the database will become corrupt.
548
There is not much that can be done about these kinds of problems
549
so they are given no further attention.
553
SQLite uses POSIX advisory locks to implement locking on Unix. On
554
Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system
555
calls. SQLite assumes that these system calls all work as advertised. If
556
that is not the case, then database corruption can result. One should
557
note that POSIX advisory locking is known to be buggy or even unimplemented
558
on many NFS implementations (including recent versions of Mac OS X)
559
and that there are reports of locking problems
560
for network filesystems under Windows. Your best defense is to not
561
use SQLite for files on a network filesystem.
565
SQLite uses the fsync() system call to flush data to the disk under Unix and
566
it uses the FlushFileBuffers() to do the same under Windows. Once again,
567
SQLite assumes that these operating system services function as advertised.
568
But it has been reported that fsync() and FlushFileBuffers() do not always
569
work correctly, especially with inexpensive IDE disks. Apparently some
570
manufactures of IDE disks have controller chips that report
571
that data has reached the disk surface when in fact the data is still
572
in volatile cache memory in the disk drive electronics. There are also
573
reports that Windows sometimes chooses to ignore FlushFileBuffers() for
574
unspecified reasons. The author cannot verify any of these reports.
575
But if they are true, it means that database corruption is a possibility
576
following an unexpected power loss. These are hardware and/or operating
577
system bugs that SQLite is unable to defend against.
580
<a name="ext3-barrier-problem"></a>
582
<p>If a Linux <a href="http://en.wikipedia.org/wiki/Ext3">ext3</a>
583
filesystem is mounted without the "barrier=1" option
584
in the <a href="http://en.wikipedia.org/wiki/fstab">/etc/fstab</a>
585
and the disk drive write cache is enabled
586
then filesystem corruption can occur following a power loss or OS crash.
587
Whether or not corruption can occur depends on the details of the disk control
588
hardware; corruption is more likely with inexpensive consumer-grade disks
589
and less of a problem for enterprise-class storage devices with advanced
590
features such as non-volatile write caches.
592
<a href="http://www.redhat.com/archives/ext3-users/2010-July/msg00001.html">
593
confirm this behavior</a>.
594
We are told that most Linux distributions do not use barrier=1 and do
595
not disable the write cache so most
596
Linux distributions are vulnerable to this problem. Note that this is an
597
operating system and hardware issue and that there is nothing that SQLite
598
can do to work around it.
599
<a href="http://ozlabs.org/~rusty/index.cgi/tech/2009-10-20.html">
600
Other database engines</a> have also run into this same problem.</p>
603
If a crash or power failure occurs and results in a hot journal but that
604
journal is deleted, the next process to open the database will not
605
know that it contains changes that need to be rolled back. The rollback
606
will not occur and the database will be left in an inconsistent state.
607
Rollback journals might be deleted for any number of reasons:
611
<li>An administrator might be cleaning up after an OS crash or power failure,
612
see the journal file, think it is junk, and delete it.</li>
613
<li>Someone (or some process) might rename the database file but fail to
614
also rename its associated journal.</li>
615
<li>If the database file has aliases (hard or soft links) and the file
616
is opened by a different alias than the one used to create the journal,
617
then the journal will not be found. To avoid this problem, you should
618
not create links to SQLite database files.</li>
619
<li>Filesystem corruption following a power failure might cause the
620
journal to be renamed or deleted.</li>
624
The last (fourth) bullet above merits additional comment. When SQLite creates
625
a journal file on Unix, it opens the directory that contains that file and
626
calls fsync() on the directory, in an effort to push the directory information
627
to disk. But suppose some other process is adding or removing unrelated
628
files to the directory that contains the database and journal at the
629
moment of a power failure. The supposedly unrelated actions of this other
630
process might result in the journal file being dropped from the directory and
631
moved into "lost+found". This is an unlikely scenario, but it could happen.
632
The best defenses are to use a journaling filesystem or to keep the
633
database and journal in a directory by themselves.
637
For a commit involving multiple databases and a master journal, if the
638
various databases were on different disk volumes and a power failure occurs
639
during the commit, then when the machine comes back up the disks might
640
be remounted with different names. Or some disks might not be mounted
641
at all. When this happens the individual file journals and the master
642
journal might not be able to find each other. The worst outcome from
643
this scenario is that the commit ceases to be atomic.
644
Some databases might be rolled back and others might not.
645
All databases will continue to be self-consistent.
646
To defend against this problem, keep all databases
647
on the same disk volume and/or remount disks using exactly the same names
648
after a power failure.
651
<a name="transaction_control"></a>
652
<h2>7.0 Transaction Control At The SQL Level</h2>
655
The changes to locking and concurrency control in SQLite version 3 also
656
introduce some subtle changes in the way transactions work at the SQL
658
By default, SQLite version 3 operates in <em>autocommit</em> mode.
660
all changes to the database are committed as soon as all operations associated
661
with the current database connection complete.</p>
663
<p>The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword
664
is optional) is used to take SQLite out of autocommit mode.
665
Note that the BEGIN command does not acquire any locks on the database.
666
After a BEGIN command, a SHARED lock will be acquired when the first
667
SELECT statement is executed. A RESERVED lock will be acquired when
668
the first INSERT, UPDATE, or DELETE statement is executed. No EXCLUSIVE
669
lock is acquired until either the memory cache fills up and must
670
be spilled to disk or until the transaction commits. In this way,
671
the system delays blocking read access to the file file until the
672
last possible moment.
675
<p>The SQL command "COMMIT" does not actually commit the changes to
676
disk. It just turns autocommit back on. Then, at the conclusion of
677
the command, the regular autocommit logic takes over and causes the
678
actual commit to disk to occur.
679
The SQL command "ROLLBACK" also operates by turning autocommit back on,
680
but it also sets a flag that tells the autocommit logic to rollback rather
683
<p>If the SQL COMMIT command turns autocommit on and the autocommit logic
684
then tries to commit change but fails because some other process is holding
685
a SHARED lock, then autocommit is turned back off automatically. This
686
allows the user to retry the COMMIT at a later time after the SHARED lock
687
has had an opportunity to clear.</p>
689
<p>If multiple commands are being executed against the same SQLite database
690
connection at the same time, the autocommit is deferred until the very
691
last command completes. For example, if a SELECT statement is being
692
executed, the execution of the command will pause as each row of the
693
result is returned. During this pause other INSERT, UPDATE, or DELETE
694
commands can be executed against other tables in the database. But none
695
of these changes will commit until the original SELECT statement finishes.
added
removed
lockingv3.html
