Pylerno
English

Design a transaction and test its rollback

TaskFlow saves its single-user JSON state in one replacement operation. Suppose the next version stores a task and its history in separate SQLite tables. Marking a task complete now needs two writes. A transaction lets you treat those writes as one change.

On this page

State the rule before writing SQL

A successful completion must change tasks.done to 1 and append one history event. A failure in either write must leave both tables as they were. Give each event a unique ID so an accidental reuse produces a visible constraint error.

Build a disposable database

Save this first block as transaction_demo.py and append the next two Python blocks in order. Use local Python 3.12 or newer. :memory: creates a database owned by this connection. The with db block commits on success or rolls back on an exception when a transaction is active.

python
import sqlite3

db = sqlite3.connect(":memory:", autocommit=False)
with db:
    db.execute("CREATE TABLE tasks (id INTEGER PRIMARY KEY, done INTEGER NOT NULL)")
    db.execute("CREATE TABLE history (event_id TEXT PRIMARY KEY, task_id INTEGER NOT NULL)")
    db.execute("INSERT INTO tasks VALUES (1, 0)")
    db.execute("INSERT INTO history VALUES (?, ?)", ("created-1", 1))

Keep both writes inside one boundary

The function starts with a conditional update. Checking rowcount makes a missing or already-complete task an error. Parameters pass values separately from the SQL text. Let failures reach the context manager so it can roll back the entire change.

python
def complete(connection, task_id, event_id):
    with connection:
        changed = connection.execute(
            "UPDATE tasks SET done = 1 WHERE id = ? AND done = 0",
            (task_id,),
        )
        if changed.rowcount != 1:
            raise ValueError("expected one open task")
        connection.execute(
            "INSERT INTO history VALUES (?, ?)",
            (event_id, task_id),
        )

Force the second write to fail

Reuse the existing event ID deliberately. SQLite accepts the update, then rejects the history insert. Catch the error outside the function and read both tables again. Finally try a fresh ID so the same business operation succeeds.

python
try:
    complete(db, 1, "created-1")
except sqlite3.IntegrityError:
    pass
else:
    raise AssertionError("expected duplicate event to fail")

assert db.execute("SELECT done FROM tasks WHERE id = 1").fetchone() == (0,)
assert db.execute("SELECT COUNT(*) FROM history").fetchone() == (1,)
complete(db, 1, "completed-1")
assert db.execute("SELECT done FROM tasks WHERE id = 1").fetchone() == (1,)
assert db.execute("SELECT COUNT(*) FROM history").fetchone() == (2,)
print("Rollback and commit verified")
db.close()

Run python transaction_demo.py. You should see Rollback and commit verified. For an exercise, call complete with an unknown task ID before closing the connection. Verify the function raises ValueError and the history count stays at two. This checks failure before the insert as well as failure during it.

Check your understanding

Which operation cancels uncommitted changes?

Choose one answer

Search documentation

Share this page