Use SAVEPOINT to keep an earlier in-memory change

By Batu · English technical notes

Also published in our primary archive.

A savepoint marks a position within an existing transaction. This in-memory example inserts keep-me first, then creates SAVEPOINT optional_step. The later discard-me insert represents work that the program decides not to keep. ROLLBACK TO optional_step restores the connection's database state to the point immediately after the savepoint was made, leaving the earlier row intact. RELEASE optional_step removes the savepoint before the outer context manager commits.

The final query asks for names in id order and the assertion verifies that only keep-me survives. This is a state check inside one connection, not a demonstration of nested independent commits. In particular, releasing an inner savepoint does not make its work durable separately from an enclosing transaction; an outer rollback can still undo it.

SAVEPOINT, ROLLBACK TO, and RELEASE are SQLite SQL features, used here through Python's standard-library sqlite3 module. The code requires Python 3.6 or later because its output uses an f-string, but no newer sqlite3 API. Avoid interpolating dynamic savepoint names into SQL without validating them, because parameters bind values rather than SQL identifiers.

AI-assistance disclosure: this article was drafted with AI assistance and should be adapted to an application's error-handling requirements.

Sources: SQLite savepoints, Python sqlite3 transaction control, and Python formatted string literals.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE note(id INTEGER PRIMARY KEY, body TEXT NOT NULL)")

with con:
    con.execute("INSERT INTO note(body) VALUES (?)", ("keep-me",))
    con.execute("SAVEPOINT optional_step")
    con.execute("INSERT INTO note(body) VALUES (?)", ("discard-me",))
    con.execute("ROLLBACK TO optional_step")
    con.execute("RELEASE optional_step")

rows = con.execute("SELECT body FROM note ORDER BY id").fetchall()
assert rows == [("keep-me",)]
print(f"saved rows: {rows}")

con.close()

saved rows: [('keep-me',)]