SQLite WAL Mode: When to Switch and What Breaks

The Problem: Readers Block Writers, Writers Block Readers

SQLite's default rollback journal (DELETE mode) uses a simple locking protocol: a writer takes an exclusive lock, preventing any reader from accessing the database until the transaction commits. On a busy application with frequent reads and occasional writes, this serializes everything. You see SQLITE_BUSY errors, latency spikes, and throughput that flatlines once concurrency exceeds a handful of connections.

Write-Ahead Logging (WAL), introduced in SQLite 3.7.0 (2010), changes the locking model so readers never block writers and writers never block readers. The trade-off is two extra files, a checkpointing mechanism you need to understand, and a few environments where WAL simply doesn't work.

How WAL Changes the File Layout

In DELETE mode, each transaction writes a rollback journal, modifies the main database file in place, then deletes the journal. In WAL mode, changes go to a separate *-wal file appended sequentially. The original database file stays read-only for existing readers. A *-shm shared-memory file coordinates access via memory-mapped I/O and POSIX advisory locks.

After enabling WAL, a directory listing shows three files:

test.db
 test.db-wal
 test.db-shm

The -wal file grows until a checkpoint merges frames back into test.db. The -shm file is an index of WAL frames and lock state; it's always mmapped, even if you disable PRAGMA mmap_size.

Enabling WAL and Verifying It Works

Run this once per database file (it persists):

sqlite3 test.db "PRAGMA journal_mode=WAL;"

The command returns wal on success. If it returns delete, the database is on a filesystem that doesn't support the required locking (common on NFS/SMB) or the directory isn't writable.

Verify the three files exist:

ls -la test.db*

Worked Example: Measuring Contention Reduction

Create a table and simulate 10 concurrent readers with one writer on an SSD:

sqlite3 test.db "CREATE TABLE big_table (data BLOB);"
# Terminal 1: start 10 long-running readers
for i in {1..10}; do
  sqlite3 test.db "SELECT * FROM big_table;" &
done
# Terminal 2: writer inserts 100 rows
sqlite3 test.db "BEGIN; INSERT INTO big_table SELECT randomblob(100) FROM generate_series(1,100); COMMIT;"

In DELETE mode, the writer blocks until all 10 readers finish. In WAL mode, the writer commits immediately; readers see a consistent snapshot from before the write. On a typical NVMe SSD, this pattern shows roughly 5× higher write throughput with WAL.

To confirm no SQLITE_BUSY errors occurred, check the writer's exit code (should be 0).

The Checkpoint Trap: Long Readers Pin the WAL

Checkpointing merges WAL frames back into the main database. By default, SQLite runs an automatic checkpoint when the WAL reaches 1000 pages (PRAGMA wal_autocheckpoint=1000). However, a checkpoint can only advance past frames that no active reader is using. A long-running SELECT * FROM big_table holds a read lock on the WAL tail, preventing checkpoint progress. The -wal file grows until disk fills.

Diagnose with:

sqlite3 test.db "PRAGMA wal_checkpoint(PASSIVE);"

Output columns: busy (frames blocked by readers), log (total frames in WAL), checkpointed (frames already merged). If log > 0 and checkpointed = 0 repeatedly, a reader is pinning the WAL.

Mitigations:

• Set a busy timeout: PRAGMA busy_timeout=5000; so writers retry instead of failing immediately.
• Split analytics workloads to a read replica (copy the database file after a full checkpoint).
• Use PRAGMA wal_checkpoint(FULL); during a maintenance window when no readers are active.

Durability: synchronous=NORMAL vs FULL

WAL's default PRAGMA synchronous=NORMAL fsyncs the WAL file at checkpoint boundaries, not on every commit. A power loss can lose the last transaction but won't corrupt the database. For full durability matching DELETE mode's synchronous=FULL, set:

PRAGMA synchronous=FULL;

Expect higher write latency (each commit waits for disk). Benchmark your workload; many applications accept NORMAL for the throughput gain.

Where WAL Fails: Network Filesystems and Read-Only Media

WAL requires POSIX advisory locking on the -shm file. Most NFS and SMB implementations either don't support it or implement it incorrectly across multiple clients. If your database lives on a network mount, test locking behavior before production: open two connections from different hosts and attempt concurrent write/read. If you see SQLITE_BUSY or corruption, stay with DELETE mode.

Read-only deployments (container images, immutable roots) also can't use WAL because the directory must be writable for -wal and -shm creation.

Backup: Don't Just Copy Files

Copying test.db alone while WAL is active yields a corrupt backup—the WAL frames aren't applied. Safe approaches:

1. Use the SQLite backup API (sqlite3_backup_init) which handles WAL internally.
2. Run PRAGMA wal_checkpoint(FULL); VACUUM; then copy the single .db file (WAL and SHM are removed after full checkpoint + vacuum).
3. If you must copy files, copy all three atomically (e.g., cp test.db* backup/ while no writer is active), but this is fragile.

32-bit Address Space Limit

On 32-bit processes, the mmapped -shm file consumes virtual address space. The 2 GB limit caps usable WAL size; once hit, checkpoints fail with SQLITE_BUSY. Workaround: run on 64-bit, or keep WAL small with frequent checkpoints (PRAGMA wal_autocheckpoint=100).

Closing Checklist

• Enable WAL with PRAGMA journal_mode=WAL; and verify three files appear.
• Test your filesystem: concurrent writer + readers from multiple processes/hosts.
• Monitor WAL size in production; alert if wal_checkpoint(PASSIVE) shows persistent busy > 0.
• Use the backup API or full-checkpoint+vacuum before file copies.
• Set synchronous=FULL only if you need commit-level durability and can accept the latency.

WAL is the right default for most local-disk SQLite deployments. The extra files and checkpoint semantics are manageable once you understand them. The exceptions—network mounts, read-only media, 32-bit processes—are exactly the places where the old rollback journal still wins.