| 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 |
|