Reproduce SQLite WAL Checkpoint Starvation With One Forgotten Reader

Reproducing the Risk

A SQLite service can keep answering health checks while its WAL grows without a successful checkpoint. One forgotten read transaction is enough to reproduce the risk.

Controlled Drill

Run a controlled drill:

• Enable WAL mode and create a small table.
• Open connection A, begin a read transaction, and keep it open.
• From connection B, commit batches of writes for 60 seconds.
• Sample WAL bytes and checkpoint results every second.
• Release A and observe recovery.

Use the following commands:

PRAGMA journal_mode = WAL;
PRAGMA wal_checkpoint(PASSIVE);

Record the three checkpoint counters plus duration, WAL file size, oldest transaction age, write latency, and free disk bytes. A green SELECT 1 says nothing about checkpoint progress.

Fault-Injection Gate

My fault-injection gate looks like this:

Given:   one reader holds its transaction
When:    10,000 writes commit
Then:    writes remain bounded
  And:   WAL growth triggers an alert before the disk budget
When:    the reader closes
Then:    checkpoint progress resumes and WAL size converges

Operational Guidance

Do not start with an automatic TRUNCATE loop. A busy result is evidence of contention; aggressive checkpoints can add latency without fixing the reader lifecycle.

First identify long transactions, ensure result iterators close, and define a maximum transaction age. SQLite’s WAL documentation explains that a checkpoint must stop when it reaches pages beyond an active reader’s end mark. This is why the drill needs concurrency rather than a synthetic file-growth assertion.

Operational thresholds should be tied to a disk budget: alert when projected WAL growth reaches the remaining safe window, not at an arbitrary file size.

The runbook should name how to find the oldest reader, how to shed writes, and when a restart is safer than waiting. A health check is useful only when it measures the subsystem that is failing.