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.

It has nothing to do with multithreading. When the INSERT query paused when doing I/O, the client could run a checkpoint, and the INSERT would panic when it resumed. The checkpoint had cleared the state the unfinished INSERT still needed.

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

Pausing a statement

To manage INSERT's state, Turso keeps track of where it stopped and preserves the state it needs to continue. The behaviour is correct if you run sequential execute() calls because nothing slips through in between. However, when using prepare() and step() the caller regains control when step() returns StepResult::IO, which in Turso's lower-level API means the statement is waiting for IO. In the meantime, nothing prevents you from running another operation.

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 loads and caches database pages and keeps track of cursors. Explicit checkpoints finish 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 has started executing but hasn't finished, before the checkpoint can invalidate that statement's cursor.

Why reject the overlap?

Automatic checkpoints ran as part of Turso's commit process. They use PASSIVE mode and skip cursor invalidation and whole cache clearing. An explicit PASSIVE could skip cursor invalidation and cache clearing too, but that alone wouldn't make the overlap safe.

The fix didn't change how and when Turso cleaned up its cached pages and query cursors, it prevented explicit checkpoints from overlapping unfinished statements.

Statements on one Turso connection share a pager and its transaction state. Before adding a row, an INSERT reads the existing database pages. It may stop to wait for I/O, but it still needs those pages and its read transaction. In the affected version, a checkpoint on the same connection could clear state the INSERT was still using. The INSERT would then resume and try to use it.

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.

Before resetting the WAL, a RESTART or TRUNCATE checkpoint ends the connection's read transaction to release its read lock, but a paused INSERT still needs that transaction to finish. Therefore, at the start of a new checkpoint, Turso checks for unfinished statements on the same connection and rejects the initiated operation if it finds one.

If overlap were allowed, that could end the read transaction used by the paused INSERT.

To allow the overlap, you can't drop the INSERT's pages. You have to keep its read transaction active, avoid releasing locks the INSERT requires, and test every point where the INSERT can pause for I/O. I'm not sure it's worth it.

SQLite's sqlite3BtreeCheckpoint() returns SQLITE_LOCKED when the relevant B-tree has an open read or write transaction.

Checkpointing on another connection is still possible. You can still run a checkpoint on another connection. It has its own pager, so the INSERT keeps its cursor and read transaction.

Keeping protection across the pause

Turso already prevented overlap between two write statements, but PRAGMA wal_checkpoint did not fall under the write category. Also, classifying it as a write still wouldn't achieve correct behaviour because it would allow overlap with a paused SELECT.

Rejecting checkpoints while another statement is unfinished handles the INSERT starting 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, so it survives I/O pauses.

When the checkpoint finishes, fails, or its statement is reset or dropped, the guard is dropped and runs this cleanup (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 INSERT now finishes without panicking, and the integrity check passes. Returning for I/O doesn’t mean the operation is finished.

Related changes

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