In SQLite, what does switching a database to WAL mode change about readers and writers running at the same time?
answer
- readers versus the writer
- changes appended, originals untouched
- each reader keeps an end mark
- still one writer per file
basics
~20 sIn WAL mode SQLite readers no longer block the writer and the writer no longer blocks readers: each reader sees a fixed snapshot while one writer appends to the log. Writers still take turns, one at a time.
solid answer
~40 sBy default SQLite uses a rollback journal (`journal_mode=DELETE`), and a writer must shut every reader out of the file while it writes its changes into the database file. `PRAGMA journal_mode=WAL` inverts that: changes are appended to a separate write-ahead log and the original pages stay untouched, so readers keep reading while a writer commits. Each read transaction remembers the last commit in the log when it starts and sees that snapshot until it ends. What does not change is the single-writer rule: there is one log, so a second connection that wants to write still waits or gets `SQLITE_BUSY`. WAL also relies on memory shared between connections, so every process using the database must run on the same host.
code
sql · 2 linesPRAGMA journal_mode=WAL;
PRAGMA journal_mode;go deeper
Remember the one-line version: in WAL mode readers and the single writer stop blocking each other, but writers still take turns.
Explain why: changes are appended to a log instead of overwriting the database file, and each reader keeps an end mark that fixes its snapshot.
Show the limits you have met in production: one writer, same host only, long readers holding back checkpoints, and the rare SQLITE_BUSY cases WAL still has.
Frame WAL as a default you would set for a concurrent service, and say what it does not buy: it never adds writers, so write-heavy multi-host load needs another store.
## Two journal modes, two concurrency stories SQLite keeps a whole database in one file and lets many **database connections** use it at once: several threads in one process, several processes, or both. How those connections share the file depends on the **journal mode**. The default, `journal_mode=DELETE`, is one of the **rollback-journal** modes; `PRAGMA journal_mode=WAL` switches to **write-ahead logging**. The file layout behind each mode is a separate subject; what matters here is what each mode lets readers and a writer do at the same moment. ## Rollback-journal mode: the writer briefly shuts readers out In rollback mode a writer copies the original content of each page it will change into a rollback journal and then writes its changes **directly into the database file**. Readers can keep reading while the writer's changes are still only in its private memory, but before the first changed page reaches the database file every reader must be gone: - the writer announces that it wants to write, and new readers are refused; - it waits for the readers already inside to finish; - it holds the file alone while it writes and commits, and only then are readers let back in. Readers are never shown a half-written transaction, because they are locked out while it is being written. The cost is that a busy reader can delay a commit, and a commit briefly blocks every reader. ## WAL mode: readers beside one writer WAL inverts the arrangement. The original pages stay untouched in the database file and the writer **appends** changed pages to a separate log; a commit is a commit record appended to that log. Because nothing in the database file is overwritten during a transaction, readers keep reading while the writer writes and commits. Each read transaction, when it starts, remembers the position of the last valid commit record in the log, its **end mark**. For every page it needs, it takes the newest copy in the log before its end mark, or the page from the database file if the log holds none. The end mark does not move for the life of the read transaction, so a reader sees one consistent **snapshot**: a transaction that commits while it is reading stays invisible to it until it ends its transaction and starts a new one. | | Rollback-journal mode (default `DELETE`) | WAL mode | |---|---|---| | Where a writer's changes go | Into the database file, originals saved in the journal | Appended to the write-ahead log | | Readers during a write | Allowed until the writer must write the file, then shut out | Allowed throughout, each on its snapshot | | The writer during reads | Waits for current readers to leave before writing the file | Not blocked by readers | | Writers at once | One | One | | Processes on other hosts | Relies on the network filesystem's locks | Not supported: needs shared memory | ## What WAL does not change - **Still one writer.** There is only one log, so only one write transaction runs at a time. A second connection that wants to write waits under its busy handler or receives `SQLITE_BUSY`. - **Same host only.** Readers find pages in the log through an index kept in shared memory, so every process using the database must be on the same machine; WAL does not work over a network filesystem. - **Long readers have a cost.** Copying log pages back into the database file (a checkpoint) cannot pass the point a current reader's snapshot still needs, so a reader that never ends lets the log grow. How checkpoints work belongs with the file format. - **`SQLITE_BUSY` is rarer, not impossible.** The documentation lists a few cases: another connection holding the database in exclusive locking mode, the last connection cleaning up as it closes, and a connection running recovery after a crash, reported as `SQLITE_BUSY_RECOVERY`. ## Turning it on `PRAGMA journal_mode=WAL` returns the new mode, `wal`, when it succeeds, and the previous mode (for example `delete`) when the conversion could not be done. Unlike the other journal modes, WAL is **persistent**: a database set to WAL comes back in WAL mode when it is closed and reopened, and setting it on one connection sets it for every connection to that file. For a multi-threaded service writing request logs to one file, WAL is usually the first change to make. Dashboards and health checks that read the log table stop competing with the writer, and only writers queue for each other. It does not remove the need for a busy timeout and short write transactions, because writers still take turns.
- Does WAL mode let two SQLite connections write to the same database at the same time?No. WAL lets readers run beside a writer, but there is one log and only one write transaction at a time. A second writer waits under its busy handler, if one is set, or receives `SQLITE_BUSY`. WAL makes each write cheaper and stops readers blocking it, which often raises write throughput, but writes are still serialized.
- In SQLite WAL mode, why does a reader that began before another connection's commit still see the old rows?When a read transaction starts it records an end mark, the last valid commit record in the log, and reads every page as of that mark for as long as the transaction lasts. A later commit lies past its end mark, so it stays invisible. To see it, the reader ends its transaction (`COMMIT` or `ROLLBACK`) and starts a new one.
- WAL mode lets several connections write to the database at once.
- In WAL mode a reader always sees the newest commit, even mid-transaction.
- WAL is SQLite's default journal mode, so nothing needs to be set.
- In WAL mode readers block the writer until they finish.
- WAL works for processes on different hosts sharing a network drive.