1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Write-Ahead Logging</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Write-Ahead Logging
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">►</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
<div class="fancy-toc1"><a href="#how_wal_works">2. How WAL Works</a></div>
<div class="fancy-toc2"><a href="#checkpointing">2.1. Checkpointing</a></div>
<div class="fancy-toc2"><a href="#concurrency">2.2. Concurrency</a></div>
<div class="fancy-toc2"><a href="#performance_considerations">2.3. Performance Considerations</a></div>
<div class="fancy-toc1"><a href="#activating_and_configuring_wal_mode">3. Activating And Configuring WAL Mode</a></div>
<div class="fancy-toc2"><a href="#automatic_checkpoint">3.1. Automatic Checkpoint</a></div>
<div class="fancy-toc2"><a href="#application_initiated_checkpoints">3.2. Application-Initiated Checkpoints</a></div>
<div class="fancy-toc2"><a href="#persistence_of_wal_mode">3.3. Persistence of WAL mode</a></div>
<div class="fancy-toc1"><a href="#the_wal_file">4. The WAL File</a></div>
<div class="fancy-toc1"><a href="#read_only_databases">5. Read-Only Databases</a></div>
<div class="fancy-toc1"><a href="#avoiding_excessively_large_wal_files">6. Avoiding Excessively Large WAL Files</a></div>
<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>
<div class="fancy-toc1"><a href="#use_of_wal_without_shared_memory">8. Use of WAL Without Shared-Memory</a></div>
<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>
<div class="fancy-toc1"><a href="#backwards_compatibility">10. Backwards Compatibility</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "▼";
} else {
sub.style.display = "none";
mk.innerHTML = "►";
}
}
</script>
</div>
<h1 id="overview"><span>1. </span>Overview</h1>
<p>The default method by which SQLite implements
<a href="atomiccommit.html">atomic commit and rollback</a> is a <a href="lockingv3.html#rollback">rollback journal</a>.
Beginning with <a href="releaselog/3_7_0.html">version 3.7.0</a> (2010-07-21), a new "Write-Ahead Log" option
(hereafter referred to as "WAL") is available.</p>
<p>There are advantages and disadvantages to using WAL instead of
a rollback journal. Advantages include:</p>
<a name="advantages"></a>
<ol>
<li>WAL is significantly faster in most scenarios.
</li><li>WAL provides more concurrency as readers do not block writers and
a writer does not block readers. Reading and writing can proceed
concurrently.
</li><li>Disk I/O operations tends to be more sequential using WAL.
</li><li>WAL uses many fewer fsync() operations and is thus less vulnerable to
problems on systems where the fsync() system call is broken.
</li></ol>
<p>But there are also disadvantages:</p>
<ol>
<li>WAL normally requires that the <a href="vfs.html">VFS</a>
support shared-memory primitives.
(Exception: <a href="wal.html#noshm">WAL without shared memory</a>)
The built-in unix and windows VFSes
support this but third-party extension VFSes for custom operating
systems might not.
</li><li>All processes using a database must be on the same host computer;
WAL does not work over a network filesystem.
</li><li>Transactions that involve changes against multiple <a href="lang_attach.html">ATTACHed</a>
databases are atomic for each individual database, but are not
atomic across all databases as a set.
</li><li>It is not possible to change the <a href="pragma.html#pragma_page_size">page_size</a> after entering WAL
mode, either on an empty database or by using <a href="lang_vacuum.html">VACUUM</a> or by restoring
from a backup using the <a href="backup.html">backup API</a>. You must be in a rollback journal
mode to change the page size.
</li><li><s>It is not possible to open <a href="wal.html#readonly">read-only WAL databases</a>.
The opening process must have write privileges for "<tt>-shm</tt>"
<a href="walformat.html#shm">wal-index</a> shared memory file associated with the database, if that
file exists, or else write access on the directory containing
the database file if the "<tt>-shm</tt>" file does not exist.</s>
Beginning with <a href="releaselog/3_22_0.html">version 3.22.0</a> (2018-01-22), a read-only
WAL-mode database file can be opened if
the <tt>-shm</tt> and <tt>-wal</tt> files
already exists or those files can be created or the
<a href="uri.html#uriimmutable">database is immutable</a>.
</li><li>WAL might be very slightly slower (perhaps 1% or 2% slower)
than the traditional rollback-journal approach
in applications that do mostly reads and seldom write.
</li><li>There is an additional quasi-persistent "<tt>-wal</tt>" file and
"<tt>-shm</tt>" shared memory file associated with each
database, which can make SQLite less appealing for use as an
<a href="appfileformat.html">application file-format</a>.
</li><li>There is the extra operation of <a href="wal.html#ckpt">checkpointing</a> which, though automatic
by default, is still something that application developers need to
be mindful of.
</li><li><s>WAL works best with smaller transactions. WAL does
not work well for very large transactions. For transactions larger than
about 100 megabytes, traditional rollback journal modes will likely
be faster. For transactions in excess of a gigabyte, WAL mode may
fail with an I/O or disk-full error.
It is recommended that one of the rollback journal modes be used for
transactions larger than a few dozen megabytes.</s>
Beginning with <a href="releaselog/3_11_0.html">version 3.11.0</a> (2016-02-15),
WAL mode works as efficiently with
large transactions as does rollback mode.
</li></ol>
<h1 id="how_wal_works"><span>2. </span>How WAL Works</h1>
<p>The traditional rollback journal works by writing a copy of the
original unchanged database content into a separate rollback journal file
and then writing changes directly into the database file. In the
event of a crash or <a href="lang_transaction.html">ROLLBACK</a>, the original content contained in the
rollback journal is played back into the database file to
revert the database file to its original state. The <a href="lang_transaction.html">COMMIT</a> occurs
when the rollback journal is deleted.</p>
<p>The WAL approach inverts this. The original content is preserved
in the database file and the changes are appended into a separate
WAL file. A <a href="lang_transaction.html">COMMIT</a> occurs when a special record indicating a commit
is appended to the WAL. Thus a COMMIT can happen without ever writing
to the original database, which allows readers to continue operating
from the original unaltered database while changes are simultaneously being
committed into the WAL. Multiple transactions can be appended to the
end of a single WAL file.</p>
<a name="ckpt"></a>
<h2 id="checkpointing"><span>2.1. </span>Checkpointing</h2>
<p>Of course, one wants to eventually transfer all the transactions that
are appended in the WAL file back into the original database. Moving
the WAL file transactions back into the database is called a
"<i>checkpoint</i>".</p><p>
</p><p>Another way to think about the difference between rollback and
write-ahead log is that in the rollback-journal
approach, there are two primitive operations, reading and writing,
whereas with a write-ahead log
there are now three primitive operations: reading, writing, and
checkpointing.</p>
<p>By default, SQLite does a checkpoint automatically when the WAL file
reaches a threshold size of 1000 pages. (The
<a href="compile.html#default_wal_autocheckpoint">SQLITE_DEFAULT_WAL_AUTOCHECKPOINT</a> compile-time option can be used to
specify a different default.) Applications using WAL do
not have to do anything in order to for these checkpoints to occur.
But if they want to, applications can adjust the automatic checkpoint
threshold. Or they can turn off the automatic checkpoints and run
checkpoints during idle moments or in a separate thread or process.</p>
<a name="concurrency"></a>
<h2 id="concurrency"><span>2.2. </span>Concurrency</h2>
<p>When a read operation begins on a WAL-mode database, it first
remembers the location of the last valid commit record in the WAL.
Call this point the "end mark". Because the WAL can be growing and
adding new commit records while various readers connect to the database,
each reader can potentially have its own end mark. But for any
particular reader, the end mark is unchanged for the duration of the
transaction, thus ensuring that a single read transaction only sees
the database content as it existed at a single point in time.</p>
<p>When a reader needs a page of content, it first checks the WAL to
see if that page appears there, and if so it pulls in the last copy
of the page that occurs in the WAL prior to the reader's end mark.
If no copy of the page exists in the WAL prior to the reader's end mark,
then the page is read from the original database file. Readers can
exist in separate processes, so to avoid forcing every reader to scan
the entire WAL looking for pages (the WAL file can grow to
multiple megabytes, depending on how often checkpoints are run), a
data structure called the "wal-index" is maintained in shared memory
which helps readers locate pages in the WAL quickly and with a minimum
of I/O. The wal-index greatly improves the performance of readers,
but the use of shared memory means that all readers must exist on the
same machine. This is why the write-ahead log implementation will not
work on a network filesystem.</p>
<p>Writers merely append new content to the end of the WAL file.
Because writers do nothing that would interfere with the actions of
readers, writers and readers can run at the same time. However,
since there is only one WAL file, there can only be one writer at
a time.</p>
<p>A checkpoint operation takes content from the WAL file
and transfers it back into the original database file.
A checkpoint can run concurrently with readers, however the checkpoint
must stop when it reaches a page in the WAL that is past the end mark
of any current reader. The checkpoint has to stop at that point because
otherwise it might overwrite part of the database file that the reader
is actively using. The checkpoint remembers (in the wal-index) how far
it got and will resume transferring content from the WAL to the database
from where it left off on the next invocation.</p>
<p>Thus a long-running read transaction can prevent a checkpointer from
making progress. But presumably every read transaction will eventually
end and the checkpointer will be able to continue.</p>
<p>Whenever a write operation occurs, the writer checks how much progress
the checkpointer has made, and if the entire WAL has been transferred into
the database and synced and if no readers are making use of the WAL, then
the writer will rewind the WAL back to the beginning and start putting new
transactions at the beginning of the WAL. This mechanism prevents a WAL
file from growing without bound.</p>
<a name="fast"></a>
<h2 id="performance_considerations"><span>2.3. </span>Performance Considerations</h2>
<p>Write transactions are very fast since they only involve writing
the content once (versus twice for rollback-journal transactions)
and because the writes are all sequential. Further, syncing the
content to the disk is not required, as long as the application is
willing to sacrifice durability following a power loss or hard reboot.
(Writers sync the WAL on every transaction commit if
<a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> is set to FULL but omit this sync if
<a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> is set to NORMAL.)</p>
<p>On the other hand, read performance deteriorates as the WAL file
grows in size since each reader must check the WAL file for the content
and the time needed to check the WAL file is proportional
to the size of the WAL file. The wal-index helps find content
in the WAL file much faster, but performance still falls off with
increasing WAL file size. Hence, to maintain good read performance
it is important to keep the WAL file size down by
running checkpoints at regular intervals.</p>
<p>Checkpointing does require sync operations in order to avoid
the possibility of database corruption following a power loss
or hard reboot. The WAL must be synced to persistent storage
prior to moving content from the WAL into the database and the
database file must by synced prior to resetting the WAL.
Checkpoint also requires more seeking.
The checkpointer makes an effort to
do as many sequential page writes to the database as it can (the pages
are transferred from WAL to database in ascending order) but even
then there will typically be many seek operations interspersed among
the page writes. These factors combine to make checkpoints slower than
write transactions.</p>
<p>The default strategy is to allow successive write transactions to
grow the WAL until the WAL becomes about 1000 pages in size, then to
run a checkpoint operation for each subsequent COMMIT until the WAL
is reset to be smaller than 1000 pages. By default, the checkpoint will be
run automatically by the same thread that does the COMMIT that pushes
the WAL over its size limit. This has the effect of causing most
COMMIT operations to be very fast but an occasional COMMIT (those that trigger
a checkpoint) to be much slower. If that effect is undesirable, then
the application can disable automatic checkpointing and run the
periodic checkpoints in a separate thread, or separate process.
(Links to commands and interfaces to accomplish this are
<a href="#how_to_checkpoint">shown below</a>.)</p>
<p>Note that with <a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> set to NORMAL, the checkpoint
is the only operation to issue an I/O barrier or sync operation
(fsync() on unix or FlushFileBuffers() on windows). If an application
therefore runs checkpoint in a separate thread or process, the main
thread or process that is doing database queries and updates will never
block on a sync operation. This helps to prevent "latch-up" in applications
running on a busy disk drive. The downside to
this configuration is that transactions are no longer durable and
might rollback following a power failure or hard reset.</p>
<p>Notice too that there is a tradeoff between average read performance
and average write performance. To maximize the read performance,
one wants to keep the WAL as small as possible and hence run checkpoints
frequently, perhaps as often as every COMMIT. To maximize
write performance, one wants to amortize the cost of each checkpoint
over as many writes as possible, meaning that one wants to run checkpoints
infrequently and let the WAL grow as large as possible before each
checkpoint. The decision of how often to run checkpoints may therefore
vary from one application to another depending on the relative read
and write performance requirements of the application.
The default strategy is to run a checkpoint once the WAL
reaches 1000 pages and this strategy seems to work well in test applications on
workstations, but other strategies might work better on different
platforms or for different workloads.</p>
<h1 id="activating_and_configuring_wal_mode"><span>3. </span>Activating And Configuring WAL Mode</h1>
<p>An SQLite database connection defaults to
<a href="pragma.html#pragma_journal_mode">journal_mode=DELETE</a>. To convert to WAL mode, use the
following pragma:</p>
<blockquote><pre>
PRAGMA journal_mode=WAL;
</pre></blockquote>
<p>The journal_mode pragma returns a string which is the new journal mode.
On success, the pragma will return the string "<tt>wal</tt>". If
the conversion to WAL could not be completed (for example, if the <a href="vfs.html">VFS</a>
does not support the necessary shared-memory primitives) then the
journaling mode will be unchanged and the string returned from the
primitive will be the prior journaling mode (for example "<tt>delete</tt>").
<a name="how_to_checkpoint"></a>
</p><h2 id="automatic_checkpoint"><span>3.1. </span>Automatic Checkpoint</h2>
<p>By default, SQLite will automatically checkpoint whenever a <a href="lang_transaction.html">COMMIT</a>
occurs that causes the WAL file to be 1000 pages or more in size, or when the
last database connection on a database file closes. The default
configuration is intended to work well for most applications.
But programs that want more control can force a checkpoint
using the <a href="pragma.html#pragma_wal_checkpoint">wal_checkpoint pragma</a> or by calling the
<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> C interface. The automatic checkpoint
threshold can be changed or automatic checkpointing can be completely
disabled using the <a href="pragma.html#pragma_wal_autocheckpoint">wal_autocheckpoint pragma</a> or by calling the
<a href="c3ref/wal_autocheckpoint.html">sqlite3_wal_autocheckpoint()</a> C interface. A program can also
use <a href="c3ref/wal_hook.html">sqlite3_wal_hook()</a> to register a callback to be invoked whenever
any transaction commits to the WAL. This callback can then invoke
<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
criteria it thinks is appropriate. (The automatic checkpoint mechanism
is implemented as a simple wrapper around <a href="c3ref/wal_hook.html">sqlite3_wal_hook()</a>.)</p>
<h2 id="application_initiated_checkpoints"><span>3.2. </span>Application-Initiated Checkpoints</h2>
<p>An application can initiate a checkpoint using any writable database
connection on the database simply by invoking
<a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> or <a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a>.
There are three subtypes of checkpoints that vary in their aggressiveness:
PASSIVE, FULL, and RESTART. The default checkpoint style is PASSIVE, which
does as much work as it can without interfering with other database
connections, and which might not run to completion if there are
concurrent readers or writers.
All checkpoints initiated by <a href="c3ref/wal_checkpoint.html">sqlite3_wal_checkpoint()</a> and
by the automatic checkpoint mechanism are PASSIVE. FULL and RESTART
checkpoints try harder to run the checkpoint to completion and can only
be initiated by a call to <a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a>. See the
<a href="c3ref/wal_checkpoint_v2.html">sqlite3_wal_checkpoint_v2()</a> documentation for additional information
on FULL and RESET checkpoints.
</p><h2 id="persistence_of_wal_mode"><span>3.3. </span>Persistence of WAL mode</h2>
<p>Unlike the other journaling modes,
<a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=WAL</a> is
persistent. If a process sets WAL mode, then closes and reopens the
database, the database will come back in WAL mode. In contrast, if
a process sets (for example) PRAGMA journal_mode=TRUNCATE and then closes and
reopens the database will come back up in the default rollback mode of
DELETE rather than the previous TRUNCATE setting.</p>
<p>The persistence of WAL mode means that applications can be converted
to using SQLite in WAL mode without making any changes to the application
itself. One has merely to run "<tt>PRAGMA journal_mode=WAL;</tt>" on the
database file(s) using the <a href="cli.html">command-line shell</a> or other utility, then
restart the application.</p>
<p>The WAL journal mode will be set on all
connections to the same database file if it is set on any one connection.
</p>
<a name="walfile"></a>
<h1 id="the_wal_file"><span>4. </span>The WAL File</h1>
<p>While a <a href="c3ref/sqlite3.html">database connection</a> is open on a WAL-mode database, SQLite
maintains an extra journal file called a "Write Ahead Log" or "WAL File".
The name of this file on disk is usually the name of the database file
with an extra "<tt>-wal</tt>" suffix, though different naming rules may
apply if SQLite is compiled with <a href="compile.html#enable_8_3_names">SQLITE_ENABLE_8_3_NAMES</a>.
</p><p>The WAL file exists for as long as any <a href="c3ref/sqlite3.html">database connection</a> has the
database open. Usually, the WAL file is deleted automatically when the
last connection to the database closes. However, if the last process to
have the database open exits without cleanly
shutting down the database connection, or if the
<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
might be retained on disk after all connections to the database have
been closed. The WAL file is part of the persistent state of the
database and should be kept with the database if the database is copied
or moved. If a database file is separated from its WAL file, then
transactions that were previously committed to the database might be lost,
or the database file might become corrupted.
The only safe way to remove a WAL file is
to open the database file using one of the <a href="c3ref/open.html">sqlite3_open()</a> interfaces
then immediately close the database using <a href="c3ref/close.html">sqlite3_close()</a>.
</p><p>The <a href="fileformat2.html#walformat">WAL file format</a> is precisely defined and is cross-platform.
<a name="readonly"></a>
</p><h1 id="read_only_databases"><span>5. </span>Read-Only Databases</h1>
<p>Older versions of SQLite could not read a WAL-mode database that was
read-only. In other words, write access was required in order to read a
WAL-mode database. This constraint was relaxed beginning with
SQLite <a href="releaselog/3_22_0.html">version 3.22.0</a> (2018-01-22).
</p><p>On newer versions of SQLite,
a WAL-mode database on read-only media, or a WAL-mode database that lacks
write permission, can still be read as long as one or more of the following
conditions are met:
</p><ol>
<li>The <tt>-shm</tt> and <tt>-wal</tt> files already exists and are readable
</li><li>There is write permission on the directory containing the database so
that the <tt>-shm</tt> and <tt>-wal</tt> files can be created.
</li><li>The database connection is opened using the
<a href="uri.html#uriimmutable">immutable query parameter</a>.
</li></ol>
<p>Even though it is possible to open a read-only WAL-mode database,
it is good practice to converted to
<a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=DELETE</a> prior to burning an
SQLite database image onto read-only media.</p>
<a name="bigwal"></a>
<h1 id="avoiding_excessively_large_wal_files"><span>6. </span>Avoiding Excessively Large WAL Files</h1>
<p>In normal cases, new content is appended to the WAL file until the
WAL file accumulates about 1000 pages (and is thus about 4MB
in size) at which point a checkpoint is automatically run and the WAL file
is recycled. The checkpoint does not normally truncate the WAL file
(unless the <a href="pragma.html#pragma_journal_size_limit">journal_size_limit pragma</a> is set). Instead, it merely
causes SQLite to start overwriting the WAL file from the beginning.
This is done because it is normally faster to overwrite an existing file
than to append. When the last connection to a database closes, that
connection does one last checkpoint and then deletes the WAL and its
associated shared-memory file, to clean up the disk.
</p><p>So in the vast majority of cases, applications need not worry about
the WAL file at all. SQLite will automatically take care of it. But
it is possible to get SQLite into a state where the WAL file will grow
without bound, causing excess disk space usage and slow queries speeds.
The following bullets enumerate some of the ways that this can happen
and how to avoid them.
</p><ul>
<li><p>
<b>Disabling the automatic checkpoint mechanism.</b>
In its default configuration, SQLite will checkpoint the WAL file at the
conclusion of any transaction when the WAL file is more than 1000 pages
long. However, compile-time and run-time options exist that can disable
or defer this automatic checkpoint. If an application disables the
automatic checkpoint, then there is nothing to prevent the WAL file
from growing excessively.
</p></li><li><p>
<b>Checkpoint starvation.</b>
A checkpoint is only able to run to completion, and reset the WAL file,
if there are no other database connections using the WAL file. If another
connection has a read transaction open,
then the checkpoint cannot reset the WAL file because
doing so might delete content out from under the reader.
The checkpoint will do as much work as it can without upsetting the
reader, but it cannot run to completion.
The checkpoint will start up again where it left off after the next
write transaction. This repeats until some checkpoint is able to complete.
</p><p>However, if a database has many concurrent overlapping readers
and there is always at least one active reader, then
no checkpoints will be able to complete
and hence the WAL file will grow without bound.
</p><p>This scenario can be avoided by ensuring that there are "reader gaps":
times when no processes are reading from the
database and that checkpoints are attempted during those times.
In applications with many concurrent readers, one might also consider
running manual checkpoints with the <a href="c3ref/c_checkpoint_full.html">SQLITE_CHECKPOINT_RESTART</a> or
<a href="c3ref/c_checkpoint_full.html">SQLITE_CHECKPOINT_TRUNCATE</a> option which will ensure that the checkpoint
runs to completion before returning. The disadvantage of using
<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
readers might block while the checkpoint is running.
</p></li><li><p>
<b>Very large write transactions.</b>
A checkpoint can only complete when no other transactions are running,
which means the WAL file cannot be reset in the middle of a write
transaction. So a large change to a large database
might result in a large WAL file. The WAL file will be checkpointed
once the write transaction completes (assuming there are no other readers
blocking it) but in the meantime, the file can grow very big.
</p><p>As of SQLite <a href="releaselog/3_11_0.html">version 3.11.0</a> (2016-02-15),
the WAL file for a single transaction
should be proportional in size to the transaction itself. Pages that
are changed by the transaction should only be written into the WAL file
once. However, with older versions of SQLite, the same page might be
written into the WAL file multiple times if the transaction grows larger
than the page cache.
</p></li></ul>
<h1 id="implementation_of_shared_memory_for_the_wal_index"><span>7. </span>Implementation Of Shared-Memory For The WAL-Index</h1>
<p>The <a href="walformat.html#shm">wal-index</a> is implemented using an ordinary file that is
mmapped for robustness. Early (pre-release) implementations of WAL mode
stored the wal-index in volatile shared-memory, such as files created in
/dev/shm on Linux or /tmp on other unix systems. The problem
with that approach is that processes with a different root directory
(changed via <a href="http://en.wikipedia.org/wiki/Chroot">chroot</a>)
will see different files and hence use different shared memory areas,
leading to database corruption. Other methods for creating nameless
shared memory blocks are not portable across the various flavors of
unix. And we could not find any method to create nameless shared
memory blocks on windows. The only way we have found to guarantee
that all processes accessing the same database file use the same shared
memory is to create the shared memory by mmapping a file in the same
directory as the database itself.</p>
<p>Using an ordinary disk file to provide shared memory has the
disadvantage that it might actually do unnecessary disk I/O by
writing the shared memory to disk. However, the developers do not
think this is a major concern since the wal-index rarely exceeds
32 KiB in size and is never synced. Furthermore, the wal-index
backing file is deleted when the last database connection disconnects,
which often prevents any real disk I/O from ever happening.</p>
<p>Specialized applications for which the default implementation of
shared memory is unacceptable can devise alternative methods via a
custom <a href="vfs.html">VFS</a>.
For example, if it is known that a particular database
will only be accessed by threads within a single process, the wal-index
can be implemented using heap memory instead of true shared memory.</p>
<a name="noshm"></a>
<h1 id="use_of_wal_without_shared_memory"><span>8. </span>Use of WAL Without Shared-Memory</h1>
<p>Beginning in SQLite <a href="releaselog/3_7_4.html">version 3.7.4</a> (2010-12-07),
WAL databases can be created, read, and
written even if shared memory is unavailable as long as the
<a href="pragma.html#pragma_locking_mode">locking_mode</a> is set to EXCLUSIVE before the first attempted access.
In other words, a process can interact with
a WAL database without using shared memory if that
process is guaranteed to be the only process accessing the database.
This feature allows WAL databases to be created, read, and written
by legacy <a href="vfs.html">VFSes</a> that lack the "version 2" shared-memory
methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on the
<a href="c3ref/io_methods.html">sqlite3_io_methods</a> object.</p>
<p>If <a href="pragma.html#pragma_locking_mode">EXCLUSIVE locking mode</a>
is set prior to the first WAL-mode
database access, then SQLite never attempts to call any of the
shared-memory methods and hence no shared-memory
wal-index is ever created.
In that case, the database connection remains in EXCLUSIVE mode
as long as the journal mode is WAL; attempts to change the locking
mode using "<tt>PRAGMA locking_mode=NORMAL;</tt>" are no-ops.
The only way to change out of EXCLUSIVE locking mode is to first
change out of WAL journal mode.</p>
<p>If NORMAL locking mode is in effect for the first WAL-mode database
access, then the shared-memory wal-index is created. This means that the
underlying VFS must support the "version 2" shared-memory.
If the VFS does not support shared-memory methods, then the attempt to
open a database that is already in WAL mode, or the attempt convert a
database into WAL mode, will fail.
As long as exactly one connection is using a shared-memory wal-index,
the locking mode can be changed freely between NORMAL and EXCLUSIVE.
It is only when the shared-memory wal-index is omitted, when the locking
mode is EXCLUSIVE prior to the first WAL-mode database access, that the
locking mode is stuck in EXCLUSIVE.</p>
<a name="busy"></a>
<h1 id="sometimes_queries_return_sqlite_busy_in_wal_mode"><span>9. </span>Sometimes Queries Return SQLITE_BUSY In WAL Mode</h1>
<p>The <a href="wal.html#advantages">second advantage of WAL-mode</a> is that
writers do not block readers and readers to do not block writers.
This is <u>mostly</u> true.
But there are some obscure cases where a query against a WAL-mode
database can return <a href="rescode.html#busy">SQLITE_BUSY</a>, so applications should be prepared
for that happenstance.
</p><p>Cases where a query against a WAL-mode database can return <a href="rescode.html#busy">SQLITE_BUSY</a>
include the following:
</p><ul>
<li><p>If another database connection has the database mode open
in <a href="pragma.html#pragma_locking_mode">exclusive locking mode</a> then all queries against the
database will return <a href="rescode.html#busy">SQLITE_BUSY</a>. Both Chrome and Firefox open their
database files in exclusive locking mode, so attempts to read Chrome or
Firefox databases while the applications are running will run into this
problem, for example.
</p></li><li><p>
When the last connection to a particular database is closing, that
connection will acquire an exclusive lock for a short time while it
cleans up the WAL and shared-memory files. If a second database tries
to open and query the database while the first connection
is still in the middle
of its cleanup process, the second connection might get an <a href="rescode.html#busy">SQLITE_BUSY</a>
error.
</p></li><li><p>
If the last connection to a database crashed, then the first new
connection to open the database will start a recovery process. An
exclusive lock is held during recovery. So if a third database connection
tries to jump in and query while the second connection is running recovery,
the third connection will get an <a href="rescode.html#busy">SQLITE_BUSY</a> error.
</p></li></ul>
<a name="bkwrds"></a>
<h1 id="backwards_compatibility"><span>10. </span>Backwards Compatibility</h1>
<p>The database file format is unchanged for WAL mode. However, the
WAL file and the <a href="walformat.html#shm">wal-index</a> are new concepts and so older versions of
SQLite will not know
how to recover a crashed SQLite database that was operating in WAL mode
when the crash occurred.
To prevent older versions of SQLite (prior to version 3.7.0, 2010-07-22)
from trying to recover
a WAL-mode database (and making matters worse) the database file format
version numbers (bytes 18 and 19 in the <a href="fileformat2.html#database_header">database header</a>)
are increased from 1 to 2 in WAL mode.
Thus, if an older version of SQLite attempts to connect to an SQLite
database that is operating in WAL mode, it will report an error along
the lines of "file is encrypted or is not a database".</p>
<p>One can explicitly change out of WAL mode using a pragma such as
this:</p>
<blockquote><pre>
PRAGMA journal_mode=DELETE;
</pre></blockquote>
<p>Deliberately changing out of WAL mode changes the database file format
version numbers back to 1 so that older versions of SQLite can once again
access the database file.</p>
<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/wal.in?m=9b1dbf50c3fb0831f">2018-11-26 12:01:01</a> UTC </small></i></p>
|
