Write-Ahead Logging
Small. Fast. Reliable.<br>Choose any three.
Home<br>Menu<br>About<br>Documentation<br>Download<br>License<br>Support<br>Purchase
Search
About<br>Documentation<br>Download<br>Support<br>Purchase
Search Documentation<br>Search Changelog
Write-Ahead Logging
Table Of Contents<br>1. Overview
2. How WAL Works
2.1. Checkpointing
2.2. Concurrency
2.3. Performance Considerations
3. Activating And Configuring WAL Mode
3.1. Automatic Checkpoint
3.2. Application-Initiated Checkpoints
3.3. Persistence of WAL mode
4. The WAL File
5. Read-Only Databases
6. Avoiding Excessively Large WAL Files
7. Implementation Of Shared-Memory For The WAL-Index
8. Use of WAL Without Shared-Memory
9. Sometimes Queries Return SQLITE_BUSY In WAL Mode
10. Backwards Compatibility
11. The WAL-Reset Bug
11.1. Bug Details
11.2. Low Probability Of Occurrence
1. Overview
The default method by which SQLite implements<br>atomic commit and rollback is a rollback journal.<br>Beginning with version 3.7.0 (2010-07-21), a new "Write-Ahead Log" option<br>(hereafter referred to as "WAL") is available.
There are advantages and disadvantages to using WAL instead of<br>a rollback journal. Advantages include:
WAL is significantly faster in most scenarios.
WAL provides more concurrency as readers do not block writers and<br>a writer does not block readers. Reading and writing can proceed<br>concurrently.
Disk I/O operations tends to be more sequential using WAL.
WAL uses many fewer fsync() operations and is thus less vulnerable to<br>problems on systems where the fsync() system call is broken.
But there are also disadvantages:
All processes using a database must be on the same host computer;<br>WAL does not work over a network filesystem. This is because WAL requires<br>all processes to share a small amount of memory and processes on<br>separate host machines obviously cannot share memory with each other.
Transactions that involve changes against multiple ATTACHed<br>databases are atomic for each individual database, but are not<br>atomic across all databases as a set.
It is not possible to change the page_size after entering WAL<br>mode, either on an empty database or by using VACUUM or by restoring<br>from a backup using the backup API. You must be in a rollback journal<br>mode to change the page size.
It is not possible to open read-only WAL databases.<br>The opening process must have write privileges for "-shm"<br>wal-index shared memory file associated with the database, if that<br>file exists, or else write access on the directory containing<br>the database file if the "-shm" file does not exist.<br>Beginning with version 3.22.0 (2018-01-22), a read-only<br>WAL-mode database file can be opened if<br>the -shm and -wal files<br>already exist or those files can be created or the<br>database is immutable.
WAL might be very slightly slower (perhaps 1% or 2% slower)<br>than the traditional rollback-journal approach<br>in applications that do mostly reads and seldom write.
There is an additional quasi-persistent "-wal" file and<br>"-shm" shared memory file associated with each<br>database, which can make SQLite less appealing for use as an<br>application file-format.
There is the extra operation of checkpointing which, though automatic<br>by default, is still something that application developers need to<br>be mindful of.
WAL works best with smaller transactions. WAL does<br>not work well for very large transactions. For transactions larger than<br>about 100 megabytes, traditional rollback journal modes will likely<br>be faster. For transactions in excess of a gigabyte, WAL mode may<br>fail with an I/O or disk-full error.<br>It is recommended that one of the rollback journal modes be used for<br>transactions larger than a few dozen megabytes.<br>Beginning with version 3.11.0 (2016-02-15),<br>WAL mode works as efficiently with<br>large transactions as does rollback mode.
2. How WAL Works
The traditional rollback journal works by writing a copy of the<br>original unchanged database content into a separate rollback journal file<br>and then writing changes directly into the database file. In the<br>event of a crash or ROLLBACK, the original content contained in the<br>rollback journal is played back into the database file to<br>revert the database file to its original state. The COMMIT occurs<br>when the rollback journal is deleted.
The WAL approach inverts this. The original content is preserved<br>in the database file and the changes are appended into a separate<br>WAL file. A COMMIT occurs when a special record indicating a commit<br>is appended to the WAL. Thus a COMMIT can happen without ever writing<br>to the original database, which allows readers to continue operating<br>from the original unaltered database while changes are simultaneously being<br>committed into the WAL. Multiple transactions can be appended to the<br>end of a single WAL file.
2.1. Checkpointing
Of course, one wants to eventually transfer all the transactions that<br>are appended in the WAL file back into the original database. Moving<br>the WAL file transactions back into the database is called a<br>"checkpoint".
Another way to...