Skip to content
Published on

The SQLite WAL-Reset Bug: A Data Corruption Race That Hid for 15 Years

🇰🇷KO🇺🇸EN🇯🇵JA🇨🇳ZH
Split View필사 모드
Share
Authors

Introduction — The Footnote on "Choose Any Three"

The top of SQLite's documentation pages always carries the same line: "Small. Fast. Reliable. Choose any three." And for the most part, SQLite has kept that promise. As covered in SQLite Is Everywhere, this engine runs quietly inside your phone, your browser, and your avionics, and its reliability is treated as close to an infrastructure constant.

Which is what makes the March 2026 incident interesting. In the documentation's own words, "one of the SQLite developers (Dan)" found and fixed, on March 3, 2026, a bug that could rarely corrupt a database in WAL mode. The SQLite team's own name for it is the "WAL-reset bug." And this bug had been alive since 3.7.0 (2010-07-21), when WAL first shipped, all the way through 3.51.2 (2026-01-09). In the release notes' own wording: "A 15-year-old database corruption bug was discovered a few days ago and has now been fixed."

Let's set the temperature first. This is not the kind of thing that should page you tonight. SQLite's own documentation states plainly that "this is not an emergency," and backs that up with reasoning. But how this bug came to exist and how it was caught is worth reading if you use WAL — it's a remarkably clean, textbook case of what happens when two locks, not one, split the guardianship of a single field.

WAL Reset — The Neighborhood This Bug Lives In

Before looking at the bug, we need the stage. WAL's basic structure was covered in SQLite Internals; here we'll touch only the parts directly relevant to this bug.

In WAL mode, writes are appended as frames to the end of the WAL file rather than to the database file itself. Reads look at both the database file and the WAL together. But if the WAL grows without bound, reads get slower — in the documentation's words, read performance degrades as the WAL file grows, because the time to check the WAL is proportional to its size.

That's why checkpointing happens. A checkpoint writes the WAL's contents back into the database file (backfill). The default strategy is to let the WAL grow to roughly 1000 pages, then run a checkpoint on every subsequent COMMIT until the WAL shrinks back below 1000 pages.

Here comes the central concept of this post. Once the entire WAL has been transferred into the database, the sync has completed, and no reader is still reading the WAL, the next writer rewinds the WAL back to the start and begins writing the new transaction at the front of the WAL. This is the "WAL reset." It isn't deleting and recreating the file — it's resetting the cursor to zero and overwriting from the front. This rewind is what keeps the WAL file from growing forever.

The reset is a pure optimization, and it works flawlessly the vast majority of the time. The problem lay in the bookkeeping that decides "is it safe to reset."

Two Ledger Lines — mxFrame and nBackfill

That bookkeeping lives in the wal-index (shared memory, usually the -shm file) header. Two fields matter for this post.

mxFrame     Number of valid frames in the WAL.
            Since frames are numbered starting at 1, this also equals the
            index of the last valid commit frame.
            0 means the WAL is empty -> everything can be read straight from the DB file.

nBackfill   Number of WAL frames already copied into the database.
            Always nBackfill <= mxFrame.

            mxFrame == nBackfill  ->  the entire WAL has been applied to the DB
                                  ->  if no connection holds WAL_READ_LOCK(N>0),
                                      the next writer is free to reset the WAL.

In other words, mxFrame == nBackfill is the permit slip for a reset. And nBackfill also doubles as the checkpointer's progress marker — a note that says "everything up to here has already been carried over, so the next checkpoint can start from this point."

Now come the two most important sentences in this entire post. Quoting SQLite's documentation (walformat.html) directly:

  • nBackfill can only be incremented while holding WAL_CKPT_LOCK.
  • But nBackfill is reset to zero during a WAL reset, and that happens while holding WAL_WRITE_LOCK.

Easy sentences to skim past, but the entire bug is contained in them. There is one field, and the two code paths that touch it take two different locks. The checkpointer holds the CKPT lock while raising nBackfill; the writer doing the reset holds the WRITE lock while dropping it to zero. Neither excludes the other — it's two people entering the same room holding different keys.

There's a reason this design ran fine for 15 years. On the normal path, the moment the checkpointer wants to raise nBackfill and the moment a writer zeroes it belong to different states, and other checks close the gap between them. The problem was one very narrow window: the instant a checkpoint is just starting.

Corruption Happens in Six Steps

SQLite's documentation lays out the scenario in exactly six steps. Let's walk through them in order.

