SQLite repairs rollback through an in-memory journal

A rollback implementation has to remain usable after it has restored earlier state. Applications can continue the surrounding transaction, make further changes, and roll back again. SQLite 3.37.2 addresses a defect in that sequence. Its release notes describe possible database corruption when a savepoint rollback occurs with PRAGMA temp_store=MEMORY, further changes follow, and the outer transaction commits. The defect dates to version 3.35.0, released on March 12, 2021. The patch arrives today, January 6, 2022.

I value SQLite because its operational requirements remain proportional to a database embedded in an application. That makes a defect in transaction recovery especially consequential. Recovery is part of the engine contract that application code should be able to rely on without implementing its own page-management logic. This release concerns that contract, even though the triggering sequence is unusual.

The assertion was evidence of a real defect

The starting point was Jingzhou Fu's January 1 report, which supplied three SQL test cases against a debug build of 3.37.1. Richard Hipp subsequently distinguished their consequences. One concerned a faulty assertion harmless to production builds. Another exposed a minor bug. The third could corrupt a database if the transaction continued to commit after a savepoint rollback.

That distinction matters when interpreting a crash report from an assertion-enabled binary. An assertion failure identifies an internal expectation that no longer holds. Its presence alone does not establish whether a production build loses data. The report provided an executable sequence and a backtrace through savepoint playback, allowing the developers to investigate the state restoration itself. Hipp identified the third case as a corruption risk, so dismissing the report as debug-only would have missed its consequence.

The repair check-in, by Dan on January 1, describes an in-memory journal problem associated with releasing savepoints and a subsequent rollback. It changes the memory journal and pager implementations and adds a focused regression test. This provides a more precise account of the repair than treating every statement in the original reproducer as an independent cause.

Journal storage has state of its own

The memory journal implementation in the released source represents its contents as a linked list of chunks. A file position contains a byte offset and a pointer to a particular chunk. The journal object keeps an end position and a read position. Those fields let code treat the structure as a file without requiring every journal access to go through an actual disk file.

The source comments explain that memory journals also hold small statement journals. Such journals can avoid temporary file creation and file I/O, then spill to disk if their configured threshold is exceeded. A journal that supports this transition retains the underlying virtual filesystem and file name. Memory storage is therefore an implementation choice within the journaling system, with its own bookkeeping requirements.

Truncation illustrates the difficulty. Removing the end of a journal can free chunks that a previously cached read position referenced. In the released implementation, memjrnlTruncate finds the retained endpoint, frees the following chunks, updates the endpoint offset, and clears both fields of the cached read position. Subsequent reads must locate their position against the remaining contents. Keeping a pointer into discarded storage would make the apparent file position inconsistent with the memory that still exists.

The read routine also checks whether a requested range exceeds the journal endpoint. Its result in that case is a short-read error. For a read within the retained range, it can walk the chunk list or reuse a valid cached read position. These checks operate below SQL syntax. An application can issue valid transactional statements while exposing a defect in the machinery that implements their meaning.

The regression test repeats the rollback

The new memory-journal regression test starts with PRAGMA journal_mode = memory and creates a table with an integer primary key and a unique second column. It inserts 2,000 rows containing 700-byte random blobs inside a transaction. Its repeated nested savepoints exercise journal truncation and reuse across different amounts of changed data.

The central sequence below is shortened from that test. It assumes the table and initial rows already exist, and uses a fixed update bound to show the nesting clearly.

SAVEPOINT one;
UPDATE t1 SET b = randomblob(700) WHERE a <= 250;
SAVEPOINT two;
UPDATE t1 SET b = randomblob(700) WHERE a = 1;
ROLLBACK TO two;
RELEASE two;
SAVEPOINT two;
UPDATE t1 SET b = randomblob(700) WHERE a = 1;
ROLLBACK TO two;
RELEASE two;
PRAGMA integrity_check;
ROLLBACK TO one;
RELEASE one;

The full test varies the outer update bound from 200 through 300. It checks that integrity_check returns ok before rolling back the outer savepoint. Repeating the inner savepoint matters because an implementation can restore state successfully once while leaving internal positions unsuitable for the next attempt. Varying the number of changed rows exercises different journal sizes with the same logical transaction structure.

This example describes the project's regression coverage. It is not a claim that every execution of this shortened SQL corrupts every affected build. The release notes identify the supported application's corruption conditions, while the test isolates memory-journal behavior for the repair. In particular, journal_mode=memory in the test and temp_store=MEMORY in the release description are different settings. Conflating them would obscure which configuration an application actually uses.

A second repair concerns cached cascade programs

The release also fixes cached bytecode for foreign-key cascades after local schema changes. The January 2 check-in clears the cache of triggers that implement CASCADE foreign-key actions whenever the schema changes. Its stated scope includes schema-dependent internal programs whose assumptions can become stale.

This is a separate defect, but it raises a related implementation concern. An engine retains derived state to avoid repeating work. A journal read position derives from stored chunks, while a cascade program derives from a schema. Changes to either underlying structure require the corresponding cached state to remain valid or be discarded. The SQL statement that exposes stale state need not be the statement that first made it stale.

I consider this a substantive maintenance release because it repairs behavior beneath ordinary transaction and schema operations. The memory-journal test is particularly useful evidence. Its repeated rollback sequence checks that restoration leaves the engine ready for subsequent work, which is exactly the property an application needs when it recovers one part of a transaction and continues the rest.