SQLite repairs WAL checksums after savepoint rollback

A database can pass an integrity check and still be missing a committed row. The report behind SQLite's June WAL fix demonstrates that distinction. Its restored copy has a structurally valid database with two rows where the test expects three. The missing row belongs to a transaction after a savepoint rollback, and the defect involves an invalid frame in the write-ahead log.

SQLite 3.50.2, released on 28 June, prevents writing WAL frames without checksums in this situation. The precondition is more specific than ordinary savepoint use. Dirty pages must already have spilled into the WAL before the savepoint rolls back. The report traces the problem to interactions with rewriting WAL frames.

I have a high regard for SQLite's testing discipline, and this is a useful case for understanding what a test actually proves. A structural consistency result and an assertion about committed application data are different checks. The reproducer needs both to distinguish a readable database from the expected recovered state.

Spilling is not committing

SQLite's WAL description explains that changes go into a separate log while readers retain a view bounded by a valid commit. A log can contain work that is not yet committed. The physical presence of a page in the WAL therefore does not, by itself, mean that a transaction containing that page has completed.

This distinction lets a transaction outgrow the immediately available cache. Dirty pages can leave memory before the transaction's final outcome is known. Rollback must then reconcile the state already written with the logical state to which the transaction returns. The June fix concerns that reconciliation after a named savepoint.

The reporter made spilling likely by setting PRAGMA cache_size = 1. The test also uses WAL mode, full auto-vacuum and page-sized blob values. Those are facts about the reproducer, not a complete statement of every configuration capable of encountering the defect. Their purpose is to force a sequence of page writes that would otherwise be harder to arrange in a small test.

The reported sequence first commits two rows. It then opens a savepoint and inserts several more rows, causing pages to spill. One later insertion causes a WAL frame to be rewritten. Rolling back and releasing the savepoint returns to the earlier logical state, after which another insertion should produce the third committed row.

A malformed frame changes recovery's view

The test comments identify a commit frame with a zero checksum after rollback. They also describe checksum state coming from a later frame while an earlier frame rewrite is skipped. The result is not simply a wrong SELECT inside the writer connection. The failure appears when the test copies the database and its WAL and opens that copied pair as a new database.

That fresh open is essential to the observation. The restored database recovers two rows, losing the insertion performed after the rollback. Its PRAGMA integrity_check still reports success. The database that recovery exposes can be internally coherent while failing the application's expected history of commits.

This explains why the release description emphasizes frames and checksums. Recovery needs a valid log representation to identify the durable history. A transaction can have an intended SQL outcome while the physical record needed to recover that outcome is wrong. The fix has to preserve the WAL's invariants through rollback and subsequent writes.

The report does not justify treating every use of savepoints as corrupting data. It identifies a particular sequence involving spilled and rewritten frames. It also does not justify an application workaround that deletes a WAL file. The WAL contains changes required to reconstruct the current database, so discarding it would remove the very information recovery needs.

A savepoint does not end the surrounding transaction

The savepoint documentation distinguishes ROLLBACK TO from a plain ROLLBACK. Returning to a named savepoint leaves a transaction active. The named savepoint remains, while intervening savepoints are canceled. Releasing an inner savepoint removes it from the stack without committing the surrounding transaction to disk.

A small example illustrates the SQL contract independently of the reported low-level failure. This is an example of expected savepoint behavior, not a replacement for the reporter's carefully arranged reproducer.

CREATE TABLE entries(id INTEGER PRIMARY KEY);
BEGIN;
INSERT INTO entries VALUES(1);
SAVEPOINT optional_part;
INSERT INTO entries VALUES(2);
ROLLBACK TO optional_part;
RELEASE optional_part;
INSERT INTO entries VALUES(3);
COMMIT;
SELECT id FROM entries ORDER BY id;

The result contains identifiers 1 and 3. Rolling back the optional part removes identifier 2 without preventing the later insertion. An engine implementation must retain that meaning even if pages left memory during the optional work. Cache pressure changes the physical execution, not the transaction contract.

In the reported bug, the later insertion makes the problem visible after recovery. That ordering is why a test that stops immediately after the rollback can miss the relevant failure. The expected state must include what the transaction machinery does after undoing the savepoint's work.

The testing discussion explains the gap

Richard Hipp's response dates the problem to the WAL frame-overwrite optimization introduced in 2016 and first released in 3.11.0. The reporter reproduced it against 3.44.0 and 3.50.1. This makes it an older implementation defect repaired by the June patch, not evidence that the May feature release first created it.

The thread also distinguishes two forms of testing. Hipp explains that dbsqlfuzz combines SQL with database inputs that can already be corrupt. It can enable operations that permit corruption, so finding a corrupt database at the end is not automatically a failure condition for that fuzzer. The final-state assertion must match what the test permits at the start.

TH3's recovery testing takes filesystem snapshots during transactions and checks whether recovery yields a complete commit or rollback. It randomizes aspects of the simulated filesystem outcome. According to the response, the SQL scripts used for those tests did not include the sequence that exposed this defect. Randomizing one dimension of a test does not supply every missing combination in another dimension.

I find that explanation more useful than a general claim about exhaustive testing. It identifies a missing transaction sequence and explains why existing fuzzing had a different objective. The regression can now assert the recovered row count as well as structural integrity, using the small-cache workload to force the relevant WAL behavior.

The June release also repairs unrelated SQL and extension defects. They do not explain this recovery issue, and the WAL fix should retain its own identity. Its concrete result is that savepoint rollback after dirty-page spilling no longer produces the checksum-less frames described in the report, allowing the later committed insertion to survive the copied database's recovery path.