SQLite Write-Ahead Logging: Concurrency and Performance Without Sacrificing ACID

The Problem: Default Rollback Journal Serializes Access

SQLite's default DELETE journal mode uses a rollback journal: before modifying a page, the engine writes the original page to a separate journal file, then updates the database file. During a write transaction, SQLite holds an exclusive lock on the database file. Readers must wait for that lock to release; writers block all readers. For workloads with frequent writes and concurrent reads — typical in web backends, embedded devices, or local analytics — this serialization becomes a throughput bottleneck.

Takeaway: Switching to Write-Ahead Logging (WAL) mode lets readers and writers operate concurrently without blocking each other, while preserving full ACID guarantees. Write-heavy workloads typically see 2–10× higher transaction throughput because fsync occurs less often and readers never wait for exclusive locks.

How WAL Changes the Concurrency Model

In WAL mode, commits append frames (page images plus metadata) to a separate -wal file instead of overwriting the main database file. A companion -shm shared-memory file coordinates access between processes. Readers snapshot the database at the start of their transaction by noting the current WAL frame index; they read from the database file and any WAL frames up to that index. Writers append new frames and hold an exclusive lock only for the brief moment of committing the transaction header — not for the entire write.

This means:

• Multiple reader processes can run simultaneously with one writer.
• Readers never block writers; writers never block readers.
• Only one writer can commit at a time (enforced by an exclusive lock during commit).

A checkpoint operation copies committed frames from the -wal file back into the main database file, truncating the WAL. Checkpoints happen automatically (default: every 1000 pages) or manually via PRAGMA wal_checkpoint.

Worked Example: Enable, Verify, and Checkpoint WAL

Run these commands in a shell with write access to the database directory. No elevated privileges are needed beyond filesystem write permission.

# 1. Open (or create) the database and enable WAL mode
sqlite3 /var/lib/app/data.db "PRAGMA journal_mode=WAL;"
# Expected output: wal

# 2. Verify the mode persisted
sqlite3 /var/lib/app/data.db "PRAGMA journal_mode;"
# Returns: wal

# 3. Confirm -wal and -shm files exist
ls -la /var/lib/app/data.db*
# Should show data.db, data.db-wal, data.db-shm

# 4. Create a table and insert data (frames go to -wal)
sqlite3 /var/lib/app/data.db "CREATE TABLE events (id INTEGER PRIMARY KEY, ts TEXT, payload TEXT); INSERT INTO events (ts, payload) VALUES (datetime('now'), 'first');"

# 5. Force a full checkpoint (blocks until all frames copied, WAL reset)
sqlite3 /var/lib/app/data.db "PRAGMA wal_checkpoint(RESTART);"
# Returns: 0|0|0  (busy|log|checkpointed pages — all zero means WAL truncated)

# 6. Check database page count and freelist after checkpoint
sqlite3 /var/lib/app/data.db "PRAGMA page_count; PRAGMA freelist_count;"

Where to run: Any host with the sqlite3 CLI installed (Linux, macOS, Windows). The database directory must be writable by the process — read-only directories or filesystems without lock support (some NFS configurations) will cause WAL to fail or fall back to rollback journal.

Expected checks: After step 1, PRAGMA journal_mode returns wal. After step 3, -wal and -shm files appear. After step 5, the WAL file size drops to near zero (or the file is removed on some platforms).

Risks: If the filesystem lacks proper locking (e.g., misconfigured NFS), enabling WAL may silently fall back to DELETE mode or return SQLITE_IOERR_LOCK. Test on the target deployment filesystem before relying on WAL in production.

Configuration Knobs That Matter

Pragma	Default	Practical Guidance
journal_mode	DELETE (or WAL for new DBs on SQLite ≥3.35.0)	Set to WAL once per database; persists across connections.
wal_autocheckpoint	1000 pages	Lower (e.g., 200) for write-heavy workloads to bound WAL growth; higher for read-heavy to reduce checkpoint overhead.
synchronous	NORMAL	Keep NORMAL or FULL in production. OFF risks corruption on power loss because frames may not reach durable storage before commit returns.
mmap_size	0 (disabled)	Set to e.g., 268435456 (256 MiB) for large databases on filesystems supporting mmap. The -shm file must be on an mmap-capable filesystem.
locking_mode	NORMAL	EXCLUSIVE can improve single-process performance but prevents other processes from opening the DB.

