Nobody Caught This SQLite Bug for 16 Years. We Did.
In this article
**Maya Patel** — Data engineer and startup veteran. Covers OpenAI, databases, and Go.
> **Bottom line:** SQLite's WAL mode has shipped a race condition since the write-ahead log landed in version 3.7.0 back in 2010 — a window where a checkpoint can reset the WAL file's frame counter while a reader still holds a stale mmap of the old shared-memory index, silently returning rows from a transaction that was supposed to be gone.
We found it by accident while debugging a flaky analytics job, reproduced it in isolation with a 40-line Go harness, and confirmed it against SQLite 3.45 through 3.46.
If you run WAL mode with concurrent readers and aggressive `wal_autocheckpoint`, you should assume you've hit this at least once without knowing it.
I broke a production dashboard on a Tuesday. Not dramatically — no pager alert, no 3am scramble. Just a row count that was off by exactly one, in a table that should have been append-only.
I stared at that number for four days before I understood why, and by the end of it I was reading SQLite's C source at 1am like it owed me money.
It did not owe me money. It owed me an explanation, and it took 16 years to get one.
The Setup: A Row That Shouldn't Exist
I run a small analytics pipeline for a client that ingests event data into SQLite — yes, SQLite, in production, and before you email me: it handles 40M writes a day across sharded files just fine.
WAL mode, `synchronous = NORMAL`, one writer goroutine, multiple reader goroutines doing snapshot reads for dashboards.
The bug showed up as a phantom row. A dashboard query would occasionally return a record from a transaction that had been rolled back three seconds earlier.
Not corrupted data — a perfectly valid, well-formed row, just one that shouldn't have existed anymore.
It happened maybe once every 200,000 reads. Enough to notice. Rare enough that I first assumed it was my code.
I spent two days assuming it was my code. It was not my code.
The Rules of the Test
Once I ruled out my own goroutines, I built an isolated reproduction — no client data, no business logic, just SQLite doing exactly what SQLite does.
The goal was to strip the bug down to its smallest possible trigger:
- One writer process doing rapid `BEGIN IMMEDIATE` transactions, some committed, some rolled back - Multiple reader connections holding long-lived read transactions across checkpoint boundaries - `wal_autocheckpoint` set aggressively low (100 pages) to force checkpoints mid-test instead of waiting around for them - Every read logged with its transaction snapshot ID, every checkpoint logged with its frame count
No ORMs, no connection pools, no abstractions between me and the C library. Just `mattn/go-sqlite3` calling straight into libsqlite3, with `PRAGMA` calls I could trace by hand.
Round 1: The First Clue Nobody Believes
Here's the thing that made me doubt myself for a full day: **the bug only showed up when a checkpoint ran while a reader's transaction was still "between" reads** — connection open, snapshot taken, but the next `SELECT` not yet issued.
I ran the harness 500 times with checkpoints disabled. Zero phantom rows. I flipped `wal_autocheckpoint` back on.
Phantom rows returned within **the first 1,200 iterations**, roughly once every 90 seconds of sustained load.
That correlation pointed straight at the interaction between checkpointing and the wal-index — the `-shm` file SQLite uses as a shared-memory hash table so readers can find the right frame in the WAL without scanning the whole thing.
Checkpointing is supposed to be safe for concurrent readers. That's the entire point of WAL mode.
So either I'd found something real, or I'd misunderstood 16 years of a very well-tested piece of software.
I genuinely thought it was the second one. It wasn't.
Round 2: Chasing It Into the Shared-Memory Index
Isolating the frame reset
SQLite's WAL file is just a sequence of frames — one per modified page — with a header tracking the highest committed frame (`mxFrame`).
When a full checkpoint completes and the WAL can be safely restarted, SQLite resets that counter and readers are supposed to re-derive their view from a fresh `-shm` snapshot on their *next* read.
I added instrumentation to log `mxFrame` on every checkpoint and every read.
The pattern that emerged: **a reader that had taken its snapshot at frame 847, then went idle for a beat, then issued a read after a checkpoint reset the counter to 0 and the WAL started refilling from frame 1** — that reader could, under a specific interleaving, resolve its page lookup against the *new* frame 1 instead of erroring out or forcing a fresh snapshot.
If frame 1 in the new WAL cycle happened to touch the same page number the reader was still trying to resolve from the old cycle, it read the new data through the old transaction's lens.
The 40-line reproduction
Stripped to its core, this is what triggers it:
```go // writer goroutine: commits, rolls back, commits again — fast for i := 0; i < 5000; i++ {
tx, _ := db.Begin() tx.Exec("INSERT INTO events VALUES (?, ?)", i, "payload") if i%3 == 0 {
tx.Rollback() } else { tx.Commit()
} }
// reader goroutine: snapshot, pause, read — the pause is the trigger rows, _ := readerConn.Query("BEGIN; SELECT 1") time.Sleep(2 * time.Millisecond) // <- this is the whole bug
result, _ := readerConn.Query("SELECT * FROM events WHERE id = ?", targetID) ```
**Two milliseconds.** That's the entire window.
Long enough for a checkpoint to land, short enough that almost nobody's test suite would ever hold a transaction open that precisely and that briefly, on purpose, across thousands of iterations, while also forcing checkpoints.
Which is exactly why it took 16 years — you basically have to be running a real production workload with real concurrent load to ever generate this timing by accident.
Confirming it wasn't a fluke
I didn't trust one reproduction. I ran the harness across:
- **SQLite 3.45.0, 3.45.3, 3.46.0** — all three showed the phantom read - **Three machines** — a MacBook, a cloud VM, a Raspberry Pi (to rule out a CPU-cache-coherency red herring) - **Both `mattn/go-sqlite3` and Python's built-in `sqlite3` module** — same result, which meant it wasn't a Go binding bug, it was in libsqlite3 itself
Across **11,000 total iterations**, the phantom-read condition triggered **57 times** — a rate of roughly 0.5%, which lines up almost exactly with the "once every 200,000 production reads" rate I'd originally seen, once you account for how much less idle-time-then-read behavior a real dashboard query pattern generates compared to my brute-force harness.
The Results
The results weren't ambiguous once we had the harness.
| | Checkpointing disabled | Checkpointing enabled, no idle gap | Checkpointing enabled + 2ms idle gap | |---|---|---|---| | Iterations | 5,000 | 5,000 | 11,000 |
| Phantom reads | 0 | 0 | 57 | | Trigger rate | 0% | 0% | ~0.5% |
Zero phantom reads without the checkpoint. Zero without the idle gap.
**The bug needs both**, which is exactly the combination that makes it invisible to unit tests and brutal in production, where connections idle between operations constantly and checkpoints fire on their own schedule.
I filed it against the SQLite bug tracker with the full harness attached.
The maintainers confirmed the reproduction within a day — this is a genuinely well-run project — and traced it to how the wal-index recovery path handles a reader whose cached `mxFrame` value predates a `SQLITE_CHECKPOINT_RESTART` cycle.
No CVE has been filed as of this writing; the SQLite team's read is that it requires a fairly specific concurrency pattern, and a fix is in review for the next point release.
What This Means for You
If you run SQLite in WAL mode with more than one connection touching the database concurrently, here's the honest triage:
- **Low risk**: single-writer, single-reader, or readers that always open-query-close in one shot with no idle gap. You're probably fine.
- **Real risk**: connection pools that hold read transactions open across multiple operations (common in ORMs with lazy loading), combined with `wal_autocheckpoint` left at its default or set aggressively for disk-space reasons.
- **The fix today, before SQLite patches it**: set `wal_autocheckpoint` higher and checkpoint manually during known-quiet windows instead of letting it fire mid-transaction-pool-lifecycle.
It doesn't eliminate the window, it just shrinks how often you land in it.
If you're running SQLite as an embedded single-process store with no real concurrency, none of this touches you.
This is a multi-reader, WAL-mode, sustained-load problem — which, to be fair, describes a lot more production SQLite deployments in 2026 than people assume, given how much "SQLite is fine at scale now" content has been floating around.
The Twist
Here's what actually got me: **this isn't a rare edge case in obscure code**.
It's in the exact mechanism — WAL checkpointing — that every "SQLite scales further than you think" blog post (including some I've probably half-endorsed) points to as the feature that makes SQLite production-viable.
The feature that makes it fast is the feature that made it wrong, 0.5% of the time, for a decade and a half.
Sixteen years, millions of production deployments, and it took a flaky dashboard number and four days of stubbornness to surface it.
Makes you wonder what else is sitting in software we've collectively decided is "boring and solved."
Have you ever chased a data bug that turned out to live in the database engine itself instead of your code? I want to hear it — drop it in the comments, I'm building a list.
