Debian Codegraph @

www/wal.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>Write-Ahead Logging</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 Write-Ahead Logging
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
126 <div class="fancy-toc1"><a href="#how_wal_works">2. How WAL Works</a></div>
127 <div class="fancy-toc2"><a href="#checkpointing">2.1. Checkpointing</a></div>
128 <div class="fancy-toc2"><a href="#concurrency">2.2. Concurrency</a></div>
129 <div class="fancy-toc2"><a href="#performance_considerations">2.3. Performance Considerations</a></div>
130 <div class="fancy-toc1"><a href="#activating_and_configuring_wal_mode">3. Activating And Configuring WAL Mode</a></div>
131 <div class="fancy-toc2"><a href="#automatic_checkpoint">3.1. Automatic Checkpoint</a></div>
132 <div class="fancy-toc2"><a href="#application_initiated_checkpoints">3.2. Application-Initiated Checkpoints</a></div>
133 <div class="fancy-toc2"><a href="#persistence_of_wal_mode">3.3. Persistence of WAL mode</a></div>
134 <div class="fancy-toc1"><a href="#the_wal_file">4. The WAL File</a></div>
135 <div class="fancy-toc1"><a href="#read_only_databases">5. Read-Only Databases</a></div>
136 <div class="fancy-toc1"><a href="#avoiding_excessively_large_wal_files">6. Avoiding Excessively Large WAL Files</a></div>
137 <div class="fancy-toc1"><a href="#implementation_of_shared_memory_for_the_wal_index">7. Implementation Of Shared-Memory For The WAL-Index</a></div>
138 <div class="fancy-toc1"><a href="#use_of_wal_without_shared_memory">8. Use of WAL Without Shared-Memory</a></div>
139 <div class="fancy-toc1"><a href="#sometimes_queries_return_sqlite_busy_in_wal_mode">9. Sometimes Queries Return SQLITE_BUSY In WAL Mode</a></div>
140 <div class="fancy-toc1"><a href="#backwards_compatibility">10. Backwards Compatibility</a></div>
141 <div class="fancy-toc1"><a href="#the_wal_reset_bug">11. The WAL-Reset Bug</a></div>
142 <div class="fancy-toc2"><a href="#bug_details">11.1. Bug Details</a></div>
143 <div class="fancy-toc2"><a href="#low_probability_of_occurrence">11.2. Low Probability Of Occurrence</a></div>
144 </div>
145 </details>
146 </div>
147
148
149
150
151
152 <h1 id="overview"><span>1. </span>Overview</h1>
153
154 <p>The default method by which SQLite implements
155 <a href="atomiccommit.html">atomic commit and rollback</a> is a <a href="lockingv3.html#rollback">rollback journal</a>.
156 Beginning with <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21), a new "Write-Ahead Log" option
157 (hereafter referred to as "WAL") is available.</p>
158
159 <p>There are advantages and disadvantages to using WAL instead of
160 a rollback journal. Advantages include:</p>
161
162 <a name="advantages"></a>
163
164 <ol>
165 <li>WAL is significantly faster in most scenarios.
166 </li><li>WAL provides more concurrency as readers do not block writers and
167 a writer does not block readers. Reading and writing can proceed
168 concurrently.
169 </li><li>Disk I/O operations tends to be more sequential using WAL.
170 </li><li>WAL uses many fewer fsync() operations and is thus less vulnerable to
171 problems on systems where the fsync() system call is broken.
172 </li></ol>
173
174 <p>But there are also disadvantages:</p>
175
176 <ol>
177 <li>All processes using a database must be on the same host computer;
178 WAL does not work over a network filesystem. This is because WAL requires
179 all processes to share a small amount of memory and processes on
180 separate host machines obviously cannot share memory with each other.
181 </li><li>Transactions that involve changes against multiple <a href="lang_attach.html">ATTACHed</a>
182 databases are atomic for each individual database, but are not
183 atomic across all databases as a set.
184 </li><li>It is not possible to change the <a href="pragma.html#pragma_page_size">page_size</a> after entering WAL
185 mode, either on an empty database or by using <a href="lang_vacuum.html">VACUUM</a> or by restoring
186 from a backup using the <a href="backup.html">backup API</a>. You must be in a rollback journal
187 mode to change the page size.
188 </li><li><s>It is not possible to open <a href="wal.html#readonly">read-only WAL databases</a>.
189 The opening process must have write privileges for "<tt>-shm</tt>"
190 <a href="walformat.html#shm">wal-index</a> shared memory file associated with the database, if that
191 file exists, or else write access on the directory containing
192 the database file if the "<tt>-shm</tt>" file does not exist.</s>
193 Beginning with <a href="releaselog/3_22_0.html">version 3.22.0</a> (2018-01-22), a read-only
194 WAL-mode database file can be opened if
195 the <tt>-shm</tt> and <tt>-wal</tt> files
196 already exist or those files can be created or the
197 <a href="uri.html#uriimmutable">database is immutable</a>.
198 </li><li>WAL might be very slightly slower (perhaps 1% or 2% slower)
199 than the traditional rollback-journal approach
200 in applications that do mostly reads and seldom write.
201 </li><li>There is an additional quasi-persistent "<tt>-wal</tt>" file and
202 "<tt>-shm</tt>" shared memory file associated with each
203 database, which can make SQLite less appealing for use as an
204 <a href="appfileformat.html">application file-format</a>.
205 </li><li>There is the extra operation of <a href="wal.html#ckpt">checkpointing</a> which, though automatic
206 by default, is still something that application developers need to
207 be mindful of.
208 </li><li><s>WAL works best with smaller transactions. WAL does
209 not work well for very large transactions. For transactions larger than
210 about 100 megabytes, traditional rollback journal modes will likely
211 be faster. For transactions in excess of a gigabyte, WAL mode may
212 fail with an I/O or disk-full error.
213 It is recommended that one of the rollback journal modes be used for
214 transactions larger than a few dozen megabytes.</s>
215 Beginning with <a href="releaselog/3_11_0.html">version 3.11.0</a> (2016-02-15),
216 WAL mode works as efficiently with
217 large transactions as does rollback mode.
218
219 </li></ol>
220
221 <h1 id="how_wal_works"><span>2. </span>How WAL Works</h1>
222
223 <p>The traditional rollback journal works by writing a copy of the
224 original unchanged database content into a separate rollback journal file
225 and then writing changes directly into the database file. In the
226 event of a crash or <a href="lang_transaction.html">ROLLBACK</a>, the original content contained in the
227 rollback journal is played back into the database file to
228 revert the database file to its original state. The <a href="lang_transaction.html">COMMIT</a> occurs
229 when the rollback journal is deleted.</p>
230
231 <p>The WAL approach inverts this. The original content is preserved
232 in the database file and the changes are appended into a separate
233 WAL file. A <a href="lang_transaction.html">COMMIT</a> occurs when a special record indicating a commit
234 is appended to the WAL. Thus a COMMIT can happen without ever writing
235 to the original database, which allows readers to continue operating
236 from the original unaltered database while changes are simultaneously being
237 committed into the WAL. Multiple transactions can be appended to the
238 end of a single WAL file.</p>
239
240 <a name="ckpt"></a>
241
242 <h2 id="checkpointing"><span>2.1. </span>Checkpointing</h2>
243
244 <p>Of course, one wants to eventually transfer all the transactions that
245 are appended in the WAL file back into the original database. Moving
246 the WAL file transactions back into the database is called a
247 "<i>checkpoint</i>".</p><p>
248
249 </p><p>Another way to think about the difference between rollback and
250 write-ahead log is that in the rollback-journal
251 approach, there are two primitive operations, reading and writing,
252 whereas with a write-ahead log
253 there are now three primitive operations: reading, writing, and
254 checkpointing.</p>
255
256 <p>By default, SQLite does a checkpoint automatically when the WAL file
257 reaches a threshold size of 1000 pages. (The
258 <a href="compile.html#default_wal_autocheckpoint">SQLITE_DEFAULT_WAL_AUTOCHECKPOINT</a> compile-time option can be used to
259 specify a different default.) Applications using WAL do
260 not have to do anything in order for these checkpoints to occur.
261 But if they want to, applications can adjust the automatic checkpoint
262 threshold. Or they can turn off the automatic checkpoints and run
263 checkpoints during idle moments or in a separate thread or process.</p>
264
265 <a name="concurrency"></a>
266
267 <h2 id="concurrency"><span>2.2. </span>Concurrency</h2>
268
269 <p>When a read operation begins on a WAL-mode database, it first
270 remembers the location of the last valid commit record in the WAL.
271 Call this point the "end mark". Because the WAL can be growing and
272 adding new commit records while various readers connect to the database,
273 each reader can potentially have its own end mark. But for any
274 particular reader, the end mark is unchanged for the duration of the
275 transaction, thus ensuring that a single read transaction only sees
276 the database content as it existed at a single point in time.</p>
277
278 <p>When a reader needs a page of content, it first checks the WAL to
279 see if that page appears there, and if so it pulls in the last copy
280 of the page that occurs in the WAL prior to the reader's end mark.
281 If no copy of the page exists in the WAL prior to the reader's end mark,
282 then the page is read from the original database file. Readers can
283 exist in separate processes, so to avoid forcing every reader to scan
284 the entire WAL looking for pages (the WAL file can grow to
285 multiple megabytes, depending on how often checkpoints are run), a
286 data structure called the "wal-index" is maintained in shared memory
287 which helps readers locate pages in the WAL quickly and with a minimum
288 of I/O. The wal-index greatly improves the performance of readers,
289 but the use of shared memory means that all readers must exist on the
290 same machine. This is why the write-ahead log implementation will not
291 work on a network filesystem.</p>
292
293 <p>Writers merely append new content to the end of the WAL file.
294 Because writers do nothing that would interfere with the actions of
295 readers, writers and readers can run at the same time. However,
296 since there is only one WAL file, there can only be one writer at
297 a time.</p>
298
299 <p>A checkpoint operation takes content from the WAL file
300 and transfers it back into the original database file.
301 A checkpoint can run concurrently with readers, however the checkpoint
302 must stop when it reaches a page in the WAL that is past the end mark
303 of any current reader. The checkpoint has to stop at that point because
304 otherwise it might overwrite part of the database file that the reader
305 is actively using. The checkpoint remembers (in the wal-index) how far
306 it got and will resume transferring content from the WAL to the database
307 from where it left off on the next invocation.</p>
308
309 <p>Thus a long-running read transaction can prevent a checkpointer from
310 making progress. But presumably every read transaction will eventually
311 end and the checkpointer will be able to continue.</p>
312
313 <p>Whenever a write operation occurs, the writer checks how much progress
314 the checkpointer has made, and if the entire WAL has been transferred into
315 the database and synced and if no readers are making use of the WAL, then
316 the writer will rewind the WAL back to the beginning and start putting new
317 transactions at the beginning of the WAL. This mechanism prevents a WAL
318 file from growing without bound.</p>
319
320 <a name="fast"></a>
321
322 <h2 id="performance_considerations"><span>2.3. </span>Performance Considerations</h2>
323
324 <p>Write transactions are very fast since they only involve writing
325 the content once (versus twice for rollback-journal transactions)
326 and because the writes are all sequential. Further, syncing the
327 content to the disk is not required, as long as the application is
328 willing to sacrifice durability following a power loss or hard reboot.
329 (Writers sync the WAL on every transaction commit if
330 <a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> is set to FULL but omit this sync if
331 <a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> is set to NORMAL.)</p>
332
333 <p>On the other hand, read performance deteriorates as the WAL file
334 grows in size since each reader must check the WAL file for the content
335 and the time needed to check the WAL file is proportional
336 to the size of the WAL file. The wal-index helps find content
337 in the WAL file much faster, but performance still falls off with
338 increasing WAL file size. Hence, to maintain good read performance
339 it is important to keep the WAL file size down by
340 running checkpoints at regular intervals.</p>
341
342 <p>Checkpointing does require sync operations in order to avoid
343 the possibility of database corruption following a power loss
344 or hard reboot. The WAL must be synced to persistent storage
345 prior to moving content from the WAL into the database and the
346 database file must be synced prior to resetting the WAL.
347 Checkpoint also requires more seeking.
348 The checkpointer makes an effort to
349 do as many sequential page writes to the database as it can (the pages
350 are transferred from WAL to database in ascending order) but even
351 then there will typically be many seek operations interspersed among
352 the page writes. These factors combine to make checkpoints slower than
353 write transactions.</p>
354
355 <p>The default strategy is to allow successive write transactions to
356 grow the WAL until the WAL becomes about 1000 pages in size, then to
357 run a checkpoint operation for each subsequent COMMIT until the WAL
358 is reset to be smaller than 1000 pages. By default, the checkpoint will be
359 run automatically by the same thread that does the COMMIT that pushes
360 the WAL over its size limit. This has the effect of causing most
361 COMMIT operations to be very fast but an occasional COMMIT (those that trigger
362 a checkpoint) to be much slower. If that effect is undesirable, then
363 the application can disable automatic checkpointing and run the
364 periodic checkpoints in a separate thread, or separate process.
365 (Links to commands and interfaces to accomplish this are
366 <a href="#how_to_checkpoint">shown below</a>.)</p>
367
368
369 <p>Note that with <a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> set to NORMAL, the checkpoint
370 is the only operation to issue an I/O barrier or sync operation
371 (fsync() on unix or FlushFileBuffers() on windows). If an application
372 therefore runs checkpoint in a separate thread or process, the main
373 thread or process that is doing database queries and updates will never
374 block on a sync operation. This helps to prevent "latch-up" in applications
375 running on a busy disk drive. The downside to
376 this configuration is that transactions are no longer durable and
377 might rollback following a power failure or hard reset.</p>
378
379
380 <p>Notice too that there is a tradeoff between average read performance
381 and average write performance. To maximize the read performance,
382 one wants to keep the WAL as small as possible and hence run checkpoints
383 frequently, perhaps as often as every COMMIT. To maximize
384 write performance, one wants to amortize the cost of each checkpoint
385 over as many writes as possible, meaning that one wants to run checkpoints
386 infrequently and let the WAL grow as large as possible before each
387 checkpoint. The decision of how often to run checkpoints may therefore
388 vary from one application to another depending on the relative read
389 and write performance requirements of the application.
390 The default strategy is to run a checkpoint once the WAL
391 reaches 1000 pages and this strategy seems to work well in test applications on
392 workstations, but other strategies might work better on different
393 platforms or for different workloads.</p>
394
395 <h1 id="activating_and_configuring_wal_mode"><span>3. </span>Activating And Configuring WAL Mode</h1>
396
397 <p>An SQLite database connection defaults to
398 <a href="pragma.html#pragma_journal_mode">journal_mode=DELETE</a>. To convert to WAL mode, use the
399 following pragma:</p>
400
401 <blockquote><pre>
402 PRAGMA journal_mode=WAL;
403 </pre></blockquote>
404
405 <p>The journal_mode pragma returns a string which is the new journal mode.
406 On success, the pragma will return the string "<tt>wal</tt>". If
407 the conversion to WAL could not be completed (for example, if the <a href="vfs.html">VFS</a>
408 does not support the necessary shared-memory primitives) then the
409 journaling mode will be unchanged and the string returned from the
410 primitive will be the prior journaling mode (for example "<tt>delete</tt>").
411
412 <a name="how_to_checkpoint"></a>
413 </p><h2 id="automatic_checkpoint"><span>3.1. </span>Automatic Checkpoint</h2>
414
415 <p>By default, SQLite will automatically checkpoint whenever a <a href="lang_transaction.html">COMMIT</a>
416 occurs that causes the WAL file to be 1000 pages or more in size, or when the
417 last database connection on a database file closes. The default
418 configuration is intended to work well for most applications.
419 But programs that want more control can force a checkpoint
420 using the <a href="pragma.html#pragma_wal_checkpoint">wal_checkpoint pragma</a> or by calling the
421 <a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface. The automatic checkpoint
422 threshold can be changed or automatic checkpointing can be completely
423 disabled using the <a href="pragma.html#pragma_wal_autocheckpoint">wal_autocheckpoint pragma</a> or by calling the
424 <a href="c3ref/wal_autocheckpoint.html">sqlite3_wal_autocheckpoint()</a> C interface. A program can also
425 use <a href="c3ref/wal_hook.html">sqlite3_wal_hook()</a> to register a callback to be invoked whenever
426 any transaction commits to the WAL. This callback can then invoke
427 <a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> or <a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> based on whatever
428 criteria it thinks is appropriate. (The automatic checkpoint mechanism
429 is implemented as a simple wrapper around <a href="c3ref/wal_hook.html">sqlite3_wal_hook()</a>.)</p>
430
431 <h2 id="application_initiated_checkpoints"><span>3.2. </span>Application-Initiated Checkpoints</h2>
432
433 <p>An application can initiate a checkpoint using any writable database
434 connection on the database simply by invoking
435 <a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> or <a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a>.
436 There are three subtypes of checkpoints that vary in their aggressiveness:
437 PASSIVE, FULL, and RESTART. The default checkpoint style is PASSIVE, which
438 does as much work as it can without interfering with other database
439 connections, and which might not run to completion if there are
440 concurrent readers or writers.
441 All checkpoints initiated by <a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> and
442 by the automatic checkpoint mechanism are PASSIVE. FULL and RESTART
443 checkpoints try harder to run the checkpoint to completion and can only
444 be initiated by a call to <a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a>. See the
445 <a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> documentation for additional information
446 on FULL and RESET checkpoints.
447
448 </p><h2 id="persistence_of_wal_mode"><span>3.3. </span>Persistence of WAL mode</h2>
449
450 <p>Unlike the other journaling modes,
451 <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=WAL</a> is
452 persistent. If a process sets WAL mode, then closes and reopens the
453 database, the database will come back in WAL mode. In contrast, if
454 a process sets (for example) PRAGMA journal_mode=TRUNCATE and then closes and
455 reopens the database will come back up in the default rollback mode of
456 DELETE rather than the previous TRUNCATE setting.</p>
457
458 <p>The persistence of WAL mode means that applications can be converted
459 to using SQLite in WAL mode without making any changes to the application
460 itself. One has merely to run "<tt>PRAGMA journal_mode=WAL;</tt>" on the
461 database file(s) using the <a href="cli.html">command-line shell</a> or other utility, then
462 restart the application.</p>
463
464 <p>The WAL journal mode will be set on all
465 connections to the same database file if it is set on any one connection.
466 </p>
467
468 <a name="walfile"></a>
469
470 <h1 id="the_wal_file"><span>4. </span>The WAL File</h1>
471
472 <p>While a <a href="c3ref/sqlite3.html">database connection</a> is open on a WAL-mode database, SQLite
473 maintains an extra journal file called a "Write Ahead Log" or "WAL File".
474 The name of this file on disk is usually the name of the database file
475 with an extra "<tt>-wal</tt>" suffix, though different naming rules may
476 apply if SQLite is compiled with <a href="compile.html#enable_8_3_names">SQLITE_ENABLE_8_3_NAMES</a>.
477
478 </p><p>The WAL file exists for as long as any <a href="c3ref/sqlite3.html">database connection</a> has the
479 database open. Usually, the WAL file is deleted automatically when the
480 last connection to the database closes. However, if the last process to
481 have the database open exits without cleanly
482 shutting down the database connection, or if the
483 <a href="c3ref/c_fcntl_begin_atomic_write.html#sqlitefcntlpersistwal">SQLITE_FCNTL_PERSIST_WAL</a> <a href="c3ref/file_control.html">file control</a> is used, then the WAL file
484 might be retained on disk after all connections to the database have
485 been closed. The WAL file is part of the persistent state of the
486 database and should be kept with the database if the database is copied
487 or moved. If a database file is separated from its WAL file, then
488 transactions that were previously committed to the database might be lost,
489 or the database file might become corrupted.
490 The only safe way to remove a WAL file is
491 to open the database file using one of the <a href="c3ref/open.html">sqlite3_open()</a> interfaces
492 then immediately close the database using <a href="c3ref/close.html">sqlite3_close()</a>.
493
494 </p><p>The <a href="fileformat2.html#walformat">WAL file format</a> is precisely defined and is cross-platform.
495
496 <a name="readonly"></a>
497
498 </p><h1 id="read_only_databases"><span>5. </span>Read-Only Databases</h1>
499
500 <p>Older versions of SQLite could not read a WAL-mode database that was
501 read-only. In other words, write access was required in order to read a
502 WAL-mode database. This constraint was relaxed beginning with
503 SQLite <a href="releaselog/3_22_0.html">version 3.22.0</a> (2018-01-22).
504
505 </p><p>On newer versions of SQLite,
506 a WAL-mode database on read-only media, or a WAL-mode database that lacks
507 write permission, can still be read as long as one or more of the following
508 conditions are met:
509 </p><ol>
510 <li>The <tt>-shm</tt> and <tt>-wal</tt> files already exist and are readable.
511 </li><li>There is write permission on the directory containing the database so
512 that the <tt>-shm</tt> and <tt>-wal</tt> files can be created.
513 </li><li>The database connection is opened using the
514 <a href="uri.html#uriimmutable">immutable query parameter</a>.
515 </li></ol>
516
517 <p>Even though it is possible to open a read-only WAL-mode database,
518 it is good practice to convert the database to
519 <a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=DELETE</a> prior to burning an
520 SQLite database image onto read-only media.</p>
521
522 <a name="bigwal"></a>
523
524 <h1 id="avoiding_excessively_large_wal_files"><span>6. </span>Avoiding Excessively Large WAL Files</h1>
525
526 <p>In normal cases, new content is appended to the WAL file until the
527 WAL file accumulates about 1000 pages (and is thus about 4MB
528 in size) at which point a checkpoint is automatically run and the WAL file
529 is recycled. The checkpoint does not normally truncate the WAL file
530 (unless the <a href="pragma.html#pragma_journal_size_limit">journal_size_limit pragma</a> is set). Instead, it merely
531 causes SQLite to start overwriting the WAL file from the beginning.
532 This is done because it is normally faster to overwrite an existing file
533 than to append. When the last connection to a database closes, that
534 connection does one last checkpoint and then deletes the WAL and its
535 associated shared-memory file, to clean up the disk.
536
537 </p><p>So in the vast majority of cases, applications need not worry about
538 the WAL file at all. SQLite will automatically take care of it. But
539 it is possible to get SQLite into a state where the WAL file will grow
540 without bound, causing excess disk space usage and slow query speeds.
541 The following bullets enumerate some of the ways that this can happen
542 and how to avoid them.
543
544 </p><ul>
545 <li><p>
546 <b>Disabling the automatic checkpoint mechanism.</b>
547 In its default configuration, SQLite will checkpoint the WAL file at the
548 conclusion of any transaction when the WAL file is more than 1000 pages
549 long. However, compile-time and run-time options exist that can disable
550 or defer this automatic checkpoint. If an application disables the
551 automatic checkpoint, then there is nothing to prevent the WAL file
552 from growing excessively.
553
554 </p></li><li><p>
555 <b>Checkpoint starvation.</b>
556 A checkpoint is only able to run to completion, and reset the WAL file,
557 if there are no other database connections using the WAL file. If another
558 connection has a read transaction open,
559 then the checkpoint cannot reset the WAL file because
560 doing so might delete content out from under the reader.
561 The checkpoint will do as much work as it can without upsetting the
562 reader, but it cannot run to completion.
563 The checkpoint will start up again where it left off after the next
564 write transaction. This repeats until some checkpoint is able to complete.
565
566 </p><p>However, if a database has many concurrent overlapping readers
567 and there is always at least one active reader, then
568 no checkpoints will be able to complete
569 and hence the WAL file will grow without bound.
570
571 </p><p>This scenario can be avoided by ensuring that there are "reader gaps":
572 times when no processes are reading from the
573 database and that checkpoints are attempted during those times.
574 In applications with many concurrent readers, one might also consider
575 running manual checkpoints with the <a href="c3ref/c_checkpoint_full.html">SQLITE_CHECKPOINT_RESTART</a> or
576 <a href="c3ref/c_checkpoint_full.html">SQLITE_CHECKPOINT_TRUNCATE</a> option which will ensure that the checkpoint
577 runs to completion before returning. The disadvantage of using
578 <a href="c3ref/c_checkpoint_full.html">SQLITE_CHECKPOINT_RESTART</a> and <a href="c3ref/c_checkpoint_full.html">SQLITE_CHECKPOINT_TRUNCATE</a> is that
579 readers might block while the checkpoint is running.
580
581 </p></li><li><p>
582 <b>Very large write transactions.</b>
583 A checkpoint can only complete when no other transactions are running,
584 which means the WAL file cannot be reset in the middle of a write
585 transaction. So a large change to a large database
586 might result in a large WAL file. The WAL file will be checkpointed
587 once the write transaction completes (assuming there are no other readers
588 blocking it) but in the meantime, the file can grow very big.
589
590 </p><p>As of SQLite <a href="releaselog/3_11_0.html">version 3.11.0</a> (2016-02-15),
591 the WAL file for a single transaction
592 should be proportional in size to the transaction itself. Pages that
593 are changed by the transaction should only be written into the WAL file
594 once. However, with older versions of SQLite, the same page might be
595 written into the WAL file multiple times if the transaction grows larger
596 than the page cache.
597 </p></li></ul>
598
599 <h1 id="implementation_of_shared_memory_for_the_wal_index"><span>7. </span>Implementation Of Shared-Memory For The WAL-Index</h1>
600
601 <p>The <a href="walformat.html#shm">wal-index</a> is implemented using an ordinary file that is
602 mmapped for robustness. Early (pre-release) implementations of WAL mode
603 stored the wal-index in volatile shared-memory, such as files created in
604 /dev/shm on Linux or /tmp on other unix systems. The problem
605 with that approach is that processes with a different root directory
606 (changed via <a href="http://en.wikipedia.org/wiki/Chroot">chroot</a>)
607 will see different files and hence use different shared memory areas,
608 leading to database corruption. Other methods for creating nameless
609 shared memory blocks are not portable across the various flavors of
610 unix. And we could not find any method to create nameless shared
611 memory blocks on windows. The only way we have found to guarantee
612 that all processes accessing the same database file use the same shared
613 memory is to create the shared memory by mmapping a file in the same
614 directory as the database itself.</p>
615
616 <p>Using an ordinary disk file to provide shared memory has the
617 disadvantage that it might actually do unnecessary disk I/O by
618 writing the shared memory to disk. However, the developers do not
619 think this is a major concern since the wal-index rarely exceeds
620 32 KiB in size and is never synced. Furthermore, the wal-index
621 backing file is deleted when the last database connection disconnects,
622 which often prevents any real disk I/O from ever happening.</p>
623
624 <p>Specialized applications for which the default implementation of
625 shared memory is unacceptable can devise alternative methods via a
626 custom <a href="vfs.html">VFS</a>.
627 For example, if it is known that a particular database
628 will only be accessed by threads within a single process, the wal-index
629 can be implemented using heap memory instead of true shared memory.</p>
630
631 <a name="noshm"></a>
632
633 <h1 id="use_of_wal_without_shared_memory"><span>8. </span>Use of WAL Without Shared-Memory</h1>
634
635 <p>Beginning in SQLite <a href="releaselog/3_7_4.html">version 3.7.4</a> (2010-12-07),
636 WAL databases can be created, read, and
637 written even if shared memory is unavailable as long as the
638 <a href="pragma.html#pragma_locking_mode">locking_mode</a> is set to EXCLUSIVE before the first attempted access.
639 In other words, a process can interact with
640 a WAL database without using shared memory if that
641 process is guaranteed to be the only process accessing the database.
642 This feature allows WAL databases to be created, read, and written
643 by legacy <a href="vfs.html">VFSes</a> that lack the "version 2" shared-memory
644 methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on the
645 <a href="c3ref/io_methods.html">sqlite3_io_methods</a> object.</p>
646
647 <p>If <a href="pragma.html#pragma_locking_mode">EXCLUSIVE locking mode</a>
648 is set prior to the first WAL-mode
649 database access, then SQLite never attempts to call any of the
650 shared-memory methods and hence no shared-memory
651 wal-index is ever created.
652 In that case, the database connection remains in EXCLUSIVE mode
653 as long as the journal mode is WAL; attempts to change the locking
654 mode using "<tt>PRAGMA locking_mode=NORMAL;</tt>" are no-ops.
655 The only way to change out of EXCLUSIVE locking mode is to first
656 change out of WAL journal mode.</p>
657
658 <p>If NORMAL locking mode is in effect for the first WAL-mode database
659 access, then the shared-memory wal-index is created. This means that the
660 underlying VFS must support the "version 2" shared-memory.
661 If the VFS does not support shared-memory methods, then the attempt to
662 open a database that is already in WAL mode, or the attempt to convert a
663 database into WAL mode, will fail.
664 As long as exactly one connection is using a shared-memory wal-index,
665 the locking mode can be changed freely between NORMAL and EXCLUSIVE.
666 It is only when the shared-memory wal-index is omitted, when the locking
667 mode is EXCLUSIVE prior to the first WAL-mode database access, that the
668 locking mode is stuck in EXCLUSIVE.</p>
669
670 <a name="busy"></a>
671
672 <h1 id="sometimes_queries_return_sqlite_busy_in_wal_mode"><span>9. </span>Sometimes Queries Return SQLITE_BUSY In WAL Mode</h1>
673
674 <p>The <a href="wal.html#advantages">second advantage of WAL-mode</a> is that
675 writers do not block readers and readers do not block writers.
676 This is <u>mostly</u> true.
677 But there are some obscure cases where a query against a WAL-mode
678 database can return <a href="rescode.html#busy">SQLITE_BUSY</a>, so applications should be prepared
679 for that happenstance.
680
681 </p><p>Cases where a query against a WAL-mode database can return <a href="rescode.html#busy">SQLITE_BUSY</a>
682 include the following:
683
684 </p><ul>
685 <li><p>If another database connection has the database mode open
686 in <a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> then all queries against the
687 database will return <a href="rescode.html#busy">SQLITE_BUSY</a>. Both Chrome and Firefox open their
688 database files in exclusive locking mode, so attempts to read Chrome or
689 Firefox databases while the applications are running will run into this
690 problem, for example.
691
692 </p></li><li><p> When the last connection to a particular database is closing,
693 that connection will acquire an exclusive lock for a short time while
694 it cleans up the WAL and shared-memory files. If a separate attempt
695 is made to open and query the database while the first connection is
696 still in the middle of its cleanup process, the second connection
697 might get an <a href="rescode.html#busy">SQLITE_BUSY</a> error.
698
699 </p></li><li><p>
700 If the last connection to a database crashed, then the first new
701 connection to open the database will start a recovery process. An
702 exclusive lock is held during recovery. So if a third database connection
703 tries to jump in and query while the second connection is running recovery,
704 the third connection will get an <a href="rescode.html#busy">SQLITE_BUSY</a> error.
705 </p></li></ul>
706
707 <a name="bkwrds"></a>
708
709 <h1 id="backwards_compatibility"><span>10. </span>Backwards Compatibility</h1>
710
711 <p>The database file format is unchanged for WAL mode. However, the
712 WAL file and the <a href="walformat.html#shm">wal-index</a> are new concepts and so older versions of
713 SQLite will not know
714 how to recover a crashed SQLite database that was operating in WAL mode
715 when the crash occurred.
716 To prevent older versions of SQLite (prior to version 3.7.0, 2010-07-22)
717 from trying to recover
718 a WAL-mode database (and making matters worse) the database file format
719 version numbers (bytes 18 and 19 in the <a href="fileformat2.html#database_header">database header</a>)
720 are increased from 1 to 2 in WAL mode.
721 Thus, if an older version of SQLite attempts to connect to an SQLite
722 database that is operating in WAL mode, it will report an error along
723 the lines of "file is encrypted or is not a database".</p>
724
725 <p>One can explicitly change out of WAL mode using a pragma such as
726 this:</p>
727
728 <blockquote><pre>
729 PRAGMA journal_mode=DELETE;
730 </pre></blockquote>
731
732
733 <p>Deliberately changing out of WAL mode changes the database file format
734 version numbers back to 1 so that older versions of SQLite can once again
735 access the database file.</p>
736
737 <a name="walresetbug"></a>
738
739 <h1 id="the_wal_reset_bug"><span>11. </span>The WAL-Reset Bug</h1>
740
741 <p>On 2026-03-03, one of the SQLite developers (Dan) found and fixed
742 a bug that could, in rare cases, lead to database corruption. We call
743 this the "WAL-reset bug".
744 Key points:
745
746 </p><ul>
747 <li><p>
748 The bug is likely present in all version of SQLite from
749 3.7.0 (2010-07-21) through 3.51.2 (2026-01-09). It is
750 fixed in version 3.51.3 (2026-03-13) and later. Backports of the fix
751 are available for some earlier releases:
752 <a href="https://sqlite.org/src/info/863c171b76cd36e0">3.44.6</a> and
753 <a href="https://sqlite.org/src/info/c7facf7ac58d2cda">3.50.7</a>.
754
755
756 </p></li><li><p>
757 The bug only affects databases in WAL mode when there are two or more
758 database connections open on the same file, in separate threads or processes,
759 and when those two connections attempt to write or checkpoint at the
760 same instant.
761
762 </p></li><li><p>
763 The bug is a data race with tight timing constraints. It is unlikely
764 to occur in common use. The developers have never been able to reproduce
765 the bug organically and had to add special testing logic to SQLite
766 that deliberately triggers the circumstances of the the bug in order
767 to verify that the issue has been fixed.
768 </p></li></ul>
769
770 <h2 id="bug_details"><span>11.1. </span>Bug Details</h2>
771
772 <p>This is what happens:
773
774 </p><ol>
775 <li><p>
776 One connection does a <a href="wal.html#ckpt">checkpoint</a>. This first checkpoint must be
777 complete. In other words, the checkpoint must successfully copy all
778 content from the WAL file back into the database and leave the WAL file
779 in a state where it can potentially be reset.
780
781 </p></li><li><p>
782 Shortly after the first checkpoint completes, a second checkpoint
783 is started.
784
785 </p></li><li><p>
786 While the second checkpoint from step 2 is starting up, another database
787 connection commits a transaction that resets the WAL file and writes new
788 content into the beginning of the WAL file.
789
790 </p></li><li><p>
791 Due to a data race, the second checkpoint from step 2 does not realize
792 that the WAL file has been reset by the transaction commit in step 3.
793 The second checkpoint leaves a field in the header of the WAL-Index set
794 incorrectly. That field indicates that part of the WAL file has already
795 been checkpointed, when in fact it has not been.
796
797 </p></li><li><p>
798 Additional transactions are committed to increase the number of pages
799 in the WAL file to be more than were present for the first checkpoint
800 from step 1.
801
802 </p></li><li><p>
803 Later when a third checkpoint occurs, the third checkpoint skips all
804 or part of the transaction that was written in step 3. Thus parts of
805 the transaction from step 3 never reach the database file, and the
806 database file goes corrupt.
807 </p></li></ol>
808
809 <h2 id="low_probability_of_occurrence"><span>11.2. </span>Low Probability Of Occurrence</h2>
810
811 <p> In order for this bug to happen, many details must align at just
812 the right moment. So much so that the SQLite developers were unable
813 to reproduce the bug organically in the lab. The only way the developers
814 have been able to cause the malfunction was to modify the SQLite sources
815 to invoke a callback controlled by <a href="c3ref/test_control.html">sqlite3_test_control()</a>
816 that enables a test program or script to trigger the write transaction
817 of step 3 at just the right moment during the second checkpoint.
818 Without that code hack, the problem has never been observed during
819 development and testing.
820
821 </p><p> This bug, though rare, does have serious consequences, and so
822 application developers should upgrade to a version of SQLite
823 that fixes the problem.
824 However, this is not an emergency.
825 Based on available telemetry, the occurrence rate of this problem in the
826 wild appears to be less than or equal to the expected occurrence rate of
827 SSD malfunctions and/or cosmic-ray hits. So even if you are running an
828 unpatched version of SQLite, and unless you are doing something really
829 unusual, you are unlikely to ever encounter this problem.
830 </p><p align="center"><small><i>This page was last updated on 2026-04-13 10:54:51Z </small></i></p>
831