A Checkpoint in the Middle of an INSERT

I recently helped fix an issue in Turso, an in-process, SQLite-compatible database written in Rust. An INSERT could panic after a checkpoint ran on the same connection.

In write-ahead log (WAL) mode, SQLite records changed database pages in a separate log. A checkpoint later copies committed page versions from the log into the main database file.

You didn’t need two threads to trigger it. The INSERT paused for I/O, the caller ran a checkpoint, and the INSERT panicked when it resumed. The checkpoint had cleared state the unfinished INSERT still needed.

INSERT starts → pauses for I/O
                  ↓
           checkpoint runs
                  ↓
INSERT resumes → saved position is gone → panic

Pausing a statement

When an INSERT needs to wait for I/O, Turso keeps track of where it stopped and the state it needs to continue. With sequential execute() calls, the INSERT finishes before the next query starts, so this overlap doesn't happen.

Turso's lower-level Rust API exposes this through Statement::step(). Returning StepResult::IO means the statement is waiting for I/O, not finished. The caller can then run another operation on the same connection.

With some setup and I/O handling omitted, the reproducer looks like this:

let mut insert = conn.prepare("INSERT INTO t VALUES (100, zeroblob(50000))")?;
if matches!(insert.step()?, StepResult::IO) {
    let _ = conn.prepare("PRAGMA wal_checkpoint(PASSIVE)")?.run_collect_rows();
    insert.run_collect_rows()?; // Resume the INSERT.
}

Here, run_collect_rows() runs a statement until it finishes.

Why did the checkpoint affect the INSERT?

The connection also caches pages in memory.

Turso uses an internal B-tree cursor to remember its current page, its position within that page, and the path it followed to get there. That path holds references to cached pages.

The pager manages the cache and keeps track of cursors. In the affected version, explicit checkpoints finished with these steps, shown here in simplified form:

self.invalidate_all_cursors();
self.page_cache.write().clear(false)?;

The first call cleared the INSERT's saved path and set current_page to -1. Its stored write phase remained unchanged. On the next step(), the INSERT continued that write and tried to access its current page:

fn current(&self) -> usize {
    turso_assert_greater_than_or_equal!(self.current_page, 0);
    self.current_page as usize
}

With current_page now -1, the assertion failed.

The fix rejects an explicit checkpoint while another statement is unfinished, before the checkpoint can invalidate that statement's cursor.

Why reject the overlap?

Automatic checkpoints ran as part of Turso's commit process. They used PASSIVE mode and skipped cursor invalidation and cache clearing.

An explicit PASSIVE checkpoint could also retain cached pages: copying committed data into the database file does not itself make those cached contents stale.

PR #8032 kept the existing explicit-checkpoint cleanup and prevented explicit checkpoints from overlapping unfinished statements.

Statements on one Turso connection share a pager and its transaction state. An INSERT reads existing B-tree pages to find where to add a row. Its read transaction determines which committed versions of those pages it can use.

Two explicit checkpoint modes also affect that transaction:

PRAGMA wal_checkpoint(RESTART);
PRAGMA wal_checkpoint(TRUNCATE);

RESTART makes the WAL reusable from its beginning. TRUNCATE also shrinks the WAL file to zero bytes after its committed changes have been copied into the database file.

Once a SQL RESTART or TRUNCATE checkpoint passes its startup checks, it releases any read transaction the connection still holds. It releases the read lock so the connection itself won't prevent WAL reuse.

If overlap were allowed, that could end the read transaction used by the paused INSERT. Keeping cached pages would not preserve that transaction.

This is a separate concern from the original PASSIVE-checkpoint panic. Before allowing this overlap, we would also need to verify that the INSERT can safely continue after the checkpoint releases its read transaction.

Supporting overlap would require preserving each unfinished operation's pages and transaction state through I/O pauses, errors, and cancellation.

SQLite's sqlite3BtreeCheckpoint() returns SQLITE_LOCKED when the relevant B-tree has an open read or write transaction. This can include a SELECT paused between rows.

Checkpointing on another connection remains possible within the WAL's locking rules. That connection has its own pager, so it does not clear the INSERT's cursor or end its read transaction.

Keeping protection across the pause

Turso already prevented two write statements from overlapping, but PRAGMA wal_checkpoint did not count as a write.

Classifying it as a write would prevent overlap with an INSERT. But it could still overlap a SELECT paused between rows. That SELECT also needs its saved B-tree position.

Whether requested through SQL or the connection API, a checkpoint cannot start while another statement is unfinished.

That blocks a checkpoint when the INSERT starts first. Now reverse the order:

Checkpoint checks for active statements → none
Checkpoint starts                      → pauses for I/O
INSERT attempts to start               → the earlier check cannot stop it

A flag marks the checkpoint as active and prevents new statements from starting until it ends.

Both startup checks use the same mutex. Their logic, in pseudocode, is:

start_statement:
    with activity locked:
        if checkpoint_active:
            reject
        register this statement as active

start_checkpoint:
    with activity locked:
        if checkpoint_active or another_statement_is_active:
            reject
        checkpoint_active = true

Checking and updating happen under the same lock. A new statement cannot start between the checkpoint checking for other active statements and setting its flag.

The mutex is then released, but the flag stays set during I/O.

An ExplicitCheckpointGuard keeps references to the shared flag and the pager while the checkpoint is paused:

pub(crate) struct StatementActivity {
    explicit_checkpoint_active: bool,
}

pub(crate) struct ExplicitCheckpointGuard {
    activity: Arc<Mutex<StatementActivity>>,
    pager: Arc<Pager>,
}

For a SQL checkpoint, this guard is stored in the statement's execution state. Returning from step() to wait for I/O leaves it there. A guard stored only in a local variable would be dropped on that return.

When the checkpoint finishes, fails, or its statement is reset or dropped, the stored guard is dropped too. Rust automatically runs its cleanup code, shown here with an internal assertion omitted:

impl Drop for ExplicitCheckpointGuard {
    fn drop(&mut self) {
        if self.pager.is_checkpointing() {
            self.pager.cleanup_after_checkpoint_failure();
        }
        let mut activity = self.activity.lock();
        activity.explicit_checkpoint_active = false;
    }
}

Cleanup happens before the flag is cleared. Another statement cannot start while unfinished checkpoint work is still being cleaned up, even if the paused checkpoint never resumes.

In the original reproducer, the checkpoint now returns StatementsInProgress. The suspended INSERT resumes to completion, and the database passes its integrity check.

Returning for I/O does not mean an operation is finished. At each yield point, ask what another caller could change or invalidate before this operation resumes.

Check the reverse order too: start the other operation first. Then test cancellation: reset or drop the paused operation instead of resuming it.

The protection must last until the saved state is no longer needed and unfinished work has been cleaned up, even if the operation never resumes.

Related changes

• Prevent explicit checkpoints and root statements on the same connection #8032