Area 08 · Backend · Concept 10 of 20

Transactions and ACID

Group writes so they all succeed or all fail, and your data stays consistent.

01–10 · The basePhase 0 + 5 · Weeks 1–2, 18–20≈ 1 hour

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.

Why it matters

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

  1. In psql, insert a document, then insert chunks with an error halfway (without BEGIN).
  2. Check what was left behind.
  3. Repeat inside BEGIN ... COMMIT and compare.

Use it in code

Charge credits and record usage atomically, after the LLM call.

python
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)
PRACTICE · TICK IT AFTER THIS

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.

Go deeper