WAL Mode: One Writer, Many Readers
SQLite's default journal blocks readers during a write. WAL mode does not, and that single difference decides whether SQLite suits a concurrent service.
2 min readSQLite 3.47
Whether reads survive alongside writes in SQLite comes down to which journaling
mode the file is in. It is not a code change: journal_mode is a property of the
database file, and it is set once.
Why does a SQLite write block all readers?
In the default rollback journal mode, a write transaction copies the original content of the pages it is about to change into a separate journal file, then modifies the database. If the transaction fails, the journal restores the original.
To keep readers from seeing a half-applied change, the writer eventually takes an exclusive lock on the whole file. Until then it holds a reserved lock and readers continue; from the moment the write spills out of memory until the transaction commits, no other process can touch the database. On a service where reads outnumber writes by a wide margin, a write that spills stalls everything.
What WAL changes
In write-ahead logging mode, the writer appends new page images to a separate WAL file rather than modifying the database in place. Readers continue reading the database file, plus whatever part of the WAL was committed at the moment they started.
PRAGMA journal_mode = WAL;Readers no longer block on the writer, and the writer no longer blocks on readers. Each reader sees a consistent snapshot from the point its transaction began.
This is a persistent change to the database file. It only needs to be run once, not on every connection, and it survives the process that set it.
What WAL does not change
There is still one writer. WAL removes reader/writer contention; it does not introduce concurrent writes. A second write transaction waits for the first. If the workload’s bottleneck is write concurrency, WAL does not address it.
Checkpointing still touches the main file. Periodically the WAL contents are written back into the database. By default this happens automatically when the WAL exceeds roughly a thousand pages, on whichever connection commits the transaction that crosses the threshold. That connection pays the cost, which shows up as an occasional slow write among fast ones.
It needs shared memory. WAL uses a shared-memory index file, which means all connections must be on the same machine. On a network filesystem WAL either fails or behaves unsafely. This rules out several deployment shapes that otherwise look fine.
Which pragmas should be set with WAL mode?
PRAGMA synchronous interacts directly with WAL. Under FULL, SQLite syncs the
WAL on every commit. Under NORMAL, it syncs only at checkpoints, which is faster
and, in WAL mode, cannot corrupt the database — a power loss can lose recent
committed transactions, but the file stays consistent. That is a genuine durability
trade, and it should be made explicitly rather than copied.
Writer serialization is still there, so a second writer waits rather than failing
immediately — which is what PRAGMA busy_timeout governs.
Limits
Everything above describes concurrency between processes and connections on one machine, with the database on local storage. It says nothing about whether SQLite suits a given workload in terms of dataset size, write rate, or operational requirements such as replication and point-in-time recovery, which are separate questions with different answers.
Frequently asked questions
- What does WAL mode change in SQLite?
- In write-ahead logging mode the writer appends new page images to a separate WAL file instead of modifying the database in place. Readers continue reading the database file plus whatever part of the WAL was committed at the moment they started, so readers no longer block on the writer and the writer no longer blocks on readers. Each reader sees a consistent snapshot from the point its transaction began.
- Does WAL mode allow multiple writers in SQLite?
- No. WAL removes reader/writer contention, but there is still exactly one writer at a time and a second write transaction waits for the first. If the bottleneck in a workload is write concurrency rather than readers blocked behind a writer, WAL does not address it.
- Does SQLite WAL mode have to be enabled on every connection?
- No. Setting journal_mode to WAL is a persistent change to the database file rather than a connection setting, so it is run once, survives the process that set it, and still applies when the file is reopened. What WAL does add is a shared-memory index file, which means every connection must be on the same machine; on a network filesystem it either fails or behaves unsafely.
- Is PRAGMA synchronous = NORMAL safe in SQLite WAL mode?
- Under NORMAL, SQLite syncs only at checkpoints rather than on every commit, which is faster and, in WAL mode, cannot corrupt the database. A power loss can lose recent committed transactions while leaving the file consistent. That is a genuine durability trade, and it should be made explicitly rather than copied from another configuration.
References
- docsSQLite documentation — PRAGMA synchronous (opens in a new tab)
WAL mode is safe from corruption with synchronous=NORMAL, but loses durability.
-- written by
Elias RoweDatabase engineer
Elias Rowe writes about database engineering, SQL performance, and production systems. He focuses on measurable behavior, practical trade-offs, and conclusions that can be reproduced rather than assumed.
