Use a savepoint for one optional local queue side effect

By Batu · English technical notes

Also published in our Blogger archive.

Roll back an optional audit without losing the claim

The outer transaction contains the job insert. SAVEPOINT surrounds the audit insert; ROLLBACK TO removes that row, RELEASE removes the marker, and outer commit persists the job. The final queries prove claimed job and zero audit rows.

A savepoint cannot undo an external notification or another connection’s committed work. Cross-system side effects need an outbox or another coordination rule.

Example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    connection = sqlite3.connect(Path(directory) / "queue.db")
    connection.execute("CREATE TABLE job(id INTEGER PRIMARY KEY, state TEXT)")
    connection.execute("CREATE TABLE audit(event TEXT NOT NULL)")
    connection.execute("BEGIN")
    connection.execute("INSERT INTO job VALUES (1, 'claimed')")
    connection.execute("SAVEPOINT optional_audit")
    connection.execute("INSERT INTO audit VALUES ('claimed')")
    connection.execute("ROLLBACK TO optional_audit")
    connection.execute("RELEASE optional_audit")
    connection.commit()
    assert connection.execute("SELECT state FROM job").fetchone()[0] == "claimed"
    assert connection.execute("SELECT count(*) FROM audit").fetchone()[0] == 0
    print("job=claimed audit=rolled-back")

Expected stdout:

job=claimed audit=rolled-back

Sources

- SQLite SAVEPOINT

- sqlite3.Connection.execute

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.