Debian Codegraph @

www/lockingv3.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>File Locking And Concurrency In SQLite Version 3</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118
119
120 <font color="#7f001f"><p>
121 <p>This document was originally created in early 2004 when SQLite version 2
122 was still in widespread use and was written to introduce
123 the new concepts of SQLite version 3 to readers who were already familiar
124 with SQLite version 2. But these days, most readers of this document have
125 probably never seen SQLite version 2 and are only familiar with SQLite
126 version 3. Nevertheless, this document continues to serve as an
127 authoritative reference to how database file locking works in SQLite
128 version 3.</p>
129
130 <p>The document only describes locking for the older rollback-mode
131 transaction mechanism. Locking for the newer <a href="wal.html">write-ahead log</a> or <a href="wal.html">WAL mode</a>
132 is described separately.</p>
133 </font>
134
135 <h2>1.0 File Locking And Concurrency In SQLite Version 3</h2>
136
137 <p>SQLite <a href="releaselog/3_0_0.html">Version 3.0.0</a> introduced a new locking and journaling
138 mechanism designed to improve concurrency over SQLite version 2
139 and to reduce the writer starvation
140 problem. The new mechanism also allows atomic commits of transactions
141 involving multiple database files.
142 This document describes the new locking mechanism.
143 The intended audience is programmers who want to understand and/or modify
144 the pager code and reviewers working to verify the design
145 of SQLite version 3.
146 </p>
147
148 <a name="overview"></a>
149 <h2>2.0 Overview</h2>
150
151 <p>
152 Locking and concurrency control are handled by the
153 <a href="https://sqlite.org/src/finfo?name=src/pager.c">
154 pager module</a>.
155 The pager module is responsible for making SQLite "ACID" (Atomic,
156 Consistent, Isolated, and Durable). The pager module makes sure changes
157 happen all at once, that either all changes occur or none of them do,
158 that two or more processes do not try to access the database
159 in incompatible ways at the same time, and that once changes have been
160 written they persist until explicitly deleted. The pager also provides
161 a memory cache of some of the contents of the disk file.</p>
162
163 <p>The pager is unconcerned
164 with the details of B-Trees, text encodings, indices, and so forth.
165 From the point of view of the pager the database consists of
166 a single file of uniform-sized blocks. Each block is called a
167 "page" and is usually 1024 bytes in size. The pages are numbered
168 beginning with 1. So the first 1024 bytes of the database are called
169 "page 1" and the second 1024 bytes are call "page 2" and so forth. All
170 other encoding details are handled by higher layers of the library.
171 The pager communicates with the operating system using one of several
172 modules
173 (Examples:
174 <a href="https://sqlite.org/src/finfo?name=src/os_unix.c">
175 os_unix.c</a>,
176 <a href="https://sqlite.org/src/finfo?name=src/os_win.c">
177 os_win.c</a>)
178 that provides a uniform abstraction for operating system services.
179 </p>
180
181 <p>The pager module effectively controls access for separate threads, or
182 separate processes, or both. Throughout this document whenever the
183 word "process" is written you may substitute the word "thread" without
184 changing the truth of the statement.</p>
185
186 <a name="locking"></a>
187 <h2>3.0 Locking</h2>
188
189 <p>
190 From the point of view of a single process, a database file
191 can be in one of five locking states:
192 </p>
193
194 <p>
195 <table cellpadding="20">
196 <tr><td valign="top">UNLOCKED</td>
197 <td valign="top">
198 No locks are held on the database. The database may be neither read nor
199 written. Any internally cached data is considered suspect and subject to
200 verification against the database file before being used. Other
201 processes can read or write the database as their own locking states
202 permit. This is the default state.
203 </td></tr>
204
205 <tr><td valign="top">SHARED</td>
206 <td valign="top">
207 <a name="shared_lock"></a>
208
209 The database may be read but not written. Any number of
210 processes can hold SHARED locks at the same time, hence there can be
211 many simultaneous readers. But no other thread or process is allowed
212 to write to the database file while one or more SHARED locks are active.
213 </td></tr>
214
215 <tr><td valign="top">RESERVED</td>
216 <td valign="top">
217 <a name="reserved_lock"></a>
218
219 A RESERVED lock means that the process is planning on writing to the
220 database file at some point in the future but that it is currently just
221 reading from the file. Only a single RESERVED lock may be active at one
222 time, though multiple SHARED locks can coexist with a single RESERVED lock.
223 RESERVED differs from PENDING in that new SHARED locks can be acquired
224 while there is a RESERVED lock.
225 </td></tr>
226
227 <tr><td valign="top">PENDING</td>
228 <td valign="top">
229 <a name="pending_lock"></a>
230
231 A PENDING lock means that the process holding the lock wants to write
232 to the database as soon as possible and is just waiting on all current
233 SHARED locks to clear so that it can get an EXCLUSIVE lock. No new
234 SHARED locks are permitted against the database if
235 a PENDING lock is active, though existing SHARED locks are allowed to
236 continue.
237 </td></tr>
238
239 <tr><td valign="top">EXCLUSIVE</td>
240 <td valign="top">
241 <a name="excl_lock"></a>
242
243 An EXCLUSIVE lock is needed in order to write to the database file.
244 Only one EXCLUSIVE lock is allowed on the file and no other locks of
245 any kind are allowed to coexist with an EXCLUSIVE lock. In order to
246 maximize concurrency, SQLite works to minimize the amount of time that
247 EXCLUSIVE locks are held.
248 </td></tr>
249 </table>
250 </p>
251
252 <p>
253 The operating system interface layer understands and tracks all five
254 locking states described above.
255 The pager module only tracks four of the five locking states.
256 A PENDING lock is always just a temporary
257 stepping stone on the path to an EXCLUSIVE lock and so the pager module
258 does not track PENDING locks.
259 </p>
260
261 <a name="rollback"></a>
262 <h2>4.0 The Rollback Journal</h2>
263
264 <p>When a process wants to change a database file (and it is not
265 in <a href="wal.html">WAL</a> mode), it
266 first records the original unchanged database content
267 in a <em>rollback journal</em>. The rollback journal is an ordinary
268 disk file that is always located
269 in the same directory or folder as the database file and has the
270 same name as the database file with the addition of a <tt>-journal</tt>
271 suffix. The rollback journal also records the initial
272 size of the database so that if the database file grows it can be truncated
273 back to its original size on a rollback.</p>
274
275 <p>If SQLite is working with multiple databases at the same time
276 (using the <a href="lang_attach.html">ATTACH</a> command) then each database has its own rollback journal.
277 But there is also a separate aggregate journal
278 called the <em>super-journal</em>.
279 The super-journal does not contain page data used for rolling back
280 changes. Instead the super-journal contains the names of the
281 individual database rollback journals for each of the ATTACHed databases.
282 Each of the individual database rollback journals also contain the name
283 of the super-journal.
284 If there are no ATTACHed databases (or if none of the ATTACHed database
285 is participating in the current transaction) no super-journal is
286 created and the normal rollback journal contains an empty string
287 in the place normally reserved for recording the name of the
288 super-journal.</p>
289
290 <p>A rollback journal is said to be <a href="fileformat2.html#hotjrnl">hot</a>
291 if it needs to be rolled back
292 in order to restore the integrity of its database.
293 A hot journal is created when a process is in the middle of a database
294 update and a program or operating system crash or power failure prevents
295 the update from completing.
296 Hot journals are an exception condition.
297 Hot journals exist to recover from crashes and power failures.
298 If everything is working correctly
299 (that is, if there are no crashes or power failures)
300 you will never get a hot journal.
301 </p>
302
303 <p>
304 If no super-journal is involved, then
305 a journal is hot if it exists and has a non-zero header
306 and its corresponding database file
307 does not have a RESERVED lock.
308 If a super-journal is named in the file journal, then the file journal
309 is hot if its super-journal exists and there is no RESERVED
310 lock on the corresponding database file.
311 It is important to understand when a journal is hot so the
312 preceding rules will be repeated in bullets:
313 </p>
314
315 <ul>
316 <li>A journal is hot if...
317 <ul>
318 <li>It exists, and</li>
319 <li>Its size is greater than 512 bytes, and</li>
320 <li>The journal header is non-zero and well-formed, and</li>
321 <li>Its super-journal exists or the super-journal name is an
322 empty string, and</li>
323 <li>There is no RESERVED lock on the corresponding database file.</li>
324 </ul>
325 </li>
326 </ul>
327
328 <a name="hot_journals"></a>
329 <h3>4.1 Dealing with hot journals</h3>
330
331 <p>
332 Before reading from a database file, SQLite always checks to see if that
333 database file has a hot journal. If the file does have a hot journal, then
334 the journal is rolled back before the file is read. In this way, we ensure
335 that the database file is in a consistent state before it is read.
336 </p>
337
338 <p>When a process wants to read from a database file, it follows
339 the following sequence of steps:
340 </p>
341
342 <ol>
343 <li>Open the database file and obtain a SHARED lock. If the SHARED lock
344 cannot be obtained, fail immediately and return SQLITE_BUSY.</li>
345 <li>Check to see if the database file has a hot journal. If the file
346 does not have a hot journal, we are done. Return immediately.
347 If there is a hot journal, that journal must be rolled back by
348 the subsequent steps of this algorithm.</li>
349 <li>Acquire a PENDING lock then an EXCLUSIVE lock on the database file.
350 (Note: Do not acquire a RESERVED lock because that would make
351 other processes think the journal was no longer hot.) If we
352 fail to acquire these locks it means another process
353 is already trying to do the rollback. In that case,
354 drop all locks, close the database, and return SQLITE_BUSY. </li>
355 <li>Read the journal file and roll back the changes.</li>
356 <li>Wait for the rolled back changes to be written onto
357 persistent storage. This protects the integrity of the database
358 in case another power failure or crash occurs.</li>
359 <li>Delete the journal file (or truncate the journal to zero bytes in
360 length if <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=TRUNCATE</a> is
361 set, or zero the journal header if
362 <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=PERSIST</a> is set).</li>
363 <li>Delete the super-journal file if it is safe to do so.
364 This step is optional. It is here only to prevent stale
365 super-journals from cluttering up the disk drive.
366 See the discussion below for details.</li>
367 <li>Drop the EXCLUSIVE and PENDING locks but retain the SHARED lock.</li>
368 </ol>
369
370 <p>After the algorithm above completes successfully, it is safe to
371 read from the database file. Once all reading has completed, the
372 SHARED lock is dropped.</p>
373
374 <a name="stale_super_journals"></a>
375 <h3>4.2 Deleting stale super-journals</h3>
376
377 <p>A stale super-journal is a super-journal that is no longer being
378 used for anything. There is no requirement that stale super-journals
379 be deleted. The only reason for doing so is to free up disk space.</p>
380
381 <p>A super-journal is stale if no individual file journals are pointing
382 to it. To figure out if a super-journal is stale, we first read the
383 super-journal to obtain the names of all of its file journals. Then
384 we check each of those file journals. If any of the file journals named
385 in the super-journal exists and points back to the super-journal, then
386 the super-journal is not stale. If all file journals are either missing
387 or refer to other super-journals or no super-journal at all, then the
388 super-journal we are testing is stale and can be safely deleted.</p>
389
390 <a name="writing"></a>
391 <h2>5.0 Writing to a database file</h2>
392
393 <p>To write to a database, a process must first acquire a SHARED lock
394 as described above (possibly rolling back incomplete changes if there
395 is a hot journal).
396 After a SHARED lock is obtained, a RESERVED lock must be acquired.
397 The RESERVED lock signals that the process intends to write to the
398 database at some point in the future. Only one process at a time
399 can hold a RESERVED lock. But other processes can continue to read
400 the database while the RESERVED lock is held.
401 </p>
402
403 <p>If the process that wants to write is unable to obtain a RESERVED
404 lock, it must mean that another process already has a RESERVED lock.
405 In that case, the write attempt fails and returns SQLITE_BUSY.</p>
406
407 <p>After obtaining a RESERVED lock, the process that wants to write
408 creates a rollback journal. The header of the journal is initialized
409 with the original size of the database file. Space in the journal header
410 is also reserved for a super-journal name, though the super-journal
411 name is initially empty.</p>
412
413 <p>Before making changes to any page of the database, the process writes
414 the original content of that page into the rollback journal. Changes
415 to pages are held in memory at first and are not written to the disk.
416 The original database file remains unaltered, which means that other
417 processes can continue to read the database.</p>
418
419 <p>Eventually, the writing process will want to update the database
420 file, either because its memory cache has filled up or because it is
421 ready to commit its changes. Before this happens, the writer must
422 make sure no other process is reading the database and that the rollback
423 journal data is safely on the disk surface so that it can be used to
424 rollback incomplete changes in the event of a power failure.
425 The steps are as follows:</p>
426
427 <ol>
428 <li>Make sure all rollback journal data has actually been written to
429 the surface of the disk (and is not just being held in the operating
430 system's or disk controllers cache) so that if a power failure occurs
431 the data will still be there after power is restored.</li>
432 <li>Obtain a PENDING lock and then an EXCLUSIVE lock on the database file.
433 If other processes still have SHARED locks, the writer might have
434 to wait until those SHARED locks clear before it is able to obtain
435 an EXCLUSIVE lock.</li>
436 <li>Write all page modifications currently held in memory out to the
437 original database disk file.</li>
438 </ol>
439
440 <p>
441 If the reason for writing to the database file is because the memory
442 cache was full, then the writer will not commit right away. Instead,
443 the writer might continue to make changes to other pages. Before
444 subsequent changes are written to the database file, the rollback
445 journal must be flushed to disk again. Note also that the EXCLUSIVE
446 lock that the writer obtained in order to write to the database initially
447 must be held until all changes are committed. That means that no other
448 processes are able to access the database from the
449 time the memory cache first spills to disk until the transaction
450 commits.
451 </p>
452
453 <p>
454 When a writer is ready to commit its changes, it executes the following
455 steps:
456 </p>
457
458 <ol>
459 <li value="4">
460 Obtain an EXCLUSIVE lock on the database file and
461 make sure all memory changes have been written to the database file
462 using the algorithm of steps 1-3 above.</li>
463 <li>Flush all database file changes to the disk. Wait for those changes
464 to actually be written onto the disk surface.</li>
465 <li>Delete the journal file. (Or if the <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode</a> is TRUNCATE or
466 PERSIST, truncate the journal file or zero the header of the journal file,
467 respectively.) This is the instant when the changes are
468 committed. Prior to deleting the journal file, if a power failure
469 or crash occurs, the next process to open the database will see that
470 it has a hot journal and will roll the changes back.
471 After the journal is deleted, there will no longer be a hot journal
472 and the changes will persist.
473 </li>
474 <li>Drop the EXCLUSIVE and PENDING locks from the database file.
475 </li>
476 </ol>
477
478 <p>As soon as the PENDING lock is released from the database file, other
479 processes can begin reading the database again. In the current implementation,
480 the RESERVED lock is also released, but that is not essential for
481 correct operation.</p>
482
483 <p>If a transaction involves multiple databases, then a more complex
484 commit sequence is used, as follows:</p>
485
486 <ol>
487 <li value="4">
488 Make sure all individual database files have an EXCLUSIVE lock and a
489 valid journal.
490 <li>Create a super-journal. The name of the super-journal is arbitrary.
491 (The current implementation appends random suffixes to the name of the
492 main database file until it finds a name that does not previously exist.)
493 Fill the super-journal with the names of all the individual journals
494 and flush its contents to disk.
495 <li>Write the name of the super-journal into
496 all individual journals (in space set aside for that purpose in the
497 headers of the individual journals) and flush the contents of the
498 individual journals to disk and wait for those changes to reach the
499 disk surface.
500 <li>Flush all database file changes to the disk. Wait for those changes
501 to actually be written onto the disk surface.</li>
502 <li>Delete the super-journal file. This is the instant when the changes are
503 committed. Prior to deleting the super-journal file, if a power failure
504 or crash occurs, the individual file journals will be considered hot
505 and will be rolled back by the next process that
506 attempts to read them. After the super-journal has been deleted,
507 the file journals will no longer be considered hot and the changes
508 will persist.
509 </li>
510 <li>Delete all individual journal files.
511 <li>Drop the EXCLUSIVE and PENDING locks from all database files.
512 </li>
513 </ol>
514
515 <a name="writer_starvation"></a>
516 <h3>5.1 Writer starvation</h3>
517
518 <p>In SQLite version 2, if many processes are reading from the database,
519 it might be the case that there is never a time when there are
520 no active readers. And if there is always at least one read lock on the
521 database, no process would ever be able to make changes to the database
522 because it would be impossible to acquire a write lock. This situation
523 is called <em>writer starvation</em>.</p>
524
525 <p>SQLite version 3 seeks to avoid writer starvation through the use of
526 the PENDING lock. The PENDING lock allows existing readers to continue
527 but prevents new readers from connecting to the database. So when a
528 process wants to write a busy database, it can set a PENDING lock which
529 will prevent new readers from coming in. Assuming existing readers do
530 eventually complete, all SHARED locks will eventually clear and the
531 writer will be given a chance to make its changes.</p>
532
533 <a name="how_to_corrupt"></a>
534 <h2>6.0 How To Corrupt Your Database Files</h2>
535
536 <p>The pager module is very robust but it can be subverted.
537 This section attempts to identify and explain the risks.
538 (See also the <a href="atomiccommit.html#sect_9_0">Things That Can Go Wrong</a> section of the article
539 on <a href="atomiccommit.html">Atomic Commit</a>.</p>
540
541 <p>
542 Clearly, a hardware or operating system fault that introduces incorrect data
543 into the middle of the database file or journal will cause problems.
544 Likewise,
545 if a rogue process opens a database file or journal and writes malformed
546 data into the middle of it, then the database will become corrupt.
547 There is not much that can be done about these kinds of problems
548 so they are given no further attention.
549 </p>
550
551 <p>
552 SQLite uses POSIX advisory locks to implement locking on Unix. On
553 Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system
554 calls. SQLite assumes that these system calls all work as advertised. If
555 that is not the case, then database corruption can result. One should
556 note that POSIX advisory locking is known to be buggy or even unimplemented
557 on many NFS implementations (including recent versions of Mac OS X)
558 and that there are reports of locking problems
559 for network filesystems under Windows. Your best defense is to not
560 use SQLite for files on a network filesystem.
561 </p>
562
563 <p>
564 SQLite uses the fsync() system call to flush data to the disk under Unix and
565 it uses the FlushFileBuffers() to do the same under Windows. Once again,
566 SQLite assumes that these operating system services function as advertised.
567 But it has been reported that fsync() and FlushFileBuffers() do not always
568 work correctly, especially with some network filesystems or inexpensive IDE disks.
569 Apparently some manufacturers of IDE disks have controller chips that report
570 that data has reached the disk surface when in fact the data is still
571 in volatile cache memory in the disk drive electronics. There are also
572 reports that Windows sometimes chooses to ignore FlushFileBuffers() for
573 unspecified reasons. The author cannot verify any of these reports.
574 But if they are true, it means that database corruption is a possibility
575 following an unexpected power loss. These are hardware and/or operating
576 system bugs that SQLite is unable to defend against.
577 </p>
578
579 <a name="ext3-barrier-problem"></a>
580
581 <p>If a Linux <a href="http://en.wikipedia.org/wiki/Ext3">ext3</a>
582 filesystem is mounted without the "barrier=1" option
583 in the <a href="http://en.wikipedia.org/wiki/fstab">/etc/fstab</a>
584 and the disk drive write cache is enabled
585 then filesystem corruption can occur following a power loss or OS crash.
586 Whether or not corruption can occur depends on the details of the disk control
587 hardware; corruption is more likely with inexpensive consumer-grade disks
588 and less of a problem for enterprise-class storage devices with advanced
589 features such as non-volatile write caches.
590 Various ext3 experts
591 <a href="http://www.redhat.com/archives/ext3-users/2010-July/msg00001.html">
592 confirm this behavior</a>.
593 We are told that most Linux distributions do not use barrier=1 and do
594 not disable the write cache so most
595 Linux distributions are vulnerable to this problem. Note that this is an
596 operating system and hardware issue and that there is nothing that SQLite
597 can do to work around it.
598 <a href="http://ozlabs.org/~rusty/index.cgi/tech/2009-10-20.html">
599 Other database engines</a> have also run into this same problem.</p>
600
601 <p>
602 If a crash or power failure occurs and results in a hot journal but that
603 journal is deleted, the next process to open the database will not
604 know that it contains changes that need to be rolled back. The rollback
605 will not occur and the database will be left in an inconsistent state.
606 Rollback journals might be deleted for any number of reasons:
607 </p>
608
609 <ul>
610 <li>An administrator might be cleaning up after an OS crash or power failure,
611 see the journal file, think it is junk, and delete it.</li>
612 <li>Someone (or some process) might rename the database file but fail to
613 also rename its associated journal.</li>
614 <li>If the database file has aliases (hard or soft links) and the file
615 is opened by a different alias than the one used to create the journal,
616 then the journal will not be found. To avoid this problem, you should
617 not create links to SQLite database files.</li>
618 <li>Filesystem corruption following a power failure might cause the
619 journal to be renamed or deleted.</li>
620 </ul>
621
622 <p>
623 The last (fourth) bullet above merits additional comment. When SQLite creates
624 a journal file on Unix, it opens the directory that contains that file and
625 calls fsync() on the directory, in an effort to push the directory information
626 to disk. But suppose some other process is adding or removing unrelated
627 files to the directory that contains the database and journal at the
628 moment of a power failure. The supposedly unrelated actions of this other
629 process might result in the journal file being dropped from the directory and
630 moved into "lost+found". This is an unlikely scenario, but it could happen.
631 The best defenses are to use a journaling filesystem or to keep the
632 database and journal in a directory by themselves.
633 </p>
634
635 <p>
636 For a commit involving multiple databases and a super-journal, if the
637 various databases were on different disk volumes and a power failure occurs
638 during the commit, then when the machine comes back up the disks might
639 be remounted with different names. Or some disks might not be mounted
640 at all. When this happens the individual file journals and the
641 super-journal might not be able to find each other. The worst outcome from
642 this scenario is that the commit ceases to be atomic.
643 Some databases might be rolled back and others might not.
644 All databases will continue to be self-consistent.
645 To defend against this problem, keep all databases
646 on the same disk volume and/or remount disks using exactly the same names
647 after a power failure.
648 </p>
649
650 <a name="transaction_control"></a>
651 <h2>7.0 Transaction Control At The SQL Level</h2>
652
653 <p>
654 The changes to locking and concurrency control in SQLite version 3 also
655 introduce some subtle changes in the way transactions work at the SQL
656 language level.
657 By default, SQLite version 3 operates in <em>autocommit</em> mode.
658 In autocommit mode,
659 all changes to the database are committed as soon as all operations associated
660 with the current database connection complete.</p>
661
662 <p>The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword
663 is optional) is used to take SQLite out of autocommit mode.
664 Note that the BEGIN command does not acquire any locks on the database.
665 After a BEGIN command, a SHARED lock will be acquired when the first
666 SELECT statement is executed. A RESERVED lock will be acquired when
667 the first INSERT, UPDATE, or DELETE statement is executed. No EXCLUSIVE
668 lock is acquired until either the memory cache fills up and must
669 be spilled to disk or until the transaction commits. In this way,
670 the system delays blocking read access to the file until the
671 last possible moment.
672 </p>
673
674 <p>The SQL command "COMMIT" does not actually commit the changes to
675 disk. It just turns autocommit back on. Then, at the conclusion of
676 the command, the regular autocommit logic takes over and causes the
677 actual commit to disk to occur.
678 The SQL command "ROLLBACK" also operates by turning autocommit back on,
679 but it also sets a flag that tells the autocommit logic to rollback rather
680 than commit.</p>
681
682 <p>If the SQL COMMIT command turns autocommit on and the autocommit logic
683 then tries to commit change but fails because some other process is holding
684 a SHARED lock, then autocommit is turned back off automatically. This
685 allows the user to retry the COMMIT at a later time after the SHARED lock
686 has had an opportunity to clear.</p>
687
688 <p align="center"><small><i>This page was last updated on 2025-05-31 13:08:22Z </small></i></p>
689