Apply pragmas per connection or via the first connection that creates the database file. Example for a long-lived application connection:

-- In application startup (SQL executed once per connection)
PRAGMA journal_mode=WAL;
PRAGMA wal_autocheckpoint=200;
PRAGMA synchronous=NORMAL;
PRAGMA mmap_size=268435456;

Limits and Common Mistakes

1. Long-Running Readers Block Checkpoints

A reader that holds a transaction open (e.g., a long SELECT in a REPEATABLE READ snapshot) prevents the checkpoint from advancing past its frame index. The -wal file grows until the reader commits or rolls back. Monitor with:

sqlite3 /var/lib/app/data.db "PRAGMA wal_checkpoint;"
-- Returns three integers: busy, log, checkpointed
-- If 'busy' > 0, a reader is blocking the checkpoint

Mitigation: keep read transactions short; use explicit PRAGMA wal_checkpoint(RESTART) during maintenance windows; consider PRAGMA read_uncommitted=TRUE for reporting queries that can tolerate dirty reads (but understand the isolation trade-off).

2. WAL Does Not Support Concurrent Writers

Only one writer can commit at a time. Applications with high write concurrency from multiple processes will serialize at commit. Connection pooling (reusing a single writer connection) or a writer queue in the application layer avoids SQLITE_BUSY errors.

3. Schema Changes Require Exclusive Lock

ALTER TABLE, CREATE INDEX, and other DDL acquire an exclusive lock that blocks all readers and writers. Plan migrations during low-traffic windows. For zero-downtime schema changes, use the online schema change pattern: create new table, copy data in batches with triggers, then atomic rename.

4. Windows File Locking and Antivirus Interference

On Windows, mandatory file locking can cause "database is locked" errors if antivirus or backup tools hold handles on -wal/-shm files. Exclude the database directory from real-time scanning.

5. ATTACHed Databases and TEMP Databases

Each attached database has its own journal mode. :memory: and TEMP databases cannot use WAL. If you attach multiple databases, set PRAGMA journal_mode=WAL on each.

Verification Checklist for Production Deployment

1. Filesystem compatibility: Run sqlite3 test.db "PRAGMA locking_mode=EXCLUSIVE; PRAGMA journal_mode=WAL;" on the target filesystem. Failure indicates lock/mmap issues.
2. Concurrency test: Open two sqlite3 shells on the same DB. In shell A: BEGIN; SELECT * FROM large_table; (hold open). In shell B: INSERT INTO large_table ...; — observe no blocking in WAL vs. blocking in DELETE mode.
3. Crash recovery: Kill a writer process mid-transaction, reopen the DB. Committed transactions survive; uncommitted are rolled back automatically on next open.
4. Benchmark: Compare TPS with PRAGMA journal_mode=DELETE vs WAL under your workload (e.g., 1000 inserts in a loop). Use sqlite3_analyzer or a custom script.
5. WAL growth monitoring: Periodically check PRAGMA wal_checkpoint and file sizes. Alert if -wal exceeds a threshold (e.g., 100 MB).

When Not to Use WAL

• Read-only databases or read-only filesystems (WAL requires writable directory for -wal/-shm).
• Network filesystems without reliable locking (some NFS, SMB configurations).
• Applications that rely on PRAGMA synchronous=OFF for maximum speed and accept corruption risk — WAL with NORMAL is safer and often faster than DELETE with OFF.
• Single-process, write-once/read-many workloads where the overhead of -shm coordination isn't justified.

Quick Diagnostic: Is WAL Actually Active?

sqlite3 /path/to/db "PRAGMA journal_mode;"
-- Returns: wal  (active)
-- Returns: delete  (not active — check filesystem locks, permissions, or SQLite version)

If the pragma returns delete after you set WAL, the filesystem likely lacks shared-memory support or the directory isn't writable. Check sqlite3_errmsg() or the CLI error output for SQLITE_IOERR_LOCK or SQLITE_CANTOPEN.