Python SQLite savepoints: discard one substep without committing the batch

Operation contract

The ledger starts an explicit transaction, writes receipt 41, then opens a named savepoint for a second import step. A duplicate primary key fails inside that step after receipt 42 was inserted. ROLLBACK TO removes the step’s changes, and RELEASE removes its savepoint marker. Receipt 43 is then accepted and the outer transaction commits only receipts 41 and 43. A second transaction releases a successful inner step and subsequently rolls back its outer transaction, proving that inner release did not commit independently.

Failure and ownership boundary

ROLLBACK TO leaves the named savepoint active; RELEASE is a separate operation. Use constant owned names or a controlled naming mechanism, not received SQL fragments. A failed cleanup or commit needs a caller policy. Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite writer contention: reject a busy transaction before changing state and Django atomic transactions: rollback the batch and defer callbacks describe related boundaries.

Working program

import sqlite3

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    connection.execute("CREATE TABLE receipt(id INTEGER PRIMARY KEY)")
    connection.execute("BEGIN")
    connection.execute("INSERT INTO receipt VALUES (41)")
    connection.execute("SAVEPOINT import_step")
    try:
        connection.execute("INSERT INTO receipt VALUES (42)")
        connection.execute("INSERT INTO receipt VALUES (41)")
    except sqlite3.IntegrityError:
        connection.execute("ROLLBACK TO import_step")
    connection.execute("RELEASE import_step")
    connection.execute("INSERT INTO receipt VALUES (43)")
    connection.execute("COMMIT")
    print("committed:", connection.execute("SELECT id FROM receipt ORDER BY id").fetchall())
    connection.execute("BEGIN")
    connection.execute("SAVEPOINT accepted_step")
    connection.execute("INSERT INTO receipt VALUES (44)")
    connection.execute("RELEASE accepted_step")
    connection.execute("ROLLBACK")
    print("after outer rollback:", connection.execute("SELECT COUNT(*) FROM receipt").fetchone()[0])
finally:
    connection.close()

Output

committed: [(41,), (43,)]
after outer rollback: 2

Costs and limits

Transaction costs depend on changed pages, indexes and storage policy. This owned in-memory database establishes savepoint behavior, not disk durability, multi-engine isolation or production throughput. Nested markers do not create independent committed database copies.

Common Mistakes

• Release inside an outer transaction does not make the inner writes durable.
• ROLLBACK TO does not remove its savepoint marker.

Connected lessons

Python sqlite3 transactions: bind values and roll back failed batches, Python SQLite writer contention: reject a busy transaction before changing state, Django atomic transactions: rollback the batch and defer callbacks.