Use one SQLite transaction for related writes, such as adding a loan and updating stock. With Python’s sqlite3 connection configured as autocommit=False, call commit() after success and rollback() after failure. A with connection: block can make that decision when the block exits. Close the connection separately.
Identify the operation that needs to stay whole
A lending tool records a checkout and updates available inventory. If it updates availability but fails to write a loan record, the user can see fewer available tools with no explanation. Both changes should be in one transaction. The same idea applies to importing a group of records whose total must reconcile or recording changes alongside an audit entry.
A transaction can roll back the inventory and loan records. If the program has already emailed a receipt, rolling back the loan leaves that receipt in the borrower’s inbox. When checkout needs a receipt, save a pending notification alongside the loan and let a separate step send it after the database commits. Give that step its own way to handle a retry.
Commit or roll back a test case
This example runs in memory and uses Python 3.12 or later for the explicit autocommit setting. The context manager exits on an exception and rolls back the writes made inside the block.
import sqlite3
from contextlib import closing
with closing(sqlite3.connect(":memory:", autocommit=False)) as connection:
with connection:
connection.execute("CREATE TABLE inventory (name TEXT PRIMARY KEY)")
try:
with connection:
connection.execute("INSERT INTO inventory VALUES (?)", ("drill",))
connection.execute("INSERT INTO inventory VALUES (?)", ("drill",))
except sqlite3.IntegrityError:
pass
assert connection.execute("SELECT COUNT(*) FROM inventory").fetchone()[0] == 0
The duplicate name violates the primary key constraint. Because the exception exits the inner transaction block, the earlier insert in the same block is rolled back. Catch the error outside that block when relying on exception-driven rollback. If you catch it inside and exit normally, the context manager may commit earlier work unless you explicitly roll it back.
Check how autocommit affects the transaction
Python exposes more than one transaction control policy. In an existing project, inspect how the connection is created before adding manual BEGIN or assuming a with connection: block changes behavior. The connection context manager neither opens a new connection nor closes it at exit. With autocommit=True, its commit-and-rollback behavior is effectively inactive. The example chooses autocommit=False explicitly to avoid relying on defaults that can differ with code or Python version.
Do not substitute another database driver’s placeholder syntax or default policy for SQLite’s. The example’s question mark placeholders bind values. The underlying database decides transaction and locking behavior, and the Python driver provides an interface to it. Read both documents when porting an import between database engines.
Keep write transactions short
Validate file shape and calculate values before acquiring a write transaction when you can do so without violating data consistency. Avoid waiting for user input or making a slow network request while holding a write lock. When a decision depends on current database state, perform the relevant check and write together under a transaction policy that makes the decision safe.
A retry policy needs an operation identity. If a caller loses the response after a commit, retrying an insert can create a duplicate unless the application can recognize the same operation. A unique request key or a natural business key can make the result inspectable. Decide whether a retry should return the first result, update existing data or raise a conflict.
Test that a failed write leaves no partial change
Write a small test that makes the first statement succeed and the second one fail. Query the database afterward and verify the first change did not remain. Test the successful case too. When the database uses a real file, run verification through a separate connection so you know whether another client sees the committed result. A durable workflow needs a clear answer to both questions: what changed, and which changes will other readers see?