Learn the concept
A transaction groups changes into one commit or rollback boundary. Use it when several local writes must succeed together. A transaction in your database does not atomically include a remote API action, so external side effects still need operation IDs and reconciliation.
Constraints protect invariants even when multiple callers write concurrently. A unique operation ID can prevent duplicate requests from creating duplicate records. Handle the resulting conflict deliberately instead of assuming application checks are enough.
Indexes speed some lookups at the cost of storage and write work. Match an index to an actual query pattern and inspect the query plan. Adding an index to every column is not a substitute for understanding the workload. Database engines differ in locking and isolation behavior; test the engine you deploy.
Run and inspect
import sqlite3
from contextlib import closing
with closing(sqlite3.connect(":memory:")) as db:
db.execute("CREATE TABLE jobs (operation_id TEXT UNIQUE)")
with db:
db.execute("INSERT INTO jobs VALUES (?)", ("op-1",))
try:
with db: db.execute("INSERT INTO jobs VALUES (?)", ("op-1",))
except sqlite3.IntegrityError:
pass
assert db.execute("SELECT COUNT(*) FROM jobs").fetchone()[0] == 1
Your exercise
Write two related records in one transaction and inject a failure between them. Add a unique request ID and test repeated insertion.
Check your understanding
The failed transaction leaves no partial local state and duplicate operation IDs are handled explicitly.