--- title: "WAL Mode: One Writer, Many Readers" description: "SQLite's default journal blocks readers during a write. WAL mode does not, and that single difference decides whether SQLite suits a concurrent service." url: https://sqlpress.com/blog/sqlite-wal-mode/ category: SQLite tags: ["reliability", "concurrency", "wal", "architecture"] author: "Elias Rowe" published: 2026-10-18T00:00:00.000Z measured: false --- # WAL Mode: One Writer, Many Readers ## Key takeaways - In the default rollback journal mode a writer excludes all readers from the point its changes reach the file until it commits; in WAL mode readers proceed against the last committed snapshot. - WAL does not add concurrent writers — there is still exactly one at a time — it removes the reader/writer conflict. - The mode is a persistent property of the database file, not a connection setting, so it is set once and survives reopening. ## Test environment - System: SQLite 3.47 - Workload: Illustrative (examples verified, nothing timed) - Notes: journal_mode is a persistent property of the database file; WAL additionally requires shared memory, so all connections must be on the same machine. 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. ```sql title="enable-wal.sql" 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. ## FAQ ### 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. ## Sources - [SQLite documentation — Write-Ahead Logging](https://sqlite.org/wal.html) (docs) - [SQLite documentation — File Locking And Concurrency](https://sqlite.org/lockingv3.html) (docs) - [SQLite documentation — PRAGMA journal_mode](https://sqlite.org/pragma.html#pragma_journal_mode) (docs) - [SQLite documentation — PRAGMA synchronous](https://sqlite.org/pragma.html#pragma_synchronous) (docs) — WAL mode is safe from corruption with synchronous=NORMAL, but loses durability.