SQLite WAL (Write-Ahead Logging) Mode: Read/Write Concurrency and Checkpointing
See how SQLite WAL changes reader and writer behavior and how checkpoints copy WAL content into the database file. WAL still allows only one writer at a time, and older read snapshots and locking can limit checkpoint progress.
Publication & corrections: infostoa · Editorial standards
Individual author and specialist reviewer information is not provided for this article.
In SQLite rollback-journal mode, a write records prior page contents in a journal before updating the database file. WAL records changes in a separate log and changes how readers obtain a snapshot. WAL still permits only one writer at a time and has locking and checkpoint constraints, so it is inaccurate to claim that readers and writers can never block one another.
2. Write-Ahead Logging (WAL) Architecture and Shared-Memory Indexing (-shm)
In WAL mode, changed pages are appended to a separate WAL file, while a read transaction uses a snapshot to read database pages together with relevant WAL frames. This changes reader-writer interaction compared with rollback journaling, but it does not allow multiple simultaneous writers. SQLite documents situations in which SQLITE_BUSY can still occur in WAL mode, so WAL should not be described as guaranteeing nonblocking concurrency or a performance improvement in every workload.
The WAL index is held in shared memory, which is one reason SQLite requires processes using a WAL database to be on the same host. Placing the database and WAL on a network filesystem for access by processes on multiple hosts is outside the supported WAL model. Check file placement and process boundaries before deployment; if several servers must access the data concurrently, evaluate a client/server database architecture instead.
3. Checkpoint progress and WAL size
A checkpoint copies valid WAL frames into the database file. By default, SQLite starts an automatic checkpoint when a commit leaves the WAL at about 1,000 pages; that is a trigger, not a size cap or a promise that the file will shrink. Each read transaction remembers the location of the last valid WAL commit at its start, called its end mark. A checkpoint stops when it reaches a WAL page past the end mark of a current reader. Do not reverse this into “frames before the end mark cannot be overwritten.” A later invocation can resume from the recorded progress. If overlapping readers leave no opportunity for completion, the WAL can keep growing.
The default automatic checkpoint is PASSIVE: it does not wait for other connections’ readers or writers and may leave work unfinished. Read the sqlite3_wal_checkpoint_v2() modes separately. FULL waits until existing readers use the latest snapshot and blocks new writer attempts, while new readers may continue. RESTART adds a wait until existing readers no longer use the WAL, so the next writer can restart it. TRUNCATE behaves like RESTART and truncates the WAL file to zero bytes before a successful return. In this API description, FULL and RESTART do not mean that readers are blocked. The broader warning in WAL overview §6 says readers might block during RESTART/TRUNCATE; verify the API, version, and call path rather than extending that warning to every mode. Do not compare WAL size with the threshold alone: observe transaction duration and log/checkpoint frame counts over the same time window.
4. A replication tool can change checkpoint ownership
In the Litestream setup described by Fly.io, the replicator keeps a read transaction open, preventing SQLite’s automatic checkpoint from advancing; it captures and replicates WAL updates, then triggers checkpointing itself. This is that tool’s implementation, not a general property of replication software. When diagnosing a stalled checkpoint or growing WAL, check how the selected replicator coordinates read transactions and checkpoints, and verify its lag and restore procedure for the deployed version. The example is not a guarantee of lossless or real-time replication.
A deployment check for concurrent writes and checkpoints
SQLite WAL §11, checked 2026-10-12, documents a very rare WAL-reset race that can corrupt a database. Its described conditions include two or more connections to the same WAL database in separate threads or processes, with writes and checkpointing overlapping at a narrow moment. The SQLite page says it is fixed in 3.51.3 and later, with backports in 3.44.6 and 3.50.7 for some earlier release lines. Check the SQLite version actually linked into the deployed binary, then determine whether the described multi-connection write/checkpoint pattern applies. This does not imply equal urgency or likelihood for every WAL deployment.