SQLite’s Write-Ahead Logging (WAL) mode allows readers to access the database while a writer is actively modifying it, a feat previously impossible with its default journaling modes.
Let’s see it in action. Imagine two terminals, both connected to the same test.db file.
Terminal 1 (Reader):
sqlite3 test.db
.mode json
SELECT count(*) FROM items;
Terminal 2 (Writer):
sqlite3 test.db
BEGIN;
INSERT INTO items (name) VALUES ('new item');
-- Leave this transaction open for a bit
Now, switch back to Terminal 1. If you run the SELECT count(*) query again while Terminal 2’s transaction is still open, you’ll see the old count, not the new one that will eventually be committed. This is WAL working: readers see a consistent snapshot of the database from before the writer’s transaction began.
To enable WAL mode, you issue a simple PRAGMA command:
PRAGMA journal_mode=WAL;
This command creates two new files alongside your main database file: test.db-wal and test.db-shm. The .wal file is the write-ahead log itself, where new data is written before being appended to the main database. The .shm file (shared memory) is used by WAL to keep track of which parts of the .wal file are valid and can be read by concurrent readers.
The magic of WAL lies in its append-only nature for writes. When a writer needs to modify a page in the database, it first writes the new version of that page into the .wal file. Crucially, it doesn’t immediately overwrite the old page in the main database file. Instead, it records the transaction in the WAL. Readers, when they need a page, first check the .wal file. If the latest version of the page is there, they read it. If not, they fall back to reading the page from the main database file. This separation allows readers to proceed unimpeded by writers.
Periodically, the WAL file is "checkpointed." This is a process where the changed pages from the .wal file are merged back into the main database file, and the corresponding entries in the WAL are marked as no longer needed. This prevents the .wal file from growing indefinitely. You can manually trigger a checkpoint with:
PRAGMA wal_checkpoint;
The primary problem WAL solves is the reader-writer conflict inherent in SQLite’s default rollback journal modes. In rollback mode, writers lock the entire database, preventing any readers from accessing it. If a read operation is in progress when a write begins, the writer has to wait for the reader to finish. If a write is in progress and a reader starts, the reader has to wait. This contention can bring applications to a crawl. WAL effectively eliminates this by allowing readers to always proceed.
The mental model to hold onto is that WAL creates a separate, append-only "staging area" for changes. Readers look in this staging area first for the most up-to-date data. If it’s not there, they know it’s still in the main database file and can safely read that. This decoupling is the key.
One of the less obvious implications of WAL is how it handles database corruption. Because changes are written to the .wal file sequentially before being merged into the main database, a crash during a write operation is less likely to corrupt the entire database. The main database file might be left in an older, consistent state, and the .wal file can often be discarded or partially recovered, making recovery simpler than with rollback journals where a partially written page in the main file could be problematic.
The next concept you’ll likely encounter is managing the size of the WAL file and understanding the wal_autocheckpoint PRAGMA.