1. Connection A completes a checkpoint.
   It must be a "complete" checkpoint — every byte of the WAL has been
   carried into the DB, and the WAL is left in a state where it can be
   reset.  (i.e. mxFrame == nBackfill)

2. Immediately after, a second checkpoint begins.

3. While checkpoint #2 is "in the process of starting," another
   connection commits a transaction. This commit resets the WAL and
   writes the new content at the very front of the WAL.

4. Data race. Checkpoint #2 has no way of knowing the WAL was reset
   in step 3. So it leaves the wal-index header field with a wrong
   value -- claiming "this part of the WAL has already been
   checkpointed," when it hasn't.

5. Additional transactions commit, and the WAL's frame count grows
   past what it was at the time of checkpoint #1.

6. Later, when a third checkpoint runs, it skips all or part of the
   transaction written in step 3.
   -> That transaction never reaches the database file.
   -> Database corruption.

Step 4 is the moment of the accident. When a reset happens, nBackfill should become 0 — the WAL is being written from scratch, so "frames already carried over" is genuinely zero. But checkpoint #2, which had just started, is still holding the pre-reset picture of the world. The nBackfill it leaves behind asserts "the first N frames have already been handled." But after the reset, those first N frames are completely different data — the transaction freshly written in step 3.

So the checkpointer in step 6 believes those frames are "already done" and skips them. The skipped data never reaches the DB file, and the WAL eventually gets reset again and overwritten on top of it. A transaction that was acknowledged as committed silently evaporates. That's a durability violation, and it's corruption.

Worth noting: the corruption doesn't surface immediately. The wrong value is recorded in step 4, but the actual damage happens in step 6. Step 5 (more commits) has to intervene in between. There is time and there are transactions sitting between the accident and the symptom — exactly the kind of bug that makes postmortems miserable.

The Preconditions — Why Most People Are Fine

The documentation spells out the triggering conditions explicitly:

  • The database must be in WAL mode.
  • Two or more connections must be open on the same file, and those connections must be in different threads or processes.
  • Those two connections must attempt a write or checkpoint at the same instant.

Here's the first honest observation. This condition rules out single-process, single-thread apps entirely — desktop apps, mobile apps, CLI tools, test fixtures. The overwhelming majority of SQLite deployments fall into that bucket.

But fairness demands a second honest observation too. The condition that remains — "WAL mode plus multiple processes writing to one file" — happens to line up exactly with a deployment shape that's grown popular recently: a web app running multiple worker processes sharing a single local SQLite file. In other words, the preconditions for this bug target not SQLite's traditional use cases, but its recently expanding ones.

That's not meant to scare anyone. Meeting the preconditions and actually hitting the bug are two very different things, and that gap is the most interesting part of this whole story.

How Do You Say You "Fixed" an Unreproducible Bug?

This is where the real spectacle of the incident is. In SQLite's own words:

The developers were never able to reproduce this bug organically in the lab.

For this bug to fire, a number of details have to line up at exactly the right moment. In particular, the commit in step 3 has to land precisely inside the narrow "in the process of starting" window of the checkpoint in step 2. That's a nanosecond-scale window. So here's the approach the SQLite team took.

They modified SQLite's own source to embed a callback controlled by sqlite3_test_control(). That callback lets a test program fire the write transaction from step 3 at exactly the right moment while checkpoint #2 is in flight. In other words, instead of waiting for the race, they turned the race into an order they could issue on command. In the documentation's own words, without this code hack, this problem had never once been observed during development and testing.

It's worth pausing on why this approach is the right one. There are exactly two ways to credibly claim you "fixed" a race that only fires probabilistically:

  1. Prove it (model checking, formal verification).
  2. Make the race deterministic, then test it.

SQLite chose option two, and paid the cost of putting a test-only hook into production code to do it. That's not free — it's an explicit tradeoff, expanding the code surface in exchange for verifiability. But the alternative is "we think we fixed it," and "we think" is not an acceptable answer for a data corruption bug.

There's a practical lesson here. If your own systems have probabilistic races, "throw enough load at it and it'll eventually reproduce" is not a strategy. Even SQLite couldn't make that work. Embedding a hook that lets you deterministically steer the schedule is — even if it looks messy — often the only path that actually gets you an answer. (Push that same philosophy to the scale of an entire system, and you get deterministic simulation testing.)

How Dangerous Is It — The Probability, in the Documentation's Own Words

I want to carry this section as close to SQLite's own phrasing as possible. This isn't a place for me to invent a number.

