SQLite WAL Lets Readers Continue, Not Writers Multiply

Write-ahead logging changes how SQLite coordinates readers and a writer. Readers can usually continue using their snapshot while a writer appends to the WAL, but a database still has only one writer at a time. WAL is a concurrency improvement, not a conversion into a multiwriter server.

That distinction explains why enabling WAL can improve a read-heavy application without solving contention from long write transactions.

The concrete problem

Use a local SQLite database with a current supported SQLite build. WAL relies on shared-memory coordination and is not an appropriate way to share one database file over a network filesystem among machines. Open two connections from separate local processes to examine the behavior.

Figure: Reader snapshot → Single writer appends WAL → Checkpoint respects readers.

A worked example

PRAGMA journal_mode = WAL;
CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY, state TEXT NOT NULL
);
INSERT INTO tasks VALUES (1, 'queued');
-- Connection A
BEGIN;
SELECT state FROM tasks WHERE id=1;
-- Connection B
BEGIN IMMEDIATE;
UPDATE tasks SET state='running' WHERE id=1;
COMMIT;
-- Connection A still has its original read snapshot.
SELECT state FROM tasks WHERE id=1;
COMMIT;
PRAGMA wal_checkpoint(PASSIVE);

Why the behavior matters

The WAL stores changes that are later incorporated into the database by checkpointing. A reader's view is associated with its read transaction, so it does not automatically switch snapshots midway through the transaction when another connection commits.

A checkpoint must respect readers that still need older state. Long-lived readers can therefore prevent complete checkpoint progress and allow the WAL to grow. Monitor transaction duration and checkpoint results rather than deleting a large -wal file manually. The WAL and shared-memory files are part of the database's active operating state.

Failure modes and design tradeoffs

WAL does not remove SQLITE_BUSY. Writers still serialize, and some other operations can encounter contention. Keep write transactions short and avoid holding them while calling external services. Durability also depends on synchronization settings and the storage environment, so journal_mode alone is not a complete durability policy.

Do not copy only the main database file while an active WAL contains committed changes. Use SQLite's backup mechanisms or a carefully documented shutdown/checkpoint procedure. A plausible-looking copied file can silently omit recent committed data.

What to verify before adoption

Assert that connection A reads the old state until it ends its transaction, while a fresh connection sees the committed new state. Hold a reader open during several writes and inspect checkpoint progress. Measure database and WAL size separately. Close all test connections and use normal SQLite APIs for cleanup.

Primary reference

• SQLite documentation. Verify the exact deployed version and driver behavior before using the example in a production runbook.