SQLite WAL Mode: Let Readers and Writers Share the Database

The lock that bites at the worst time

You have a small service backed by SQLite. Reads are fast, writes are rare, everything is fine — until a background job starts a long write transaction and every incoming request starts failing with SQLITE_BUSY. The default rollback journal gives writers an exclusive lock on the whole database file, so one slow writer stalls all readers.

The takeaway: switching the journal mode to Write-Ahead Logging (WAL) lets readers keep working from a consistent snapshot while a single writer appends changes to a separate log file. For read-heavy workloads this commonly removes the busy errors entirely and can multiply throughput, at the cost of a couple of extra files and some checkpoint housekeeping.

What WAL actually changes

In the default rollback-journal mode, SQLite copies pages it's about to overwrite into a journal, then modifies the database file in place. Readers and writers therefore contend for the same file, and a writer needs an exclusive lock.

In WAL mode the logic flips: new versions of pages are appended to a separate -wal file, and the main database file is left untouched until a later checkpoint copies the logged pages back. Readers keep reading the old pages in the main file (plus whatever they need from the log up to their snapshot point), so a writer never blocks them. Writers still serialize against each other — WAL gives you one writer plus many concurrent readers, not multi-writer concurrency.

WAL has been available since SQLite 3.7.0 (2010), so any reasonably modern build supports it. The examples below assume SQLite 3.40+.

Enabling it and proving it works

WAL is a persistent property of the database, not a per-connection flag. Set it once from any connection:

-- run in the sqlite3 shell or any client
PRAGMA journal_mode=WAL;
-- expected result: a single row containing "wal"

No special filesystem permissions are needed beyond read/write access to the database's directory, because SQLite creates two sidecar files next to the database: mydb.db-wal and mydb.db-shm (the shared-memory index that coordinates readers). If the directory is read-only or on a network filesystem without working byte-range locking, WAL will fail or silently fall back — verify by re-running PRAGMA journal_mode; and checking it still returns wal.

To see the concurrency difference yourself, open two sqlite3 shells against the same database (both will see WAL mode, since it persists):

-- Shell 1: start a slow write
BEGIN IMMEDIATE;
INSERT INTO events (payload) VALUES ('big batch');
-- don't commit yet

-- Shell 2, while shell 1's transaction is open:
SELECT count(*) FROM events;
-- returns immediately with the pre-transaction count,
-- no SQLITE_BUSY

Shell 2 sees a consistent snapshot from before shell 1's commit — that's the whole point. Repeat the same experiment with PRAGMA journal_mode=DELETE; (the default) and shell 2's query will block or fail once shell 1 escalates to a reserved lock. In your application code, the practical effect is that you can stop wrapping every read in retry loops, as long as you still handle the case of two writers racing.

The trade-off: checkpoints and disk usage

WAL isn't free. The -wal file grows with every committed write until a checkpoint moves pages back into the main database and resets the log. SQLite checkpoints automatically once the WAL reaches roughly 1000 pages (about 4 MB with the default page size), but a long-lived read transaction pins its snapshot and prevents the checkpoint from reclaiming log pages past that point — the WAL keeps growing until the reader finishes.

Three practical consequences:

• Disk headroom. A busy database with a stuck reader can accumulate a WAL far larger than the database itself. Monitor the -wal file size, not just the .db file.
• Manual checkpoints. You can force one with PRAGMA wal_checkpoint(RESTART); — run it from any connection; it returns a row with busy/log/checkpointed page counts you can log to confirm it made progress. A RESTART checkpoint blocks if readers hold old snapshots, so don't run it on a latency-critical path.
• Environment limits. WAL requires working byte-range locking, which rules out most network filesystems (NFS, SMB mounts) for multi-process access. Tools built against SQLite older than 3.7.0 also can't open a WAL-mode database at all — a real concern if you ship database files to other systems.

Read performance can also degrade slightly when the WAL grows very large, because each read may need to consult the log index. Regular checkpoints keep this negligible.

A decision rule you can apply today

Enable WAL when your workload is read-heavy, your database lives on local disk, and you control the SQLite version of every consumer. Stay on the rollback journal when the file must be a single self-contained artifact, must be readable by ancient SQLite builds, or sits on a network share.

The actionable part: on a staging copy of your database, run PRAGMA journal_mode=WAL;, confirm it returns wal, then replay your slowest write job while hitting the read endpoints. Watch for SQLITE_BUSY disappearing from the logs, and watch the -wal file size during a long read to understand your checkpoint behavior. That fifteen-minute experiment tells you more than any benchmark table.