An agent adapter inserts a job row, runs a short SQL script to record the result, then encounters an error. The exception handler calls rollback(). The adapter reports failure and assumes the database is unchanged.

With Python’s sqlite3.executescript() in legacy transaction-control mode, that assumption can be wrong. The method can commit the pending job row before it starts the script. A later rollback cannot reach back across that commit.

I would require a failure-path test that reopens the database before accepting this adapter. Seeing rollback() in the handler tells the reviewer what the programmer intended. Reading the saved rows tells them what happened.

The example below is an offline fixture, not a reported production incident or a Claude Code defect. It uses two local tables to represent an application-owned agent ledger. It makes no model calls, runs no generated SQL and does not contact a remote service.

The helper changes the transaction boundary

Python’s executescript() reference documents the condition: under LEGACY_TRANSACTION_CONTROL, an existing pending transaction is implicitly committed first. The method performs no other implicit transaction control; transaction statements needed by the script must be in the script itself.

Python added the autocommit connection attribute in 3.12. This fixture explicitly selects LEGACY_TRANSACTION_CONTROL when that constant exists. On earlier Python versions it uses the older isolation_level interface. That choice makes the failure reproducible without relying on a future default. Do not generalise it to every autocommit setting.

Here is the failing sequence, with table creation omitted:

db.execute(
    "INSERT INTO jobs VALUES (?, ?)",
    ("job-1", "done"),
)
try:
    db.executescript("""
        INSERT INTO receipts VALUES ('job-1', 'done');
        SELECT * FROM missing_table;
    """)
except sqlite3.Error:
    db.rollback()
    raise

The first execute() starts a transaction under the fixture’s DEFERRED isolation setting. executescript() commits it. The receipt insert then runs without an explicit enclosing transaction and completes before the invalid query fails.

SQLite’s transaction documentation explains the second part: an automatically started transaction commits when its last active statement finishes. A script containing several statements does not, by its mere grouping into one Python string, establish an all-or-nothing operation.

After the handler runs, a fresh connection finds both the job row and the receipt. The Python operation raised an exception, but its earlier database changes remain.

For a local agent scheduler, that can leave a job marked done even though its adapter returned an error. A retry may then fail on the job’s unique key. Another reader may interpret the saved receipt as completion. This fixture only demonstrates the persisted rows; those downstream reactions depend on your application’s state machine.

Two repairs that do not cover the original write

Wrapping the same code in with db: looks attractive. Python’s connection context manager commits or rolls back an open transaction on exit. It does not undo a transaction already committed inside the block. The download tests this variant and finds the same two persisted rows.

Adding BEGIN and COMMIT inside the script changes the outcome, but still leaves a gap:

BEGIN;
INSERT INTO receipts VALUES ('job-1', 'done');
SELECT * FROM missing_table;
COMMIT;

The failure prevents the final COMMIT from running. The Python handler rolls back the receipt insert. However, executescript() already committed the job row before reaching this BEGIN. The resulting database contains one job and no receipt.

That is a useful regression test because the change appears to repair atomicity when you inspect only the script’s own table. Test the complete operation, including writes made before the helper was called.

Give the operation one transaction owner

For this small ledger update, the repaired path avoids executescript(). It opens a fresh connection configured for explicit SQL transaction control, starts the transaction before either write, and executes fixed statements with bound parameters:

db = connect(path, isolation_level=None)
try:
    db.execute("BEGIN IMMEDIATE")
    db.execute(
        "INSERT INTO jobs VALUES (?, ?)",
        (job_id, "done"),
    )
    db.execute(
        "INSERT INTO receipts VALUES (?, ?)",
        (job_id, "done"),
    )
    db.execute("COMMIT")
except BaseException:
    if db.in_transaction:
        db.execute("ROLLBACK")
    raise
finally:
    db.close()

connect() here is the fixture’s helper, not a bare alias for sqlite3.connect. On Python 3.12 and later it pins legacy transaction control so that isolation_level=None disables Python’s implicit transaction opening. The SQL BEGIN, COMMIT and ROLLBACK then define the boundary. Copy the helper as well as the operation, or choose and test an equivalent explicit connection policy for your runtime.

The broad exception handler exists to attempt rollback and immediately re-raise, including for interruption. It does not convert an error into success. If rollback itself fails, a service needs to retain both failure details and reconcile the database rather than announce that nothing changed.

BEGIN IMMEDIATE acquires a write transaction at the start. SQLite documents that it can fail with SQLITE_BUSY when another writer is active. This example has no contention-retry policy and does not claim to test concurrent writers. A busy failure must remain a failure until the caller deliberately retries under its own limits.

The function owns a fresh connection. It must not be pasted unchanged into a caller’s existing transaction: SQLite BEGIN transactions do not nest. A shared-connection design needs a documented owner and, where appropriate, savepoints. Helpers must not commit work owned by their caller.

Bound parameters also keep a job identifier out of SQL syntax. One test passes an identifier containing quote and SQL-like text and verifies that it is stored as data. This is a fixed-operation adapter, not a sandbox for arbitrary SQL supplied by a model.

Run the failure paths and reopen the database

Download and inspect agent-sqlite-script-rollback.py. It needs Python 3.9 or later and only the standard library. It creates temporary database files and removes them when each run finishes.

python3 agent-sqlite-script-rollback.py
python3 agent-sqlite-script-rollback.py --test
python3 -O agent-sqlite-script-rollback.py --test

The demonstration prints:

Script + rollback: jobs=1, receipts=1
Explicit transaction: jobs=0, receipts=0

The eight tests cover the original script failure, the ineffective context-manager wrapper, and the narrower transaction placed inside the script. The repaired path is tested with an injected failure after each write, successful completion, a duplicate job key, and the SQL-shaped identifier.

Each test checks rows through a newly opened connection after the operation has closed its own connection. This avoids confusing uncommitted changes visible to the writer with the state another consumer can read. The checks use unittest assertions, which remain active under Python optimisation.

The fixture deliberately uses tiny tables. The receipt has no foreign key, and the schema does not model approval or authorisation. Those are separate requirements. The earlier SQLite approval-ledger article covers enabling and testing foreign-key enforcement; passing that check would not repair this transaction boundary.

Keep local rollback separate from remote recovery

One SQLite transaction can bind these two local inserts. It cannot undo an email, a payment or a tool call that already changed another service. Do not hold this database write transaction open while waiting for a model or remote tool and assume that a later SQL rollback reverses the external effect.

For those workflows, the local ledger needs to distinguish an intended operation, an attempted call and a verified outcome. An exception after dispatch may require reconciliation rather than immediate retry. See the related discussion of a timed-out MCP write with an unknown outcome.

This fixture also does not test power loss, filesystem durability, disk-full recovery or journal configuration. A fresh-connection read proves the observed database state after these handled failures. It is not a crash-durability certification.

For the wider engineering approach to agent adapters and review evidence, see my Claude Code book, available as a Kindle edition on Amazon UK.

Add a failure after every database write in the actual adapter. Reopen the database and compare every table the operation was allowed to change. If a helper commits midway, move the transaction boundary or split the operation into explicitly recorded stages before enabling automatic retries.