Another look at SQLite's WAL-Reset bug

# Another look at SQLite's WAL-Reset bug Source: [https://theconsensus.dev/p/2026/08/23/another-look-at-sqlite-wal-reset.html](https://theconsensus.dev/p/2026/08/23/another-look-at-sqlite-wal-reset.html) On software infrastructure\. ## Another look at SQLite's WAL\-Reset bug A stale read let a SQLite checkpoint discard committed WAL frames for sixteen years\. We reproduce the race with a 100\-line C workload, getting both lost writes and a corrupted database file within seconds using only the public SQLite API\. By Phil EatonAugust 23, 2026[Focus](https://theconsensus.dev/p/2026/08/23/another-look-at-sqlite-wal-reset.html?focus=1) You are getting early access to this article as a subscriber\. Your support makes articles like this possible\. Thank you\. SQLite does physical write\-ahead logging\. It works through edits to database pages in memory and queues every changed page to disk in the write\-ahead log \(WAL\)\. The WAL stores pages as a "frame" \(which is a frame header and then the actual page\) and collapses multiple changes to the same page within a single transaction into one frame\. Once the WAL has reached a threshold size of 1,000 pages \([by default](https://sqlite.org/wal.html?utm_source=theconsensus.dev&utm_medium=referral#ckpt)\), SQLite triggers a "checkpoint"\. Checkpointing copies pages out of the WAL into their permanent location on disk\. But long\-lived readers can keep SQLite from checkpointing\. And conversely, large WALs make readers slower \(when the page cache is invalidated and you must fetch pages from disk\) because pages in the WAL have no useful ordering for reads\. Finding the page you want is a function of the size of the WAL\. In the kindest checkpoint mode, "passive" checkpointing only copies pages out of the WAL not in use by other transactions\. In the most severe mode, "truncate" checkpointing attempts to wait for all readers and writers to complete, then copies all pages out of the WAL and truncates the WAL to zero bytes\. In general, after all pages have been copied out of the WAL the WAL can reset and reuse space\. \(Except for the truncate mode where it will allocate new space from 0 bytes\.\) Otherwise, while pages are still in use by some open transaction, the WAL keeps growing as needed to fit new pages\. The automatic checkpointer only ever does passive checkpointing\. If the application thinks it can do a better job scheduling checkpoints, particularly during times it knows the database is idle, or if the application wants to do more aggressive checkpointing, applications[are allowed](https://sqlite.org/wal.html?utm_source=theconsensus.dev&utm_medium=referral#application_initiated_checkpoints)to do so via methods like`PRAGMA wal\_checkpoint`\. Earlier this month, Tailscale wrote about[hitting a bug](https://tailscale.com/blog/sqlite-wal-reset-bug?utm_source=theconsensus.dev&utm_medium=referral)in concurrent checkpointing that caused the checkpointer to incorrectly mark pages in the WAL as having been copied out already\. This led to data loss at a minimum and occasionally data corruption spotted by`PRAGMA integrity\_check`when data went not just missing but out of sync with indexes\. SQLite[fixed this](https://www.sqlite.org/wal.html?utm_source=theconsensus.dev&utm_medium=referral#the_wal_reset_bug)in March 2026\. \(That Tailscale only just published the blog is probably more a matter of them only now having confidence the bug was actually fixed\.\) Let's trigger this bug organically and see what damage we can do\! ## Tracing the bug[\#](https://theconsensus.dev/p/2026/08/23/another-look-at-sqlite-wal-reset.html#tracing-the-bug) We'll have two threads and three database connections\. Thread 1, database connection A \(the checkpointer\) checkpoints\. Thread 2, database connection B \(the writer\) updates a row in table Z and commits\. Thread 1, database connection C \(the reader\) reads from table Z\. At the right concurrency and with the right timing, these three interleave to create the bug\. The interactions happen entirely in[wal\.c](https://github.com/sqlite/sqlite/blob/version-3.51.2/src/wal.c?utm_source=theconsensus.dev&utm_medium=referral)\. Here's a sample interleaving\. linecheckpointerwriterreader[4359](https://github.com/sqlite/sqlite/blob/version-3.51.2/src/wal.c?utm_source=theconsensus.dev&utm_medium=referral#L4359)`walIndexReadHdr`sees`mxFrame=360`\(window opens\)[4056](https://github.com/sqlite/sqlite/blob/version-3.51.2/src/wal.c?utm_source=theconsensus.dev&utm_medium=referral#L4056)`walRestartLog`:`readLock==0`,`nBackfill\>0`[2146](https://github.com/sqlite/sqlite/blob/version-3.51.2/src/wal.c?utm_source=theconsensus.dev&utm_medium=referral#L2146)`walRestartHdr`:`mxFrame=0`,`salt\+\+`,`nBackfill=0`, read marks clearedcommits 5 new frames[2216](https://github.com/sqlite/sqlite/blob/version-3.51.2/src/wal.c?utm_source=theconsensus.dev&utm_medium=referral#L2216)`nBackfill`\(0\) <`mxFrame`\(360\): stale check passes \(window closes\)[2227](https://github.com/sqlite/sqlite/blob/version-3.51.2/src/wal.c?utm_source=theconsensus.dev&utm_medium=referral#L2227)`mxSafeFrame = 360`[2318](https://github.com/sqlite/sqlite/blob/version-3.51.2/src/wal.c?utm_source=theconsensus.dev&utm_medium=referral#L2318)`nBackfill = 360`\(loses frames\)[3239](https://github.com/sqlite/sqlite/blob/version-3.51.2/src/wal.c?utm_source=theconsensus.dev&utm_medium=referral#L3239)`minFrame = 361`[3571](https://github.com/sqlite/sqlite/blob/version-3.51.2/src/wal.c?utm_source=theconsensus.dev&utm_medium=referral#L3571)skips frames 1\.\.5, the live data The tricky part is this writer restarting the log during the checkpoint's racy window\. Thankfully there's something we can exploit\. wal\.c does a`munmap`of the database file during the window\. And if we cause the database to be particularly large, that`munmap`now takes enough time for the writer to fit in a log restart\. ## Observing the bug[\#](https://theconsensus.dev/p/2026/08/23/another-look-at-sqlite-wal-reset.html#observing-the-bug) In a concurrent thread we'll write a monotonically increasing value \(one value, one row\) to a second "canary" table and only write the next value after we receive a SQLITE\_OK\. SQLITE\_OK means the insert was committed\. While we try to get SQLite to exhibit the bug, we'll check if we ever read back fewer rows than the writer thread says we wrote to the canary table\. If we read back fewer rows than we know we successfully wrote, we've got data loss\. Occasionally data loss will also bring a corrupted data file\. That Tailscale noticed the bug via the integrity check was to some degree luck since this bug does not necessarily involve data corruption, more often just data loss\. And SQLite, even after the fix, does not guard against lost writes\. Here's our workload in full\. About 100 lines of C\. We'll see the bug happen within a few seconds\. ``` #include "sqlite3.h" #include <pthread.h> #include <stdatomic.h> #include <stdio.h> #include <stdlib.h> static long scalar(sqlite3 *db, const char *buf) { sqlite3_stmt *p = 0; long v = -1; if (sqlite3_prepare_v2(db, buf, -1, &p, 0) == SQLITE_OK) { if (sqlite3_step(p) == SQLITE_ROW) v = (long)sqlite3_column_int64(p, 0); sqlite3_finalize(p); } return v; } static sqlite3 *burstDb; static atomic_int burstStop; static long nCommitted; static void *burst(void *arg) { char buf[64]; while (!burstStop) { sqlite3_snprintf(sizeof(buf), buf, "INSERT INTO canary VALUES(%ld)", nCommitted + 1); if (sqlite3_exec(burstDb, buf, 0, 0, 0) == SQLITE_OK) nCommitted++; } return 0; } static sqlite3 *openDb(const char *init) { sqlite3 *db = 0; sqlite3_open("race.db", &db); sqlite3_busy_timeout(db, 5000); sqlite3_exec(db, init, 0, 0, 0); return db; } int main(void) { setvbuf(stdout, NULL, _IONBF, 0); // Large mmap_size for a large munmap. Large database itself so // we actually get a large mmap/munmap. sqlite3 *db = openDb("PRAGMA journal_mode=wal; PRAGMA mmap_size=1073741824;" "CREATE TABLE t1(a INTEGER PRIMARY KEY, b);" "CREATE TABLE canary(a INTEGER PRIMARY KEY);" "WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<65536)" " INSERT INTO t1 SELECT NULL, randomblob(3900) FROM s;" "PRAGMA wal_checkpoint(TRUNCATE)"); sqlite3 *helper = openDb(""); burstDb = openDb(""); for (int i = 0; i < atoi(getenv("ATTEMPTS") ?: "200"); i++) { pthread_t t; // 1. mmap in big table, cache stale shmem values. Must run before 2. scalar(db, "SELECT count(*) FROM t1 WHERE b IS NOT NULL"); // 2. Makes sure `isChanged` is true (lets us enter munmap path). sqlite3_exec(helper, "UPDATE t1 SET b=randomblob(3900) WHERE a<=20", 0, 0, 0); // 3. Try to fully backfill the WAL. Must run on helper not db, so db values stay stale. for (int i = 0; i < 50; i++) { int nLog, nCkpt; sqlite3_wal_checkpoint_v2(helper, "main", SQLITE_CHECKPOINT_PASSIVE, &nLog, &nCkpt); if (nCkpt >= nLog) break; } // 4. Commit during checkpoint in unrelated thread/connection. burstStop = 0; pthread_create(&t, 0, burst, 0); sqlite3_exec(db, "PRAGMA wal_checkpoint", 0, 0, 0); burstStop = 1; pthread_join(t, 0); if (scalar(helper, "SELECT count(*) FROM canary") < nCommitted) break; /* Lost a write. */ } sqlite3_exec(helper, "PRAGMA wal_checkpoint(TRUNCATE)", 0, 0, 0); /* No pages in WAL now. Still in the database or have we lost writes? */ long recovered = scalar(helper, "SELECT count(*) FROM canary"); if (recovered < 0) { fprintf(stderr, "database unreadable: %s\n", sqlite3_errmsg(helper)); return 1; } printf("permanently lost: %ld transactions\n", nCommitted - recovered); printf("integrity_check: %s\n", scalar(helper, "SELECT integrity_check='ok' FROM pragma_integrity_check()") ? "ok" : "failed"); return nCommitted != recovered; } ``` walrace\.c Grab`clang`and`unzip`and the buggy and fixed SQLite amalgamation\. ``` sudo apt-update -y sudo apt-get install -y unzip clang curl -O https://sqlite.org/2026/sqlite-amalgamation-3510200.zip curl -O https://sqlite.org/2026/sqlite-amalgamation-3530000.zip unzip -q sqlite-amalgamation-3510200.zip unzip -q sqlite-amalgamation-3530000.zip ``` And build both versions\. ``` cc -O2 -o walrace-present walrace.c sqlite-amalgamation-3510200/sqlite3.c \ -I sqlite-amalgamation-3510200 -lpthread -lm cc -O2 -o walrace-absent walrace.c sqlite-amalgamation-3530000/sqlite3.c \ -I sqlite-amalgamation-3530000 -lpthread -lm ``` The code doesn't delete the database files for you, so make sure you delete them before running\. And you'll see variations like this\. ``` $ rm -f race.db* && time ./walrace-present database unreadable: database disk image is malformed real 0m4.286s user 0m2.414s sys 0m1.756s $ rm -f race.db* && time ./walrace-present permanently lost: 1 transactions integrity_check: ok real 0m3.454s user 0m2.042s sys 0m1.302s ``` The code above stops looping after the first lost transaction\. If instead you had it loop for 30 seconds, you'd see many more lost transactions\. Separately, try out the version built against SQLite 3\.53\.0 \(where the bug is fixed\) and you'll stop seeing these lost writes and corrupted data files\. ``` $ rm -f race.db* && time ./walrace-absent permanently lost: 0 transactions integrity_check: ok real 0m15.946s user 0m10.814s sys 0m5.037s ``` And while testing out variations on the workload I noticed one other thing\. Build the*fixed*amalgamation with SQLite's debug mode on and run the workload again\. ``` cc -O2 -o walrace-absent walrace.c sqlite-amalgamation-3530000/sqlite3.c \ -I sqlite-amalgamation-3530000 -lpthread -lm -DSQLITE_DEBUG ``` Bump up our loop to give it more tries to run\. ``` $ rm -f race.db* && time ATTEMPTS=4000 ./walrace-absent walrace-absent: sqlite-amalgamation-3530000/sqlite3.c:69238: void walMerge(const u32 *, ht_slot *, int, ht_slot **, int *, ht_slot *): Assertion `iRight>=nRight || aContent[aRight[iRight]]>dbpage' failed. Aborted (core dumped) real 2m24.642s user 1m46.527s sys 0m38.251s ``` And hey, we probably shouldn't be hitting that\. However, there is not an obvious bug here, just the assertion failure, which maybe should be updated\. ## Thread sanitizer[\#](https://theconsensus.dev/p/2026/08/23/another-look-at-sqlite-wal-reset.html#thread-sanitizer) Another interesting thing is if we turn on thread sanitizer\. \(This section works most reliably on macOS, not Linux\.\) ``` cc -O1 -g -fno-inline -fsanitize=thread -o walrace-present-tsan walrace.c \ sqlite-amalgamation-3510200/sqlite3.c -I sqlite-amalgamation-3510200 -lpthread -lm ``` And bump the loop size and allow the workload to keep losing transactions\. ``` $ rm -f race.db* && TSAN_OPTIONS="halt_on_error=0 history_size=7" ./walrace-present-tsan 2> tsan.txt permanently lost: 1 transactions integrity_check: ok [1] 24066 abort TSAN_OPTIONS="halt_on_error=0 history_size=7" ./walrace-present-tsan 2> ``` And tie thread sanitizer back to source code lines\. ``` $ awk -v A=sqlite-amalgamation-3510200/sqlite3.c '/#0 /&&match($0,/sqlite3\.c:[0-9]+/){n=substr($0,RSTART+10,RLENGTH-10);c="sed -n "n"p "A;c|getline s;close(c);sub(/^ +/,"",s);print " "$2" "n": "s;next}{if(/^ (Read|Write|Previous|Atomic)/)print " "$0}' tsan.txt ... omitted ... Read of size 4 at 0x0001009f8060 by main thread (mutexes: write M0): walCheckpoint 68972: if( pInfo->nBackfill<pWal->hdr.mxFrame ){ Previous atomic write of size 4 at 0x0001009f8060 by thread T26 (mutexes: write M1): walRestartHdr 68911: AtomicStore(&pInfo->nBackfill, 0); ... omitted ... ``` It kind of looks like even thread sanitizer caught this bug? Interestingly a line related to the assertion failure above also showed up in thread sanitizer\. Thread sanitizer sounds useful\! ## Parting thoughts[\#](https://theconsensus.dev/p/2026/08/23/another-look-at-sqlite-wal-reset.html#parting-thoughts) We've got a cheap reproduction of the bug that involved no changes to source code, no shims, etc\. I wanted to show you can get both data loss \(not warned against\) and data corruption \(warned against if you run the integrity check\)\. I spent a while longer looking for more bugs in the area and did not find any\. The bug still seems rare\. But there's a fix and better to upgrade than not\. And more general lost write protection might be good\. Noticed a mistake? Have a question or comment? Write to[the editor](https://theconsensus.dev/cdn-cgi/l/email-protection#215149484d61554944424e4f52444f5254520f454457)\.