The documentation first acknowledges the severity. This bug is rare but has severe consequences, so application developers should upgrade to a version with the fix.

And then it immediately lowers the temperature. "However, this is not an emergency." The reasoning given is this: based on available telemetry, the rate at which this problem occurs in the field appears to be below the expected rate of SSD malfunctions or cosmic-ray bit flips. So, it writes, even if you are running an unpatched version, you are unlikely to ever encounter this problem unless you are doing something quite unusual.

It's worth reading that comparison correctly. "Below the SSD failure rate" is not "zero." It means this risk is buried in the noise of a hardware-failure probability you are already accepting. If you already have backups and integrity checks in place to guard against SSD failure or bit flips, this bug falls inside that same safety net. If you don't have those safeguards, this bug isn't what you should be worrying about — your SSD is.

And there's a caveat worth leaving in. The documentation qualifies this with "based on available telemetry." What telemetry, and what sample size, is not disclosed. And as we saw above, because the cause and the symptom of this corruption are separated in time, there is a structural possibility that even when it did occur, it was never attributed to "an SQLite bug" and instead got filed under "corruption of unknown cause" or "disk problem." That means this is the kind of estimate that could plausibly be biased toward optimism. That's not a claim that the SQLite team is being dishonest — it's a statement that observing bugs like this one is inherently hard.

Two Messy Weeks — The Withdrawal of 3.52.0

Here's where the story gets a little strange. And this part is easy to misread, so let's be precise about it.

The timeline runs like this:

2026-01-09   3.51.2 released       (the last version with the bug still alive)
2026-03-03   Dan finds and fixes the WAL-reset bug
2026-03-06   3.52.0 released       (includes the WAL-reset fix + a CLI overhaul)
2026-03-13   3.52.0 withdrawn
             The same day, 3.51.3 released (WAL-reset fix backported only)
2026-04-09   3.53.0 released       (re-release of 3.52.0 + additional fixes)
2026-05-05   3.53.1  /  2026-06-03  3.53.2  /  2026-06-26  3.53.3

A major release shipped three days after discovery, and that release was withdrawn a week later.

Important: 3.52.0 was not withdrawn because of the WAL-reset bug. This is exactly the point where the timeline can trick you, so let's be explicit. The reason for the withdrawal was a completely different problem — some of 3.52.0's new features were not 100% compatible with earlier releases, and the feature and its related APIs had to be reworked.

Specifically, the release notes narrow the scope like this: 3.52.0 works fine and is fully backward-compatible unless your database has an expression index or an index on a VIRTUAL generated column. The problem hits when that index's expression evaluates to a floating-point value derived from text or JSONB input. If such an index exists, 3.52.0 could, in rare cases, fail to interoperate correctly with earlier releases.

For what it's worth, that same release's changelog has a separate entry noting that floating-point-to-text conversion was reimplemented, and the default rounding was changed from 15 significant digits (used by every prior version) to 17. It's notable that both entries touch the same territory, but the published documentation does not state that this change caused the compatibility problem above — so I won't assert causation here.

So how did the team respond? This is the part worth learning from. They did not say "wait for 3.53.0." Instead, they shipped a patch release, 3.51.3, on the 3.51 branch. 3.51.3 fixes only the WAL-reset bug and a handful of minor issues found since 3.51.2. No new features.

Unpacking what that choice means: the corruption fix had been bundled together with a CLI overhaul, new SQL syntax, a QRF library, and a floating-point conversion rework. And it was that bundle where the compatibility problem broke out. If 3.52.0 had been the only path forward, users would have been forced into a choice: "swallow an entire feature release with compatibility risk, just to get the corruption fix." Shipping a backport to an older, stable branch removes that forced choice — it decouples the risks, so someone who only wants the corruption fix can take only the corruption fix.

And this wasn't theoretical. The node-sqlite3 repository received an issue on the very day of the withdrawal, 2026-03-13, titled "replace the withdrawn 3.52.0 with the stable 3.51.3" — and the reporter explicitly noted they hadn't been harmed by it, they were doing preventive cleanup. Without the backport, that repository's options would have been considerably worse.

3.53.0 (2026-04-09) is, in the release notes' own words, "a re-release of 3.52.0, with improvements addressing the stale expression index issue" added. And regarding the WAL-reset bug, it notes: "if you haven't hit 3.52.0, this is the first major release carrying this fix," and recommends upgrading.

