Debian Codegraph @

www/atomiccommit.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>Atomic Commit In SQLite</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 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Atomic Commit In SQLite
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#_introduction">1. Introduction</a></div>
126 <div class="fancy-toc1"><a href="#_hardware_assumptions">2. Hardware Assumptions</a></div>
127 <div class="fancy-toc1"><a href="#_single_file_commit">3. Single File Commit</a></div>
128 <div class="fancy-toc2"><a href="#_initial_state">3.1. Initial State</a></div>
129 <div class="fancy-toc2"><a href="#_acquiring_a_read_lock">3.2. Acquiring A Read Lock</a></div>
130 <div class="fancy-toc2"><a href="#_reading_information_out_of_the_database">3.3. Reading Information Out Of The Database</a></div>
131 <div class="fancy-toc2"><a href="#_obtaining_a_reserved_lock">3.4. Obtaining A Reserved Lock</a></div>
132 <div class="fancy-toc2"><a href="#_creating_a_rollback_journal_file">3.5. Creating A Rollback Journal File</a></div>
133 <div class="fancy-toc2"><a href="#_changing_database_pages_in_user_space">3.6. Changing Database Pages In User Space</a></div>
134 <div class="fancy-toc2"><a href="#_flushing_the_rollback_journal_file_to_mass_storage">3.7. Flushing The Rollback Journal File To Mass Storage</a></div>
135 <div class="fancy-toc2"><a href="#_obtaining_an_exclusive_lock">3.8. Obtaining An Exclusive Lock</a></div>
136 <div class="fancy-toc2"><a href="#_writing_changes_to_the_database_file">3.9. Writing Changes To The Database File</a></div>
137 <div class="fancy-toc2"><a href="#flushing_changes_to_mass_storage">3.10. Flushing Changes To Mass Storage</a></div>
138 <div class="fancy-toc2"><a href="#deleting_the_rollback_journal">3.11. Deleting The Rollback Journal</a></div>
139 <div class="fancy-toc2"><a href="#releasing_the_lock">3.12. Releasing The Lock</a></div>
140 <div class="fancy-toc1"><a href="#_rollback">4. Rollback</a></div>
141 <div class="fancy-toc2"><a href="#_when_something_goes_wrong_">4.1. When Something Goes Wrong...</a></div>
142 <div class="fancy-toc2"><a href="#_hot_rollback_journals">4.2. Hot Rollback Journals</a></div>
143 <div class="fancy-toc2"><a href="#_obtaining_an_exclusive_lock_on_the_database">4.3. Obtaining An Exclusive Lock On The Database</a></div>
144 <div class="fancy-toc2"><a href="#_rolling_back_incomplete_changes">4.4. Rolling Back Incomplete Changes</a></div>
145 <div class="fancy-toc2"><a href="#_deleting_the_hot_journal">4.5. Deleting The Hot Journal</a></div>
146 <div class="fancy-toc2"><a href="#_continue_as_if_the_uncompleted_writes_had_never_happened">4.6. Continue As If The Uncompleted Writes Had Never Happened</a></div>
147 <div class="fancy-toc1"><a href="#_multi_file_commit">5. Multi-file Commit</a></div>
148 <div class="fancy-toc2"><a href="#_separate_rollback_journals_for_each_database">5.1. Separate Rollback Journals For Each Database</a></div>
149 <div class="fancy-toc2"><a href="#_the_super_journal_file">5.2. The Super-Journal File</a></div>
150 <div class="fancy-toc2"><a href="#_updating_rollback_journal_headers">5.3. Updating Rollback Journal Headers</a></div>
151 <div class="fancy-toc2"><a href="#_updating_the_database_files">5.4. Updating The Database Files</a></div>
152 <div class="fancy-toc2"><a href="#_delete_the_super_journal_file">5.5. Delete The Super-Journal File</a></div>
153 <div class="fancy-toc2"><a href="#_clean_up_the_rollback_journals">5.6. Clean Up The Rollback Journals</a></div>
154 <div class="fancy-toc1"><a href="#_additional_details_of_the_commit_process">6. Additional Details Of The Commit Process</a></div>
155 <div class="fancy-toc2"><a href="#_always_journal_complete_sectors">6.1. Always Journal Complete Sectors</a></div>
156 <div class="fancy-toc2"><a href="#_dealing_with_garbage_written_into_journal_files">6.2. Dealing With Garbage Written Into Journal Files</a></div>
157 <div class="fancy-toc2"><a href="#_cache_spill_prior_to_commit">6.3. Cache Spill Prior To Commit</a></div>
158 <div class="fancy-toc1"><a href="#_optimizations">7. Optimizations</a></div>
159 <div class="fancy-toc2"><a href="#_cache_retained_between_transactions">7.1. Cache Retained Between Transactions</a></div>
160 <div class="fancy-toc2"><a href="#_exclusive_access_mode">7.2. Exclusive Access Mode</a></div>
161 <div class="fancy-toc2"><a href="#_do_not_journal_freelist_pages">7.3. Do Not Journal Freelist Pages</a></div>
162 <div class="fancy-toc2"><a href="#_single_page_updates_and_atomic_sector_writes">7.4. Single Page Updates And Atomic Sector Writes</a></div>
163 <div class="fancy-toc2"><a href="#_filesystems_with_safe_append_semantics">7.5. Filesystems With Safe Append Semantics</a></div>
164 <div class="fancy-toc2"><a href="#_persistent_rollback_journals">7.6. Persistent Rollback Journals</a></div>
165 <div class="fancy-toc1"><a href="#_testing_atomic_commit_behavior">8. Testing Atomic Commit Behavior</a></div>
166 <div class="fancy-toc1"><a href="#_things_that_can_go_wrong">9. Things That Can Go Wrong</a></div>
167 <div class="fancy-toc2"><a href="#_broken_locking_implementations">9.1. Broken Locking Implementations</a></div>
168 <div class="fancy-toc2"><a href="#_incomplete_disk_flushes">9.2. Incomplete Disk Flushes</a></div>
169 <div class="fancy-toc2"><a href="#_partial_file_deletions">9.3. Partial File Deletions</a></div>
170 <div class="fancy-toc2"><a href="#_garbage_written_into_files">9.4. Garbage Written Into Files</a></div>
171 <div class="fancy-toc2"><a href="#_deleting_or_renaming_a_hot_journal">9.5. Deleting Or Renaming A Hot Journal</a></div>
172 <div class="fancy-toc1"><a href="#_future_directions_and_conclusion">10. Future Directions And Conclusion</a></div>
173 </div>
174 </details>
175 </div>
176
177
178
179
180 <h1 id="_introduction"><span>1. </span> Introduction</h1>
181
182 <p>An important feature of transactional databases like SQLite
183 is "atomic commit".
184 Atomic commit means that either all database changes within a single
185 transaction occur or none of them occur. With atomic commit, it
186 is as if many different writes to different sections of the database
187 file occur instantaneously and simultaneously.
188 Real hardware serializes writes to mass storage, and writing
189 a single sector takes a finite amount of time.
190 So it is impossible to truly write many different sectors of a
191 database file simultaneously and/or instantaneously.
192 But the atomic commit logic within
193 SQLite makes it appear as if the changes for a transaction
194 are all written instantaneously and simultaneously.</p>
195
196 <p>SQLite has the important property that transactions appear
197 to be atomic even if the transaction is interrupted by an
198 operating system crash or power failure.</p>
199
200 <p>This article describes the techniques used by SQLite to create the
201 illusion of atomic commit.</p>
202
203 <p>The information in this article applies only when SQLite is operating
204 in "rollback mode", or in other words when SQLite is not
205 using a <a href="wal.html">write-ahead log</a>. SQLite still supports atomic commit when
206 write-ahead logging is enabled, but it accomplishes atomic commit by
207 a different mechanism from the one described in this article. See
208 the <a href="wal.html">write-ahead log documentation</a> for additional information on how
209 SQLite supports atomic commit in that context.</p>
210
211 <a name="hardware"></a>
212
213 <h1 id="_hardware_assumptions"><span>2. </span> Hardware Assumptions</h1>
214
215 <p>Throughout this article, we will call the mass storage device "disk"
216 even though the mass storage device might really be flash memory.</p>
217
218 <p>We assume that disk is written in chunks which we call a "sector".
219 It is not possible to modify any part of the disk smaller than a sector.
220 To change a part of the disk smaller than a sector, you have to read in
221 the full sector that contains the part you want to change, make the
222 change, then write back out the complete sector.</p>
223
224 <p>On a traditional spinning disk, a sector is the minimum unit of transfer
225 in both directions, both reading and writing. On flash memory, however,
226 the minimum size of a read is typically much smaller than a minimum write.
227 SQLite is only concerned with the minimum write amount and so for the
228 purposes of this article, when we say "sector" we mean the minimum amount
229 of data that can be written to mass storage in a single go.</p>
230
231 <p>
232 Prior to SQLite version 3.3.14, a sector size of 512 bytes was
233 assumed in all cases. There was a compile-time option to change
234 this but the code had never been tested with a larger value. The
235 512 byte sector assumption seemed reasonable since until very recently
236 all disk drives used a 512 byte sector internally. However, there
237 has recently been a push to increase the sector size of disks to
238 4096 bytes. Also the sector size
239 for flash memory is usually larger than 512 bytes. For these reasons,
240 versions of SQLite beginning with 3.3.14 have a method in the OS
241 interface layer that interrogates the underlying filesystem to find
242 the true sector size. As currently implemented (version 3.5.0) this
243 method still returns a hard-coded value of 512 bytes, since there
244 is no standard way of discovering the true sector size on either
245 Unix or Windows. But the method is available for embedded device
246 manufacturers to tweak according to their own needs. And we have
247 left open the possibility of filling in a more meaningful implementation
248 on Unix and Windows in the future.</p>
249
250 <p>SQLite has traditionally assumed that a sector write is <u>not</u> atomic.
251 However, SQLite does always assume that a sector write is linear. By "linear"
252 we mean that SQLite assumes that when writing a sector, the hardware begins
253 at one end of the data and writes byte by byte until it gets to
254 the other end. The write might go from beginning to end or from
255 end to beginning. If a power failure occurs in the middle of a
256 sector write it might be that part of the sector was modified
257 and another part was left unchanged. The key assumption by SQLite
258 is that if any part of the sector gets changed, then either the
259 first or the last bytes will be changed. So the hardware will
260 never start writing a sector in the middle and work towards the
261 ends. We do not know if this assumption is always true but it
262 seems reasonable.</p>
263
264 <p>The previous paragraph states that SQLite does not assume that
265 sector writes are atomic. This is true by default. But as of
266 SQLite version 3.5.0, there is a new interface called the
267 Virtual File System (<a href="vfs.html">VFS</a>) interface. The <a href="vfs.html">VFS</a> is the only means
268 by which SQLite communicates to the underlying filesystem. The
269 code comes with default VFS implementations for Unix and Windows
270 and there is a mechanism for creating new custom VFS implementations
271 at runtime. In this new VFS interface there is a method called
272 xDeviceCharacteristics. This method interrogates the underlying
273 filesystem to discover various properties and behaviors that the
274 filesystem may or may not exhibit. The xDeviceCharacteristics
275 method might indicate that sector writes are atomic, and if it does
276 so indicate, SQLite will try to take advantage of that fact. But
277 the default xDeviceCharacteristics method for both Unix and Windows
278 does not indicate atomic sector writes and so these optimizations
279 are normally omitted.</p>
280
281 <p>SQLite assumes that the operating system will buffer writes and
282 that a write request will return before data has actually been stored
283 in the mass storage device.
284 SQLite further assumes that write operations will be reordered by
285 the operating system.
286 For this reason, SQLite does a "flush" or "fsync" operation at key
287 points. SQLite assumes that the flush or fsync will not return until
288 all pending write operations for the file that is being flushed have
289 completed. We are told that the flush and fsync primitives
290 are broken on some versions of Windows and Linux. This is unfortunate.
291 It opens SQLite up to the possibility of database corruption following
292 a power loss in the middle of a commit. However, there is nothing
293 that SQLite can do to test for or remedy the situation. SQLite
294 assumes that the operating system that it is running on works as
295 advertised. If that is not quite the case, well then hopefully you
296 will not lose power too often.</p>
297
298 <p>SQLite assumes that when a file grows in length that the new
299 file space originally contains garbage and then later is filled in
300 with the data actually written. In other words, SQLite assumes that
301 the file size is updated before the file content. This is a
302 pessimistic assumption and SQLite has to do some extra work to make
303 sure that it does not cause database corruption if power is lost
304 between the time when the file size is increased and when the
305 new content is written. The xDeviceCharacteristics method of
306 the <a href="vfs.html">VFS</a> might indicate that the filesystem will always write the
307 data before updating the file size. (This is the
308 SQLITE_IOCAP_SAFE_APPEND property for those readers who are looking
309 at the code.) When the xDeviceCharacteristics method indicates
310 that files content is written before the file size is increased,
311 SQLite can forego some of its pedantic database protection steps
312 and thereby decrease the amount of disk I/O needed to perform a
313 commit. The current implementation, however, makes no such assumptions
314 for the default VFSes for Windows and Unix.</p>
315
316 <p>SQLite assumes that a file deletion is atomic from the
317 point of view of a user process. By this we mean that if SQLite
318 requests that a file be deleted and the power is lost during the
319 delete operation, once power is restored either the file will
320 exist completely with all if its original content unaltered, or
321 else the file will not be seen in the filesystem at all. If
322 after power is restored the file is only partially deleted,
323 if some of its data has been altered or erased,
324 or the file has been truncated but not completely removed, then
325 database corruption will likely result.</p>
326
327 <p>SQLite assumes that the detection and/or correction of
328 bit errors caused by cosmic rays, thermal noise, quantum
329 fluctuations, device driver bugs, or other mechanisms, is the
330 responsibility of the underlying hardware and operating system.
331 SQLite does not add any redundancy to the database file for
332 the purpose of detecting corruption or I/O errors.
333 SQLite assumes that the data it reads is exactly the same data
334 that it previously wrote.</p>
335
336 <p>By default, SQLite assumes that an operating system call to write
337 a range of bytes will not damage or alter any bytes outside of that range
338 even if a power loss or OS crash occurs during that write. We
339 call this the "<a href="psow.html">powersafe overwrite</a>" property.
340 Prior to <a href="releaselog/3_7_9.html">version 3.7.9</a> (2011-11-01),
341 SQLite did not assume powersafe overwrite. But with the standard
342 sector size increasing from 512 to 4096 bytes on most disk drives, it
343 has become necessary to assume powersafe overwrite in order to maintain
344 historical performance levels and so powersafe overwrite is assumed by
345 default in recent versions of SQLite. The assumption of powersafe
346 overwrite property can be disabled at compile-time or at run-time if
347 desired. See the <a href="psow.html">powersafe overwrite documentation</a> for further
348 details.
349
350
351 <a name="section_3_0"></a>
352 </p><h1 id="_single_file_commit"><span>3. </span> Single File Commit</h1>
353
354 <p>We begin with an overview of the steps SQLite takes in order to
355 perform an atomic commit of a transaction against a single database
356 file. The details of file formats used to guard against damage from
357 power failures and techniques for performing an atomic commit across
358 multiple databases are discussed in later sections.</p>
359
360 <a name="initstate"></a>
361
362 <h2 id="_initial_state"><span>3.1. </span> Initial State</h2>
363
364 <img src="images/ac/commit-0.gif" align="right" hspace="15">
365
366 <p>The state of the computer when a database connection is
367 first opened is shown conceptually by the diagram at the
368 right.
369 The area of the diagram on the extreme right (labeled "Disk") represents
370 information stored on the mass storage device. Each rectangle is
371 a sector. The blue color represents that the sectors contain
372 original data.
373 The middle area is the operating systems disk cache. At the
374 onset of our example, the cache is cold and this is represented
375 by leaving the rectangles of the disk cache empty.
376 The left area of the diagram shows the content of memory for
377 the process that is using SQLite. The database connection has
378 just been opened and no information has been read yet, so the
379 user space is empty.
380 </p>
381 <br clear="both">
382
383 <a name="rdlck"></a>
384
385 <a name="section_3_2"></a>
386 <h2 id="_acquiring_a_read_lock"><span>3.2. </span> Acquiring A Read Lock</h2>
387
388 <img src="images/ac/commit-1.gif" align="right" hspace="15">
389
390 <p>Before SQLite can write to a database, it must first read
391 the database to see what is there already. Even if it is just
392 appending new data, SQLite still has to read in the database
393 schema from the "<a href="schematab.html">sqlite_schema</a>" table so that it can know
394 how to parse the INSERT statements and discover where in the
395 database file the new information should be stored.</p>
396
397 <p>The first step toward reading from the database file
398 is obtaining a shared lock on the database file. A "shared"
399 lock allows two or more database connections to read from the
400 database file at the same time. But a shared lock prevents
401 another database connection from writing to the database file
402 while we are reading it. This is necessary because if another
403 database connection were writing to the database file at the
404 same time we are reading from the database file, we might read
405 some data before the change and other data after the change.
406 This would make it appear as if the change made by the other
407 process is not atomic.</p>
408
409 <p>Notice that the shared lock is on the operating system
410 disk cache, not on the disk itself. File locks
411 really are just flags within the operating system kernel,
412 usually. (The details depend on the specific OS layer
413 interface.) Hence, the lock will instantly vanish if the
414 operating system crashes or if there is a power loss. It
415 is usually also the case that the lock will vanish if the
416 process that created the lock exits.</p>
417
418 <br clear="both">
419
420 <a name="section_3_3"></a>
421 <h2 id="_reading_information_out_of_the_database"><span>3.3. </span> Reading Information Out Of The Database</h2>
422
423 <img src="images/ac/commit-2.gif" align="right" hspace="15">
424
425 <p>After the shared lock is acquired, we can begin reading
426 information from the database file. In this scenario, we
427 are assuming a cold cache, so information must first be
428 read from mass storage into the operating system cache then
429 transferred from operating system cache into user space.
430 On subsequent reads, some or all of the information might
431 already be found in the operating system cache and so only
432 the transfer to user space would be required.</p>
433
434 <p>Usually only a subset of the pages in the database file
435 are read. In this example we are showing three
436 pages out of eight being read. In a typical application, a
437 database will have thousands of pages and a query will normally
438 only touch a small percentage of those pages.</p>
439
440 <br clear="both">
441
442 <a name="rsvdlock"></a>
443
444 <h2 id="_obtaining_a_reserved_lock"><span>3.4. </span> Obtaining A Reserved Lock</h2>
445
446 <img src="images/ac/commit-3.gif" align="right" hspace="15">
447
448 <p>Before making changes to the database, SQLite first
449 obtains a "reserved" lock on the database file. A reserved
450 lock is similar to a shared lock in that both a reserved lock
451 and shared lock allow other processes to read from the database
452 file. A single reserve lock can coexist with multiple shared
453 locks from other processes. However, there can only be a
454 single reserved lock on the database file. Hence only a
455 single process can be attempting to write to the database
456 at one time.</p>
457
458 <p>The idea behind a reserved lock is that it signals that
459 a process intends to modify the database file in the near
460 future but has not yet started to make the modifications.
461 And because the modifications have not yet started, other
462 processes can continue to read from the database. However,
463 no other process should also begin trying to write to the
464 database.</p>
465
466 <br clear="both">
467 <a name="section_3_5"></a>
468 <h2 id="_creating_a_rollback_journal_file"><span>3.5. </span> Creating A Rollback Journal File</h2>
469 <img src="images/ac/commit-4.gif" align="right" hspace="15">
470
471 <p>Prior to making any changes to the database file, SQLite first
472 creates a separate rollback journal file and writes into the
473 rollback journal the original
474 content of the database pages that are to be altered.
475 The idea behind the rollback journal is that it contains
476 all information needed to restore the database back to
477 its original state.</p>
478
479 <p>The rollback journal contains a small header (shown in green
480 in the diagram) that records the original size of the database
481 file. So if a change causes the database file to grow, we
482 will still know the original size of the database. The page
483 number is stored together with each database page that is
484 written into the rollback journal.</p>
485
486 <p>
487 When a new file is created, most desktop operating systems
488 (Windows, Linux, Mac OS X) will not actually write anything to
489 disk. The new file is created in the operating systems disk
490 cache only. The file is not created on mass storage until sometime
491 later, when the operating system has a spare moment. This creates
492 the impression to users that I/O is happening much faster than
493 is possible when doing real disk I/O. We illustrate this idea in
494 the diagram to the right by showing that the new rollback journal
495 appears in the operating system disk cache only and not on the
496 disk itself.</p>
497
498 <br clear="both">
499 <a name="section_3_6"></a>
500 <h2 id="_changing_database_pages_in_user_space"><span>3.6. </span> Changing Database Pages In User Space</h2>
501 <img src="images/ac/commit-5.gif" align="right" hspace="15">
502
503 <p>After the original page content has been saved in the rollback
504 journal, the pages can be modified in user memory. Each database
505 connection has its own private copy of user space, so the changes
506 that are made in user space are only visible to the database connection
507 that is making the changes. Other database connections still see
508 the information in operating system disk cache buffers which have
509 not yet been changed. And so even though one process is busy
510 modifying the database, other processes can continue to read their
511 own copies of the original database content.</p>
512
513 <br clear="both">
514 <a name="section_3_7"></a>
515 <h2 id="_flushing_the_rollback_journal_file_to_mass_storage"><span>3.7. </span> Flushing The Rollback Journal File To Mass Storage</h2>
516 <img src="images/ac/commit-6.gif" align="right" hspace="15">
517
518 <p>The next step is to flush the content of the rollback journal
519 file to nonvolatile storage.
520 As we will see later,
521 this is a critical step in insuring that the database can survive
522 an unexpected power loss.
523 This step also takes a lot of time, since writing to nonvolatile
524 storage is normally a slow operation.</p>
525
526 <p>This step is usually more complicated than simply flushing
527 the rollback journal to the disk. On most platforms two separate
528 flush (or fsync()) operations are required. The first flush writes
529 out the base rollback journal content. Then the header of the
530 rollback journal is modified to show the number of pages in the
531 rollback journal. Then the header is flushed to disk. The details
532 on why we do this header modification and extra flush are provided
533 in a later section of this paper.</p>
534
535 <br clear="both">
536 <a name="section_3_8"></a>
537 <h2 id="_obtaining_an_exclusive_lock"><span>3.8. </span> Obtaining An Exclusive Lock</h2>
538 <img src="images/ac/commit-7.gif" align="right" hspace="15">
539
540 <p>Prior to making changes to the database file itself, we must
541 obtain an exclusive lock on the database file. Obtaining an
542 exclusive lock is really a two-step process. First SQLite obtains
543 a "pending" lock. Then it escalates the pending lock to an
544 exclusive lock.</p>
545
546 <p>A pending lock allows other processes that already have a
547 shared lock to continue reading the database file. But it
548 prevents new shared locks from being established. The idea
549 behind a pending lock is to prevent writer starvation caused
550 by a large pool of readers. There might be dozens, even hundreds,
551 of other processes trying to read the database file. Each process
552 acquires a shared lock before it starts reading, reads what it
553 needs, then releases the shared lock. If, however, there are
554 many different processes all reading from the same database, it
555 might happen that a new process always acquires its shared lock before
556 the previous process releases its shared lock. And so there is
557 never an instant when there are no shared locks on the database
558 file and hence there is never an opportunity for the writer to
559 seize the exclusive lock. A pending lock is designed to prevent
560 that cycle by allowing existing shared locks to proceed but
561 blocking new shared locks from being established. Eventually
562 all shared locks will clear and the pending lock will then be
563 able to escalate into an exclusive lock.</p>
564
565 <br clear="both">
566 <a name="section_3_9"></a>
567 <h2 id="_writing_changes_to_the_database_file"><span>3.9. </span> Writing Changes To The Database File</h2>
568 <img src="images/ac/commit-8.gif" align="right" hspace="15">
569
570 <p>Once an exclusive lock is held, we know that no other
571 processes are reading from the database file and it is
572 safe to write changes into the database file. Usually
573 those changes only go as far as the operating systems disk
574 cache and do not make it all the way to mass storage.</p>
575
576 <br clear="both">
577 <a name="section_3_10"></a>
578 <h2 id="flushing_changes_to_mass_storage"><span>3.10. </span>Flushing Changes To Mass Storage</h2>
579 <img src="images/ac/commit-9.gif" align="right" hspace="15">
580
581 <p>Another flush must occur to make sure that all the
582 database changes are written into nonvolatile storage.
583 This is a critical step to ensure that the database will
584 survive a power loss without damage. However, because
585 of the inherent slowness of writing to disk or flash memory,
586 this step together with the rollback journal file flush in section
587 3.7 above takes up most of the time required to complete a
588 transaction commit in SQLite.</p>
589
590 <br clear="both">
591 <a name="section_3_11"></a>
592 <h2 id="deleting_the_rollback_journal"><span>3.11. </span>Deleting The Rollback Journal</h2>
593 <img src="images/ac/commit-A.gif" align="right" hspace="15">
594
595 <p>After the database changes are all safely on the mass
596 storage device, the rollback journal file is deleted.
597 This is the instant where the transaction commits.
598 If a power failure or system crash occurs prior to this
599 point, then recovery processes to be described later make
600 it appear as if no changes were ever made to the database
601 file. If a power failure or system crash occurs after
602 the rollback journal is deleted, then it appears as if
603 all changes have been written to disk. Thus, SQLite gives
604 the appearance of having made no changes to the database
605 file or having made the complete set of changes to the
606 database file depending on whether or not the rollback
607 journal file exists.</p>
608
609 <p>Deleting a file is not really an atomic operation, but
610 it appears to be from the point of view of a user process.
611 A process is always able to ask the operating system "does
612 this file exist?" and the process will get back a yes or no
613 answer. After a power failure that occurs during a
614 transaction commit, SQLite will ask the operating system
615 whether or not the rollback journal file exists. If the
616 answer is "yes" then the transaction is incomplete and is
617 rolled back. If the answer is "no" then it means the transaction
618 did commit.</p>
619
620 <p>The existence of a transaction depends on whether or
621 not the rollback journal file exists and the deletion
622 of a file appears to be an atomic operation from the point of
623 view of a user-space process. Therefore,
624 a transaction appears to be an atomic operation.</p>
625
626 <p>The act of deleting a file is expensive on many systems.
627 As an optimization, SQLite can be configured to truncate
628 the journal file to zero bytes in length
629 or overwrite the journal file header with zeros. In either
630 case, the resulting journal file is no longer capable of rolling
631 back and so the transaction still commits. Truncating a file
632 to zero length, like deleting a file, is assumed to be an atomic
633 operation from the point of view of a user process. Overwriting
634 the header of the journal with zeros is not atomic, but if any
635 part of the header is malformed the journal will not roll back.
636 Hence, one can say that the commit occurs as soon as the header
637 is sufficiently changed to make it invalid. Typically this happens
638 as soon as the first byte of the header is zeroed.</p>
639
640 <br clear="both">
641 <a name="section_3_12"></a>
642 <h2 id="releasing_the_lock"><span>3.12. </span>Releasing The Lock</h2>
643 <img src="images/ac/commit-B.gif" align="right" hspace="15">
644
645 <p>The last step in the commit process is to release the
646 exclusive lock so that other processes can once again
647 start accessing the database file.</p>
648
649 <p>In the diagram at the right, we show that the information
650 that was held in user space is cleared when the lock is released.
651 This used to be literally true for older versions of SQLite. But
652 more recent versions of SQLite keep the user space information
653 in memory in case it might be needed again at the start of the
654 next transaction. It is cheaper to reuse information that is
655 already in local memory than to transfer the information back
656 from the operating system disk cache or to read it off of the
657 disk drive again. Prior to reusing the information in user space,
658 we must first reacquire the shared lock and then we have to check
659 to make sure that no other process modified the database file while
660 we were not holding a lock. There is a counter in the first page
661 of the database that is incremented every time the database file
662 is modified. We can find out if another process has modified the
663 database by checking that counter. If the database was modified,
664 then the user space cache must be cleared and reread. But it is
665 commonly the case that no changes have been made and the user
666 space cache can be reused for a significant performance savings.</p>
667
668 <br clear="both">
669 <a name="rollback"></a>
670
671 <h1 id="_rollback"><span>4. </span> Rollback</h1>
672
673 <p>An atomic commit is supposed to happen instantaneously. But the processing
674 described above clearly takes a finite amount of time.
675 Suppose the power to the computer were cut
676 part way through the commit operation described above. In order
677 to maintain the illusion that the changes were instantaneous, we
678 have to "rollback" any partial changes and restore the database to
679 the state it was in prior to the beginning of the transaction.</p>
680
681 <a name="crisis"></a>
682
683 <h2 id="_when_something_goes_wrong_"><span>4.1. </span> When Something Goes Wrong...</h2>
684 <img src="images/ac/rollback-0.gif" align="right" hspace="15">
685
686 <p>Suppose the power loss occurred
687 during <a href="#section_3_10">step 3.10</a> above,
688 while the database changes were being written to disk.
689 After power is restored, the situation might be something
690 like what is shown to the right. We were trying to change
691 three pages of the database file but only one page was
692 successfully written. Another page was partially written
693 and a third page was not written at all.</p>
694
695 <p>The rollback journal is complete and intact on disk when
696 the power is restored. This is a key point. The reason for
697 the flush operation in <a href="#section_3_7">step 3.7</a>
698 is to make absolutely sure that
699 all of the rollback journal is safely on nonvolatile storage
700 prior to making any changes to the database file itself.</p>
701
702 <br clear="both">
703 <a name="section_4_2"></a>
704 <h2 id="_hot_rollback_journals"><span>4.2. </span> Hot Rollback Journals</h2>
705 <img src="images/ac/rollback-1.gif" align="right" hspace="15">
706
707 <p>The first time that any SQLite process attempts to access
708 the database file, it obtains a shared lock as described in
709 <a href="#section_3_2">section 3.2</a> above.
710 But then it notices that there is a
711 rollback journal file present. SQLite then checks to see if
712 the rollback journal is a "hot journal". A hot journal is
713 a rollback journal that needs to be played back in order to
714 restore the database to a sane state. A hot journal only
715 exists when an earlier process was in the middle of committing
716 a transaction when it crashed or lost power.</p>
717
718 <p>A rollback journal is a "hot" journal if all of the following
719 are true:</p>
720
721 <ul>
722 <li>The rollback journal exists.
723 </li><li>The rollback journal is not an empty file.
724 </li><li>There is no reserved lock on the main database file.
725 </li><li>The header of the rollback journal is well-formed and in particular
726 has not been zeroed out.
727 </li><li>The rollback journal does not
728 contain the name of a super-journal file (see
729 <a href="#section_5_5">section 5.5</a> below) or if does
730 contain the name of a super-journal, then that super-journal
731 file exists.
732 </li></ul>
733
734 <p>The presence of a hot journal is our indication
735 that a previous process was trying to commit a transaction but
736 it aborted for some reason prior to the completion of the
737 commit. A hot journal means that
738 the database file is in an inconsistent state and needs to
739 be repaired (by rollback) prior to being used.</p>
740
741 <br clear="both">
742 <a name="exlock"></a>
743
744 <h2 id="_obtaining_an_exclusive_lock_on_the_database"><span>4.3. </span> Obtaining An Exclusive Lock On The Database</h2>
745 <img src="images/ac/rollback-2.gif" align="right" hspace="15">
746
747 <p>The first step toward dealing with a hot journal is to
748 obtain an exclusive lock on the database file. This prevents two
749 or more processes from trying to rollback the same hot journal
750 at the same time.</p>
751
752 <br clear="both">
753 <a name="section_4_4"></a>
754 <h2 id="_rolling_back_incomplete_changes"><span>4.4. </span> Rolling Back Incomplete Changes</h2>
755 <img src="images/ac/rollback-3.gif" align="right" hspace="15">
756
757 <p>Once a process obtains an exclusive lock, it is permitted
758 to write to the database file. It then proceeds to read the
759 original content of pages out of the rollback journal and write
760 that content back to where it came from in the database file.
761 Recall that the header of the rollback journal records the original
762 size of the database file prior to the start of the aborted
763 transaction. SQLite uses this information to truncate the
764 database file back to its original size in cases where the
765 incomplete transaction caused the database to grow. At the
766 end of this step, the database should be the same size and
767 contain the same information as it did before the start of
768 the aborted transaction.</p>
769
770 <br clear="both">
771 <a name="delhotjrnl"></a>
772
773 <h2 id="_deleting_the_hot_journal"><span>4.5. </span> Deleting The Hot Journal</h2>
774 <img src="images/ac/rollback-4.gif" align="right" hspace="15">
775
776 <p>After all information in the rollback journal has been
777 played back into the database file (and flushed to disk in case
778 we encounter yet another power failure), the hot rollback journal
779 can be deleted.</p>
780
781 <p>As in <a href="#section_3_11">section 3.11</a>, the journal
782 file might be truncated to zero length or its header might
783 be overwritten with zeros as an optimization on systems where
784 deleting a file is expensive. Either way, the journal is no
785 longer hot after this step.</p>
786
787 <br clear="both">
788 <a name="cont"></a>
789
790 <h2 id="_continue_as_if_the_uncompleted_writes_had_never_happened"><span>4.6. </span> Continue As If The Uncompleted Writes Had Never Happened</h2>
791 <img src="images/ac/rollback-5.gif" align="right" hspace="15">
792
793 <p>The final recovery step is to reduce the exclusive lock back
794 to a shared lock. Once this happens, the database is back in the
795 state that it would have been if the aborted transaction had never
796 started. Since all of this recovery activity happens completely
797 automatically and transparently, it appears to the program using
798 SQLite as if the aborted transaction had never begun.</p>
799
800 <br clear="both">
801 <a name="multicommit"></a>
802
803 <h1 id="_multi_file_commit"><span>5. </span> Multi-file Commit</h1>
804
805 <p>SQLite allows a single
806 <a href="c3ref/sqlite3.html">database connection</a> to talk to
807 two or more database files simultaneously through the use of
808 the <a href="lang_attach.html">ATTACH DATABASE</a> command.
809 When multiple database files are modified within a single
810 transaction, all files are updated atomically.
811 In other words, either all of the database files are updated or
812 else none of them are.
813 Achieving an atomic commit across multiple database files is
814 more complex that doing so for a single file. This section
815 describes how SQLite works that bit of magic.</p>
816
817 <a name="multijrnl"></a>
818
819 <h2 id="_separate_rollback_journals_for_each_database"><span>5.1. </span> Separate Rollback Journals For Each Database</h2>
820 <img src="images/ac/multi-0.gif" align="right" hspace="15">
821
822 <p>When multiple database files are involved in a transaction,
823 each database has its own rollback journal and each database
824 is locked separately. The diagram at the right shows a scenario
825 where three different database files have been modified within
826 one transaction. The situation at this step is analogous to
827 the single-file transaction scenario at
828 <a href="#section_3_6">step 3.6</a>. Each database file has
829 a reserved lock. For each database, the original content of pages
830 that are being changed have been written into the rollback journal
831 for that database, but the content of the journals have not yet
832 been flushed to disk. No changes have been made to the database
833 file itself yet, though presumably there are changes being held
834 in user memory.</p>
835
836 <p>For brevity, the diagrams in this section are simplified from
837 those that came before. Blue color still signifies original content
838 and pink still signifies new content. But the individual pages
839 in the rollback journal and the database file are not shown and
840 we are not making the distinction between information in the
841 operating system cache and information that is on disk. All of
842 these factors still apply in a multi-file commit scenario. They
843 just take up a lot of space in the diagrams and they do not add
844 any new information, so they are omitted here.</p>
845
846 <br clear="both">
847 <a name="sprjrnl"></a>
848
849 <h2 id="_the_super_journal_file"><span>5.2. </span> The Super-Journal File</h2>
850 <img src="images/ac/multi-1.gif" align="right" hspace="15">
851
852 <p>The next step in a multi-file commit is the creation of a
853 "super-journal" file. The name of the super-journal file is
854 the same name as the original database filename (the database
855 that was opened using the
856 <a href="c3ref/open.html">sqlite3_open()</a> interface,
857 not one of the <a href="lang_attach.html">ATTACHed</a> auxiliary
858 databases) with the text "<b>-mj</b><i>HHHHHHHH</i>" appended where
859 <i>HHHHHHHH</i> is a random 32-bit hexadecimal number. The
860 random <i>HHHHHHHH</i> suffix changes for every new super-journal.</p>
861
862 <p><i>(Nota bene: The formula for computing the super-journal filename
863 given in the previous paragraph corresponds to the implementation as
864 of SQLite version 3.5.0. But this formula is not part of the SQLite
865 specification and is subject to change in future releases.)</i></p>
866
867 <p>Unlike the rollback journals, the super-journal does not contain
868 any original database page content. Instead, the super-journal contains
869 the full pathnames for rollback journals for every database that is
870 participating in the transaction.</p>
871
872 <p>After the super-journal is constructed, its content is flushed
873 to disk before any further actions are taken. On Unix, the directory
874 that contains the super-journal is also synced in order to make sure
875 the super-journal file will appear in the directory following a
876 power failure.</p>
877
878 <p>The purpose of the super-journal is to ensure that multi-file
879 transactions are atomic across a power-loss. But if the database files
880 have other settings that compromise integrity across a power-loss event
881 (such as <a href="pragma.html#pragma_synchronous">PRAGMA synchronous=OFF</a> or <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=MEMORY</a>) then
882 the creation of the super-journal is omitted, as an optimization.
883
884 <br clear="both">
885 <a name="multijrnlupdate"></a>
886
887 </p><h2 id="_updating_rollback_journal_headers"><span>5.3. </span> Updating Rollback Journal Headers</h2>
888 <img src="images/ac/multi-2.gif" align="right" hspace="15">
889
890 <p>The next step is to record the full pathname of the super-journal file
891 in the header of every rollback journal. Space to hold the
892 super-journal filename was reserved at the beginning of each rollback journal
893 as the rollback journals were created.</p>
894
895 <p>The content of each rollback journal is flushed to disk both before
896 and after the super-journal filename is written into the rollback
897 journal header. It is important to do both of these flushes. Fortunately,
898 the second flush is usually inexpensive since typically only a single
899 page of the journal file (the first page) has changed.</p>
900
901 <p>This step is analogous to
902 <a href="#section_3_7">step 3.7</a> in the single-file commit
903 scenario described above.</p>
904
905 <br clear="both">
906 <a name="multidbupdate"></a>
907
908 <h2 id="_updating_the_database_files"><span>5.4. </span> Updating The Database Files</h2>
909 <img src="images/ac/multi-3.gif" align="right" hspace="15">
910
911 <p>Once all rollback journal files have been flushed to disk, it
912 is safe to begin updating database files. We have to obtain an
913 exclusive lock on all database files before writing the changes.
914 After all the changes are written, it is important to flush the
915 changes to disk so that they will be preserved in the event of
916 a power failure or operating system crash.</p>
917
918 <p>This step corresponds to steps
919 <a href="#section_3_8">3.8</a>,
920 <a href="#section_3_9">3.9</a>, and
921 <a href="#section_3_10">3.10</a> in the single-file commit
922 scenario described previously.</p>
923
924
925 <br clear="both">
926 <a name="section_5_5"></a>
927 <h2 id="_delete_the_super_journal_file"><span>5.5. </span> Delete The Super-Journal File</h2>
928 <img src="images/ac/multi-4.gif" align="right" hspace="15">
929
930 <p>The next step is to delete the super-journal file.
931 This is the point where the multi-file transaction commits.
932 This step corresponds to
933 <a href="#section_3_11">step 3.11</a> in the single-file
934 commit scenario where the rollback journal is deleted.</p>
935
936 <p>If a power failure or operating system crash occurs at this
937 point, the transaction will not rollback when the system reboots
938 even though there are rollback journals present. The
939 difference is the super-journal pathname in the header of the
940 rollback journal. Upon restart, SQLite only considers a journal
941 to be hot and will only playback the journal if there is no
942 super-journal filename in the header (which is the case for
943 a single-file commit) or if the super-journal file still
944 exists on disk.</p>
945
946 <br clear="both">
947 <a name="cleanup"></a>
948
949 <h2 id="_clean_up_the_rollback_journals"><span>5.6. </span> Clean Up The Rollback Journals</h2>
950 <img src="images/ac/multi-5.gif" align="right" hspace="15">
951
952 <p>The final step in a multi-file commit is to delete the
953 individual rollback journals and drop the exclusive locks on
954 the database files so that other processes can see the changes.
955 This corresponds to
956 <a href="#section_3_12">step 3.12</a> in the single-file
957 commit sequence.</p>
958
959 <p>The transaction has already committed at this point so timing
960 is not critical in the deletion of the rollback journals.
961 The current implementation deletes a single rollback journal
962 then unlocks the corresponding database file before proceeding
963 to the next rollback journal. But in the future we might change
964 this so that all rollback journals are deleted before any database
965 files are unlocked. As long as the rollback journal is deleted before
966 its corresponding database file is unlocked it does not matter in what
967 order the rollback journals are deleted or the database files are
968 unlocked.</p>
969
970 <a name="moredetail"></a>
971
972 <h1 id="_additional_details_of_the_commit_process"><span>6. </span> Additional Details Of The Commit Process</h1>
973
974 <p><a href="#section_3_0">Section 3.0</a> above provides an overview of
975 how atomic commit works in SQLite. But it glosses over a number of
976 important details. The following subsections will attempt to fill
977 in the gaps.</p>
978
979 <a name="completesectors"></a>
980
981 <h2 id="_always_journal_complete_sectors"><span>6.1. </span> Always Journal Complete Sectors</h2>
982
983 <p>When the original content of a database page is written into
984 the rollback journal (as shown in <a href="#section_3_5">section 3.5</a>),
985 SQLite always writes a complete sector of data, even if the
986 page size of the database is smaller than the sector size.
987 Historically, the sector size in SQLite has been hard coded to 512
988 bytes and since the minimum page size is also 512 bytes, this has never
989 been an issue. But beginning with SQLite version 3.3.14, it is possible
990 for SQLite to use mass storage devices with a sector size larger than 512
991 bytes. So, beginning with version 3.3.14, whenever any page within a
992 sector is written into the journal file, all pages in that same sector
993 are stored with it.</p>
994
995 <p>It is important to store all pages of a sector in the rollback
996 journal in order to prevent database corruption following a power
997 loss while writing the sector. Suppose that pages 1, 2, 3, and 4 are
998 all stored in sector 1 and that page 2 is modified. In order to write
999 the changes to page 2, the underlying hardware must also rewrite the
1000 content of pages 1, 3, and 4 since the hardware must write the complete
1001 sector. If this write operation is interrupted by a power outage,
1002 one or more of the pages 1, 3, or 4 might be left with incorrect data.
1003 Hence, to avoid lasting corruption to the database, the original content
1004 of all of those pages must be contained in the rollback journal.</p>
1005
1006 <a name="journalgarbage"></a>
1007
1008 <h2 id="_dealing_with_garbage_written_into_journal_files"><span>6.2. </span> Dealing With Garbage Written Into Journal Files</h2>
1009
1010 <p>When data is appended to the end of the rollback journal,
1011 SQLite normally makes the pessimistic assumption that the file
1012 is first extended with invalid "garbage" data and that afterwards
1013 the correct data replaces the garbage. In other words, SQLite assumes
1014 that the file size is increased first and then afterwards the content
1015 is written into the file. If a power failure occurs after the file
1016 size has been increased but before the file content has been written,
1017 the rollback journal can be left containing garbage data. If after
1018 power is restored, another SQLite process sees the rollback journal
1019 containing the garbage data and tries to roll it back into the original
1020 database file, it might copy some of the garbage into the database file
1021 and thus corrupt the database file.</p>
1022
1023 <p>SQLite uses two defenses against this problem. In the first place,
1024 SQLite records the number of pages in the rollback journal in the header
1025 of the rollback journal. This number is initially zero. So during an
1026 attempt to rollback an incomplete (and possibly corrupt) rollback
1027 journal, the process doing the rollback will see that the journal
1028 contains zero pages and will thus make no changes to the database. Prior
1029 to a commit, the rollback journal is flushed to disk to ensure that
1030 all content has been synced to disk and there is no "garbage" left
1031 in the file, and only then is the page count in the header changed from
1032 zero to the number of pages in the rollback journal. The rollback journal
1033 header is always kept in a separate sector from any page data so that
1034 it can be overwritten and flushed without risking damage to a data
1035 page if a power outage occurs. Notice that the rollback journal
1036 is flushed to disk twice: once to write the page content and a second
1037 time to write the page count in the header.</p>
1038
1039 <p>The previous paragraph describes what happens when the
1040 synchronous pragma setting is "full".</p>
1041
1042 <blockquote>
1043 PRAGMA synchronous=FULL;
1044 </blockquote>
1045
1046 <p>The default synchronous setting is full so the above is what usually
1047 happens. However, if the synchronous setting is lowered to "normal",
1048 SQLite only flushes the rollback journal once, after the page count has
1049 been written.
1050 This carries a risk of corruption because it might happen that the
1051 modified (non-zero) page count reaches the disk surface before all
1052 of the data does. The data will have been written first, but SQLite
1053 assumes that the underlying filesystem can reorder write requests and
1054 that the page count can be burned into oxide first even though its
1055 write request occurred last. So as a second line of defense, SQLite
1056 also uses a 32-bit checksum on every page of data in the rollback
1057 journal. This checksum is evaluated for each page during rollback
1058 while rolling back a journal as described in
1059 <a href="#section_4_4">section 4.4</a>. If an incorrect checksum
1060 is seen, the rollback is abandoned. Note that the checksum does
1061 not guarantee that the page data is correct since there is a small
1062 but finite probability that the checksum might be right even if the data is
1063 corrupt. But the checksum does at least make such an error unlikely.
1064 </p>
1065
1066 <p>Note that the checksums in the rollback journal are not necessary
1067 if the synchronous setting is FULL. We only depend on the checksums
1068 when synchronous is lowered to NORMAL. Nevertheless, the checksums
1069 never hurt and so they are included in the rollback journal regardless
1070 of the synchronous setting.</p>
1071
1072 <a name="cachespill"></a>
1073
1074 <h2 id="_cache_spill_prior_to_commit"><span>6.3. </span> Cache Spill Prior To Commit</h2>
1075
1076 <p>The commit process shown in <a href="#section_3_0">section 3.0</a>
1077 assumes that all database changes fit in memory until it is time to
1078 commit. This is the common case. But sometimes a larger change will
1079 overflow the user-space cache prior to transaction commit. In those
1080 cases, the cache must spill to the database before the transaction
1081 is complete.</p>
1082
1083 <p>At the beginning of a cache spill, the status of the database
1084 connection is as shown in <a href="#section_3_6">step 3.6</a>.
1085 Original page content has been saved in the rollback journal and
1086 modifications of the pages exist in user memory. To spill the cache,
1087 SQLite executes steps <a href="#section_3_7">3.7</a> through
1088 <a href="#section_3_9">3.9</a>. In other words, the rollback journal
1089 is flushed to disk, an exclusive lock is acquired, and changes are
1090 written into the database. But the remaining steps are deferred
1091 until the transaction really commits. A new journal header is
1092 appended to the end of the rollback journal (in its own sector)
1093 and the exclusive database lock is retained, but otherwise processing
1094 returns to <a href="#section_3_6">step 3.6</a>. When the transaction
1095 commits, or if another cache spill occurs, steps
1096 <a href="#section_3_7">3.7</a> and <a href="#section_3_9">3.9</a> are
1097 repeated. (Step <a href="#section_3_8">3.8</a> is omitted on second
1098 and subsequent passes since an exclusive database lock is already held
1099 due to the first pass.)</p>
1100
1101 <p>A cache spill causes the lock on the database file to
1102 escalate from reserved to exclusive. This reduces concurrency.
1103 A cache spill also causes extra disk flush or fsync operations to
1104 occur and these operations are slow, hence a cache spill can
1105 seriously reduce performance.
1106 For these reasons a cache spill is avoided whenever possible.</p>
1107
1108 <a name="opts"></a>
1109
1110 <h1 id="_optimizations"><span>7. </span> Optimizations</h1>
1111
1112 <p>Profiling indicates that for most systems and in most circumstances
1113 SQLite spends most of its time doing disk I/O. It follows then that
1114 anything we can do to reduce the amount of disk I/O will likely have a
1115 large positive impact on the performance of SQLite. This section
1116 describes some of the techniques used by SQLite to try to reduce the
1117 amount of disk I/O to a minimum while still preserving atomic commit.</p>
1118
1119 <a name="keepcache"></a>
1120
1121 <h2 id="_cache_retained_between_transactions"><span>7.1. </span> Cache Retained Between Transactions</h2>
1122
1123 <p><a href="#section_3_12">Step 3.12</a> of the commit process shows
1124 that once the shared lock has been released, all user-space cache
1125 images of database content must be discarded. This is done because
1126 without a shared lock, other processes are free to modify the database
1127 file content and so any user-space image of that content might become
1128 obsolete. Consequently, each new transaction would begin by rereading
1129 data which had previously been read. This is not as bad as it sounds
1130 at first since the data being read is still likely in the operating
1131 systems file cache. So the "read" is really just a copy of data
1132 from kernel space into user space. But even so, it still takes time.</p>
1133
1134 <p>Beginning with SQLite version 3.3.14 a mechanism has been added
1135 to try to reduce the needless rereading of data. In newer versions
1136 of SQLite, the data in the user-space pager cache is retained when
1137 the lock on the database file is released. Later, after the
1138 shared lock is acquired at the beginning of the next transaction,
1139 SQLite checks to see if any other process has modified the database
1140 file. If the database has been changed in any way since the lock
1141 was last released, the user-space cache is erased at that point.
1142 But commonly the database file is unchanged and the user-space cache
1143 can be retained, and some unnecessary read operations can be avoided.</p>
1144
1145 <p>In order to determine whether or not the database file has changed,
1146 SQLite uses a counter in the database header (in bytes 24 through 27)
1147 which is incremented during every change operation. SQLite saves a copy
1148 of this counter prior to releasing its database lock. Then after
1149 acquiring the next database lock it compares the saved counter value
1150 against the current counter value and erases the cache if the values
1151 are different, or reuses the cache if they are the same.</p>
1152
1153 <a name="section_7_2"></a>
1154 <h2 id="_exclusive_access_mode"><span>7.2. </span> Exclusive Access Mode</h2>
1155
1156 <p>SQLite version 3.3.14 adds the concept of "Exclusive Access Mode".
1157 In exclusive access mode, SQLite retains the exclusive
1158 database lock at the conclusion of each transaction. This prevents
1159 other processes from accessing the database, but in many deployments
1160 only a single process is using a database so this is not a
1161 serious problem. The advantage of exclusive access mode is that
1162 disk I/O can be reduced in three ways:</p>
1163
1164 <ol>
1165 <li><p>It is not necessary to increment the change counter in the
1166 database header for transactions after the first transaction. This
1167 will often save a write of page one to both the rollback
1168 journal and the main database file.</p></li>
1169
1170 <li><p>No other processes can change the database so there is never
1171 a need to check the change counter and clear the user-space cache
1172 at the beginning of a transaction.</p></li>
1173
1174 <li><p>Each transaction can be committed by overwriting the rollback
1175 journal header with zeros rather than deleting the journal file.
1176 This avoids having to modify the directory entry for the journal file
1177 and it avoids having to deallocate disk sectors associated with the
1178 journal. Furthermore, the next transaction will overwrite existing
1179 journal file content rather than append new content and on most systems
1180 overwriting is much faster than appending.</p></li>
1181 </ol>
1182
1183 <p>The third optimization, zeroing the journal file header rather than
1184 deleting the rollback journal file,
1185 does not depend on holding an exclusive lock at all times.
1186 This optimization can be set independently of exclusive lock mode
1187 using the <a href="pragma.html#pragma_journal_mode">journal_mode pragma</a>
1188 as described in <a href="#section_7_6">section 7.6</a> below.</p>
1189
1190 <a name="freelistjrnl"></a>
1191
1192 <h2 id="_do_not_journal_freelist_pages"><span>7.3. </span> Do Not Journal Freelist Pages</h2>
1193
1194 <p>When information is deleted from an SQLite database, the pages used
1195 to hold the deleted information are added to a "<a href="fileformat2.html#freelist">freelist</a>". Subsequent
1196 inserts will draw pages off of this freelist rather than expanding the
1197 database file.</p>
1198
1199 <p>Some freelist pages contain critical data; specifically the locations
1200 of other freelist pages. But most freelist pages contain nothing useful.
1201 These latter freelist pages are called "leaf" pages. We are free to
1202 modify the content of a leaf freelist page in the database without
1203 changing the meaning of the database in any way.</p>
1204
1205 <p>Because the content of leaf freelist pages is unimportant, SQLite
1206 avoids storing leaf freelist page content in the rollback journal
1207 in <a href="#section_3_5">step 3.5</a> of the commit process.
1208 If a leaf freelist page is changed and that change does not get rolled back
1209 during a transaction recovery, the database is not harmed by the omission.
1210 Similarly, the content of a new freelist page is never written back
1211 into the database at <a href="#section_3_9">step 3.9</a> nor
1212 read from the database at <a href="#section_3_3">step 3.3</a>.
1213 These optimizations can greatly reduce the amount of I/O that occurs
1214 when making changes to a database file that contains free space.</p>
1215
1216 <a name="atomicsector"></a>
1217
1218 <h2 id="_single_page_updates_and_atomic_sector_writes"><span>7.4. </span> Single Page Updates And Atomic Sector Writes</h2>
1219
1220 <p>Beginning in SQLite version 3.5.0, the new Virtual File System (VFS)
1221 interface contains a method named xDeviceCharacteristics which reports
1222 on special properties that the underlying mass storage device
1223 might have. Among the special properties that
1224 xDeviceCharacteristics might report is the ability to do an
1225 atomic sector write.</p>
1226
1227 <p>Recall that by default SQLite assumes that sector writes are
1228 linear but not atomic. A linear write starts at one end of the
1229 sector and changes information byte by byte until it gets to the
1230 other end of the sector. If a power loss occurs in the middle of
1231 a linear write then part of the sector might be modified while the
1232 other end is unchanged. In an atomic sector write, either the entire
1233 sector is overwritten or else nothing in the sector is changed.</p>
1234
1235 <p>We believe that most modern disk drives implement atomic sector
1236 writes. When power is lost, the drive uses energy stored in capacitors
1237 and/or the angular momentum of the disk platter to provide power to
1238 complete any operation in progress. Nevertheless, there are so many
1239 layers in between the write system call and the on-board disk drive
1240 electronics that we take the safe approach in both Unix and w32 VFS
1241 implementations and assume that sector writes are not atomic. On the
1242 other hand, device
1243 manufacturers with more control over their filesystems might want
1244 to consider enabling the atomic write property of xDeviceCharacteristics
1245 if their hardware really does do atomic writes.</p>
1246
1247 <p>When sector writes are atomic and the page size of a database is
1248 the same as a sector size, and when there is a database change that
1249 only touches a single database page, then SQLite skips the whole
1250 journaling and syncing process and simply writes the modified page
1251 directly into the database file. The change counter in the first
1252 page of the database file is modified separately since no harm is
1253 done if power is lost before the change counter can be updated.</p>
1254
1255 <a name="safeappend"></a>
1256
1257 <h2 id="_filesystems_with_safe_append_semantics"><span>7.5. </span> Filesystems With Safe Append Semantics</h2>
1258
1259 <p>Another optimization introduced in SQLite version 3.5.0 makes
1260 use of "safe append" behavior of the underlying disk.
1261 Recall that SQLite assumes that when data is appended to a file
1262 (specifically to the rollback journal) that the size of the file
1263 is increased first and that the content is written second. So
1264 if power is lost after the file size is increased but before the
1265 content is written, the file is left containing invalid "garbage"
1266 data. The xDeviceCharacteristics method of the VFS might, however,
1267 indicate that the filesystem implements "safe append" semantics.
1268 This means that the content is written before the file size is
1269 increased so that it is impossible for garbage to be introduced
1270 into the rollback journal by a power loss or system crash.</p>
1271
1272 <p>When safe append semantics are indicated for a filesystem,
1273 SQLite always stores the special value of -1 for the page count
1274 in the header of the rollback journal. The -1 page count value
1275 tells any process attempting to rollback the journal that the
1276 number of pages in the journal should be computed from the journal
1277 size. This -1 value is never changed. So that when a commit
1278 occurs, we save a single flush operation and a sector write of
1279 the first page of the journal file. Furthermore, when a cache
1280 spill occurs we no longer need to append a new journal header
1281 to the end of the journal; we can simply continue appending
1282 new pages to the end of the existing journal.</p>
1283
1284 <a name="section_7_6"></a>
1285 <h2 id="_persistent_rollback_journals"><span>7.6. </span> Persistent Rollback Journals</h2>
1286
1287 <p>Deleting a file is an expensive operation on many systems.
1288 So as an optimization, SQLite can be configured to avoid the
1289 delete operation of <a href="#section_3_11">section 3.11</a>.
1290 Instead of deleting the journal file in order to commit a transaction,
1291 the file is either truncated to zero bytes in length or its
1292 header is overwritten with zeros. Truncating the file to zero
1293 length saves having to make modifications to the directory containing
1294 the file since the file is not removed from the directory.
1295 Overwriting the header has the additional savings of not having
1296 to update the length of the file (in the "inode" on many systems)
1297 and not having to deal with newly freed disk sectors. Furthermore,
1298 at the next transaction the journal will be created by overwriting
1299 existing content rather than appending new content onto the end
1300 of a file, and overwriting is often much faster than appending.</p>
1301
1302 <p>SQLite can be configured to commit transactions by overwriting
1303 the journal header with zeros instead of deleting the journal file
1304 by setting the "PERSIST" journaling mode using the
1305 <a href="pragma.html#pragma_journal_mode">journal_mode</a> PRAGMA.
1306 For example:</p>
1307
1308 <blockquote><pre>
1309 PRAGMA journal_mode=PERSIST;
1310 </pre></blockquote>
1311
1312 <p>The use of persistent journal mode provides a noticeable performance
1313 improvement on many systems. Of course, the drawback is that the
1314 journal files remain on the disk, using disk space and cluttering
1315 directories, long after the transaction commits. The only safe way
1316 to delete a persistent journal file is to commit a transaction
1317 with journaling mode set to DELETE:</p>
1318
1319 <blockquote><pre>
1320 PRAGMA journal_mode=DELETE;
1321 BEGIN EXCLUSIVE;
1322 COMMIT;
1323 </pre></blockquote>
1324
1325 <p>Beware of deleting persistent journal files by any other means
1326 since the journal file might be hot, in which case deleting it will
1327 corrupt the corresponding database file.</p>
1328
1329 <p>Beginning in SQLite <a href="releaselog/3_6_4.html">version 3.6.4</a> (2008-10-15),
1330 the TRUNCATE journal mode is
1331 also supported:</p>
1332
1333 <blockquote><pre>
1334 PRAGMA journal_mode=TRUNCATE;
1335 </pre></blockquote>
1336
1337 <p>In truncate journal mode, the transaction is committed by truncating
1338 the journal file to zero length rather than deleting the journal file
1339 (as in DELETE mode) or by zeroing the header (as in PERSIST mode).
1340 TRUNCATE mode shares the advantage of PERSIST mode that the directory
1341 that contains the journal file and database does not need to be updated.
1342 Hence truncating a file is often faster than deleting it. TRUNCATE has
1343 the additional advantage that it is not followed by a
1344 system call (ex: fsync()) to synchronize the change to disk. It might
1345 be safer if it did.
1346 But on many modern filesystems, a truncate is an atomic and
1347 synchronous operation and so we think that TRUNCATE will usually be safe
1348 in the face of power failures. If you are uncertain about whether or
1349 not TRUNCATE will be synchronous and atomic on your filesystem and it is
1350 important to you that your database survive a power loss or operating
1351 system crash that occurs during the truncation operation, then you might
1352 consider using a different journaling mode.</p>
1353
1354 <p>On embedded systems with synchronous filesystems, TRUNCATE results
1355 in slower behavior than PERSIST. The commit operation is the same speed.
1356 But subsequent transactions are slower following a TRUNCATE because it is
1357 faster to overwrite existing content than to append to the end of a file.
1358 New journal file entries will always be appended following a TRUNCATE but
1359 will usually overwrite with PERSIST.</p>
1360
1361 <a name="testing"></a>
1362
1363 <h1 id="_testing_atomic_commit_behavior"><span>8. </span> Testing Atomic Commit Behavior</h1>
1364
1365 <p>The developers of SQLite are confident that it is robust
1366 in the face of power failures and system crashes because the
1367 automatic test procedures do extensive checks on
1368 the ability of SQLite to recover from simulated power loss.
1369 We call these the "crash tests".</p>
1370
1371 <p>Crash tests in SQLite use a modified VFS that can simulate
1372 the kinds of filesystem damage that occur during a power
1373 loss or operating system crash. The crash-test VFS can simulate
1374 incomplete sector writes, pages filled with garbage data because
1375 a write has not completed, and out of order writes, all occurring
1376 at varying points during a test scenario. Crash tests execute
1377 transactions over and over, varying the time at which a simulated
1378 power loss occurs and the properties of the damage inflicted.
1379 Each test then reopens the database after the simulated crash and
1380 verifies that the transaction either occurred completely
1381 or not at all and that the database is in a completely
1382 consistent state.</p>
1383
1384 <p>The crash tests in SQLite have discovered a number of very
1385 subtle bugs (now fixed) in the recovery mechanism. Some of
1386 these bugs were very obscure and unlikely to have been found
1387 using only code inspection and analysis techniques. From this
1388 experience, the developers of SQLite feel confident that any other
1389 database system that does not use a similar crash test system
1390 likely contains undetected bugs that will lead to database
1391 corruption following a system crash or power failure.</p>
1392
1393 <a name="sect_9_0"></a>
1394
1395 <h1 id="_things_that_can_go_wrong"><span>9. </span> Things That Can Go Wrong</h1>
1396
1397 <p>The atomic commit mechanism in SQLite has proven to be robust,
1398 but it can be circumvented by a sufficiently creative
1399 adversary or a sufficiently broken operating system implementation.
1400 This section describes a few of the ways in which an SQLite database
1401 might be corrupted by a power failure or system crash.
1402 (See also: <a href="howtocorrupt.html">How To Corrupt Your Database Files</a>.)</p>
1403
1404 <a name="brokenlocks"></a>
1405
1406 <h2 id="_broken_locking_implementations"><span>9.1. </span> Broken Locking Implementations</h2>
1407
1408 <p>SQLite uses filesystem locks to make sure that only one
1409 process and database connection is trying to modify the database
1410 at a time. The filesystem locking mechanism is implemented
1411 in the VFS layer and is different for every operating system.
1412 SQLite depends on this implementation being correct. If something
1413 goes wrong and two or more processes are able to write the same
1414 database file at the same time, severe damage can result.</p>
1415
1416 <p>We have received reports of implementations of both
1417 Windows network filesystems and NFS in which locking was
1418 subtly broken. We can not verify these reports, but as
1419 locking is difficult to get right on a network filesystem
1420 we have no reason to doubt them. You are advised to
1421 avoid using SQLite on a network filesystem in the first place,
1422 since performance will be slow. But if you must use a
1423 network filesystem to store SQLite database files, consider
1424 using a secondary locking mechanism to prevent simultaneous
1425 writes to the same database even if the native filesystem
1426 locking mechanism malfunctions.</p>
1427
1428 <p>The versions of SQLite that come preinstalled on Apple
1429 Mac OS X computers contain a version of SQLite that has been
1430 extended to use alternative locking strategies that work on
1431 all network filesystems that Apple supports. These extensions
1432 used by Apple work great as long as all processes are accessing
1433 the database file in the same way. Unfortunately, the locking
1434 mechanisms do not exclude one another, so if one process is
1435 accessing a file using (for example) AFP locking and another
1436 process (perhaps on a different machine) is using dot-file locks,
1437 the two processes might collide because AFP locks do not exclude
1438 dot-file locks or vice versa.</p>
1439
1440 <a name="fsync"></a>
1441
1442 <h2 id="_incomplete_disk_flushes"><span>9.2. </span> Incomplete Disk Flushes</h2>
1443
1444 <p>SQLite uses the fsync() system call on Unix and the FlushFileBuffers()
1445 system call on w32 in order to sync the file system buffers onto disk
1446 oxide as shown in <a href="#section_3_7">step 3.7</a> and
1447 <a href="#section_3_10">step 3.10</a>. Unfortunately, we have received
1448 reports that neither of these interfaces works as advertised on many
1449 systems. We hear that FlushFileBuffers() can be completely disabled
1450 using registry settings on some Windows versions. Some historical
1451 versions of Linux contain versions of fsync() which are no-ops on
1452 some filesystems, we are told. Even on systems where
1453 FlushFileBuffers() and fsync() are said to be working, often
1454 the IDE disk control lies and says that data has reached oxide
1455 while it is still held only in the volatile control cache.</p>
1456
1457 <p>On the Mac, you can set this pragma:</p>
1458
1459 <blockquote>
1460 PRAGMA fullfsync=ON;
1461 </blockquote>
1462
1463 <p>Setting fullfsync on a Mac will guarantee that data really does
1464 get pushed out to the disk platter on a flush. But the implementation
1465 of fullfsync involves resetting the disk controller. And so not only
1466 is it profoundly slow, it also slows down other unrelated disk I/O.
1467 So its use is not recommended.</p>
1468
1469 <a name="filedel"></a>
1470
1471 <h2 id="_partial_file_deletions"><span>9.3. </span> Partial File Deletions</h2>
1472
1473 <p>SQLite assumes that file deletion is an atomic operation from the
1474 point of view of a user process. If power fails in the middle of
1475 a file deletion, then after power is restored SQLite expects to see
1476 either the entire file with all of its original data intact, or it
1477 expects not to find the file at all. Transactions may not be atomic
1478 on systems that do not work this way.</p>
1479
1480 <a name="filegarbage"></a>
1481
1482 <h2 id="_garbage_written_into_files"><span>9.4. </span> Garbage Written Into Files</h2>
1483
1484 <p>SQLite database files are ordinary disk files that can be
1485 opened and written by ordinary user processes. A rogue process
1486 can open an SQLite database and fill it with corrupt data.
1487 Corrupt data might also be introduced into an SQLite database
1488 by bugs in the operating system or disk controller; especially
1489 bugs triggered by a power failure. There is nothing SQLite can
1490 do to defend against these kinds of problems.</p>
1491
1492 <a name="mvhotjrnl"></a>
1493
1494 <h2 id="_deleting_or_renaming_a_hot_journal"><span>9.5. </span> Deleting Or Renaming A Hot Journal</h2>
1495
1496 <p>If a crash or power loss does occur and a hot journal is left on
1497 the disk, it is essential that the original database file and the hot
1498 journal remain on disk with their original names until the database
1499 file is opened by another SQLite process and rolled back.
1500 During recovery at <a href="#section_4_2">step 4.2</a> SQLite locates
1501 the hot journal by looking for a file in the same directory as the
1502 database being opened and whose name is derived from the name of the
1503 file being opened. If either the original database file or the
1504 hot journal have been moved or renamed, then the hot journal will
1505 not be seen and the database will not be rolled back.</p>
1506
1507 <p>We suspect that a common failure mode for SQLite recovery happens
1508 like this: A power failure occurs. After power is restored, a well-meaning
1509 user or system administrator begins looking around on the disk for
1510 damage. They see their database file named "important.data". This file
1511 is perhaps familiar to them. But after the crash, there is also a
1512 hot journal named "important.data-journal". The user then deletes
1513 the hot journal, thinking that they are helping to cleanup the system.
1514 We know of no way to prevent this other than user education.</p>
1515
1516 <p>If there are multiple (hard or symbolic) links to a database file,
1517 the journal will be created using the name of the link through which
1518 the file was opened. If a crash occurs and the database is opened again
1519 using a different link, the hot journal will not be located and no
1520 rollback will occur.</p>
1521
1522 <p>Sometimes a power failure will cause a filesystem to be corrupted
1523 such that recently changed filenames are forgotten and the file is
1524 moved into a "/lost+found" directory. When that happens, the hot
1525 journal will not be found and recovery will not occur.
1526 SQLite tries to prevent this
1527 by opening and syncing the directory containing the rollback journal
1528 at the same time it syncs the journal file itself. However, the
1529 movement of files into /lost+found can be caused by unrelated processes
1530 creating unrelated files in the same directory as the main database file.
1531 And since this is out from under the control of SQLite, there is nothing
1532 that SQLite can do to prevent it. If you are running on a system that
1533 is vulnerable to this kind of filesystem namespace corruption (most
1534 modern journalling filesystems are immune, we believe) then you might
1535 want to consider putting each SQLite database file in its own private
1536 subdirectory.</p>
1537
1538 <a name="future"></a>
1539
1540 <h1 id="_future_directions_and_conclusion"><span>10. </span> Future Directions And Conclusion</h1>
1541
1542 <p>Every now and then someone discovers a new failure mode for
1543 the atomic commit mechanism in SQLite and the developers have to
1544 put in a patch. This is happening less and less and the
1545 failure modes are becoming more and more obscure. But it would
1546 still be foolish to suppose that the atomic commit logic of
1547 SQLite is entirely bug-free. The developers are committed to fixing
1548 these bugs as quickly as they might be found.</p>
1549
1550 <p>
1551 The developers are also on the lookout for new ways to
1552 optimize the commit mechanism. The current VFS implementations
1553 for Unix (Linux and Mac OS X) and Windows make pessimistic assumptions about
1554 the behavior of those systems. After consultation with experts
1555 on how these systems work, we might be able to relax some of the
1556 assumptions on these systems and allow them to run faster. In
1557 particular, we suspect that most modern filesystems exhibit the
1558 safe append property and that many of them might support atomic
1559 sector writes. But until this is known for certain, SQLite will
1560 take the conservative approach and assume the worst.</p>
1561 <p align="center"><small><i>This page was last updated on 2026-04-21 10:28:59Z </small></i></p>
1562