The idea
A transaction wraps several database operations into one unit. ACID describes its guarantees: Atomic (all or nothing), Consistent (constraints hold), Isolated (concurrent transactions don’t see each other’s half-done work), Durable (committed data survives crashes).
Use transactions whenever several writes must stay in sync: creating a document and its chunks, deducting credits and recording usage, saving an agent step and its tool result. If anything fails, roll back so you never have chunks without a document or usage without a charge.
Keep transactions short. Never hold one open across an LLM call; do the slow call first, then write the results in a quick transaction.
Without them, partial failures leave your data in states your code never expected.
Words to know
- Commit / rollbackMake a transaction’s changes permanent / undo them.
- Isolation levelHow much concurrent transactions can see of each other.
Try it without code
Break consistency
- In psql, insert a document, then insert chunks with an error halfway (without BEGIN).
- Check what was left behind.
- Repeat inside BEGIN ... COMMIT and compare.
Use it in code
Charge credits and record usage atomically, after the LLM call.
import psycopg
def record_usage(conn, user_id, tokens, cost_cents, request_id):
with conn.transaction(): # BEGIN ... COMMIT, or ROLLBACK on error
row = conn.execute("SELECT credits_cents FROM accounts WHERE user_id = %s FOR UPDATE",
(user_id,)).fetchone()
if row[0] < cost_cents:
raise ValueError("insufficient credits")
conn.execute("UPDATE accounts SET credits_cents = credits_cents - %s WHERE user_id = %s",
(cost_cents, user_id))
conn.execute("INSERT INTO usage (request_id, user_id, tokens, cost_cents) VALUES (%s, %s, %s, %s)",
(request_id, user_id, tokens, cost_cents))
# reply = call_llm(...) <- slow call happens OUTSIDE the transaction
# record_usage(conn, user, reply.usage.output_tokens, cents, req_id)Wrap document + chunk creation in your ingestion in one transaction and test that a failure leaves nothing behind.
Common mistakes
- Holding a transaction (and row locks) open during a model call.
- Autocommit mode by accident.