A Busy Checkpointer Is Enough

SQLite's "WAL-reset bug" sat in every release from 3.7.0 (2010) to 3.51.2 and was fixed in 3.51.3 in March 2026. Tailscale traced months of trouble to it, and Phil Eaton's "Another look at SQLite's WAL-Reset bug" reproduces it with about 100 lines of C. His reproducer uses a 256 MB database and a 1 GB memory map, because unmapping that much memory slows the checkpoint down at exactly the wrong moment and gives a writer time to slip in.

I was reading SQLite's write-ahead log code and wanted to see whether you need that help. You don't. A tiny database, no memory map, and separate processes, with one connection running checkpoints in a loop while others commit small transactions, damaged the database in every one of 20 ten-second runs on an affected version. The fixed version came through all 20 clean.

The race in one paragraph

In WAL mode, commits append pages ("frames") to a log file, and a checkpoint copies them back into the database. A shared-memory header records how many frames the log holds (mxFrame) and how many have been copied back (nBackfill). Once everything is copied back, the next writer may restart the log from frame 1. The bug: a checkpoint reads mxFrame, a writer restarts the log and commits a few new frames, and the checkpoint carries on with its stale count. It sets nBackfill to the old, larger number, so SQLite believes the new frames were already copied back. Readers skip them and so does the next checkpoint. The fix rechecks the log's salt (its generation number) after the checkpoint takes its lock, and gives up if the log was restarted in the meantime.

The setup

Python's sqlite3 module linked against SQLite 3.46.1 (affected), and the same script through apsw with SQLite 3.53.4 (fixed). Writer processes loop on BEGIN IMMEDIATE, insert a row with a 200-byte blob, bump a per-writer counter, and commit. Checkpointer processes loop on PRAGMA wal_checkpoint(PASSIVE). The files live on a RAM-backed filesystem. At the end I run integrity_check and look for every row each writer was told was committed.

Spinning checkpoints keep copying the log back as fast as it fills, so it restarts constantly: about 8,000 restarts a second at about 90,000 commits a second, roughly one commit in eleven. Each restart is a chance for the race.

Results

All runs were 10 seconds. One writer and one checkpointer is enough, which matches Eaton's three-connection setup. More checkpointers mean more restarts and more chances. Like him, I saw lost writes more often than corruption: in more than half of the damaged runs integrity_check said "ok" while committed rows were gone. An integrity check verifies that the file is consistent with itself, not that it still holds what you wrote.

You can watch it happen

The -shm index that holds that header is an ordinary file, so a separate process can map it and poll the header about 600,000 times a second. The bug leaves a fingerprint that can't occur legitimately: nBackfill greater than mxFrame, more frames copied back than the log holds. There's one catch. While a log restart is in progress, SQLite writes the new header (with mxFrame = 0) slightly before it zeroes nBackfill. My first monitor flagged that window on the fixed version too, so I only count the fingerprint when mxFrame is above zero.

With that rule, the fingerprint showed up in every damaged run and in none of the 20 runs on the fixed version. It also showed up once in a run where every row survived. My guess is that the skipped frames in that run were overwritten by later commits before anything read them.

Default settings were much safer

With no explicit checkpointer, SQLite checkpoints automatically when the log passes 1,000 pages, and the connection that just committed runs it. I ran 40 one-minute runs on 3.46.1 with two or four writers and only automatic checkpoints: 316 million commits and at least 637,000 log restarts, with no fingerprint, no missing rows, and no corruption.

That's a rough comparison, but a telling one. With one writer and one spinning checkpointer, damage showed up about once per 146,000 restarts. At that rate, 637,000 restarts would be expected to produce about four damaged runs, and seeing none would be about a 1% chance. My guess is that the race needs a checkpoint that runs alongside a different connection's commit, and here the checkpoints almost always run right after a commit, in the connection that made it.

Two of my early attempts at this measurement were invalid. In the first, eight parallel runs filled the 4 GB RAM disk. In the second, writers died on "database is locked" and left most runs with a single writer. The locks weren't the bug: BEGIN IMMEDIATE waited out its full 5-second busy timeout because the other writers, looping flat out, kept grabbing the lock between its retries. The final runs log and retry those errors instead (there were 194 in 40 minutes).

What I'd take from it

• If you run SQLite older than 3.51.3 and checkpoint from a background thread or process, upgrade. Rows can go missing while the integrity check still passes.
• The race doesn't need a big database or a memory map. A busy checkpointer plus small, frequent commits hit it within seconds here.
• Default automatic checkpointing was far less exposed in my runs. That's not a guarantee, and it's no reason to stay on an affected version.
• If you can't upgrade yet, a process that maps the -shm file and alarms on nBackfill > mxFrame (with mxFrame > 0) caught every damaged run here.

Limits: one machine, a RAM-backed filesystem, and Python on top of SQLite, so the timing differs from a real disk and from C. Run counts for the smaller configurations are small (6 each). The autocheckpoint figure bounds this workload, not every workload.