One more footnote worth adding. The 3.52.0 release notes also included a line saying the team is trying to slow SQLite's release cadence to roughly once every six months — and that this is a goal, not a promise. That the release carrying that sentence got withdrawn a week later is the kind of irony you can't help but sympathize with.

What You Should Actually Do

Boiled down, the action list is short.

1. Check your version. Verify the SQLite version your application is actually linked against — not the version your package manager reports, but the version your runtime reports.

SELECT sqlite_version();

2. Your baseline is 3.51.3 (2026-03-13) or later. Every version at or above 3.7.0 and below that has this bug. If you want to move to a major release, that's 3.53.0 or later; as of this writing (2026-07-16), the latest is 3.53.3 (2026-06-26).

3. If you can't upgrade, backports exist. SQLite's documentation provides backports for a couple of older releases — 3.44.6 and 3.50.7. Note, however, that these ship as Fossil check-ins rather than official release tarballs.

4. And here's the real trap — upgrading once is not the end of the story.

For this last point, let's walk through a real case. OpenAI's codex repository merged a PR in June 2026, titled "pin bundled SQLite to a version with the WAL-reset fix," on 2026-06-14. According to the PR description, here's how the incident happened:

SQLx 0.9 was allowing a wide version range for libsqlite3-sys. As a result, a single lockfile update completely unrelated to this issue rolled libsqlite3-sys back from 0.37.0 to 0.35.0, which silently downgraded the bundled SQLite runtime from 3.51.3 to 3.50.2. In other words, a project that had already reached a fixed version was pushed back onto a vulnerable one by an unrelated dependency cleanup.

This is the price of SQLite being a library. SQLite Is Everywhere covered the upsides of the serverless design; here's the flip side of that coin. Having no server also means there is no central point to patch. With PostgreSQL, upgrade one server and every client is fixed. With SQLite, every binary that links that code has to be fixed individually, and the version is usually not something you chose directly — it's determined transitively, somewhere in your dependency tree. And transitive dependencies move while you're not looking.

Practically speaking: if your project bundles SQLite, pin the version with both an upper and lower bound, and actually assert the result of SELECT sqlite_version() in CI. Don't count on a human catching it by eyeballing a lockfile diff — the codex case is exactly that failure mode playing out.

What This Incident Leaves Behind

A few things are worth pulling out.

First, fifteen years is not proof that code is correct. This bug lived for 15 years inside one of the most heavily tested code paths in SQLite's history, in a project with arguably the most paranoid test suite in the industry. The reason is simple — tests never controlled the scheduler, and this bug lived entirely in the scheduler. 100% coverage does not mean 100% interleaving.

Second, invariants attach to locks, not to fields. This whole incident compresses into one sentence: "the path that increments nBackfill and the path that zeroes it took different locks." Each path, on its own, held its own lock correctly. Each was individually right. What was wrong was that the question "which lock guards this field" had two answers. When you review shared state in your own code, the question to ask isn't "did we take a lock" — it's "does every path that touches this state take the same lock."

Third, this is a model of good disclosure. Look at what the SQLite team did. They built a dedicated documentation section, stated the affected version range precisely, published the six-step mechanism, admitted they couldn't reproduce it, wrote down that they had to hack the source to reproduce it at all, disclosed a rate estimate along with its basis, dialed the temperature down explicitly with "this is not an emergency," and added their own bug as an entry in the corruption-cause list of How To Corrupt An SQLite Database File. That last part is especially good — that document exists mainly to explain "corruption is usually your fault," and they wrote "or possibly ours" into it themselves.

Fourth, don't bundle your risks together. The lesson of 3.52.0 isn't the compatibility bug itself — it's the structure where a corruption fix was bundled with a feature release. The ability to ship a backport to an old branch is not a luxury of release engineering; it's safety equipment.

Closing

The honest conclusion is this: you very likely never hit the WAL-reset bug, and SQLite's own documentation states its occurrence rate is buried in the noise of SSD failure rates. This post wasn't written to alarm you.

But the fix is simple — upgrade to 3.51.3 or later, and assert in CI that the version stays there. It costs almost nothing, and it's exactly what the documentation recommends.

And I think there's a better way to read this whole incident. This isn't a story about SQLite's reliability being overrated. If anything, it's the opposite — a developer found, by reading the code, a race that nobody in 15 years of real-world use had ever triggered organically; embedded a test hook to force it into existence; fixed it in three days; when the release got tangled, shipped a backport to an old branch; and disclosed the entire process in public. "Reliable" doesn't mean bug-free. It means this is how you handle one.

References

Discuss on Twitter • View on GitHub