Transaction Management

Transaction Management

Purpose

Keep multi-step writes atomic and correct under concurrency — short transactions, explicit isolation, no I/O inside, and the right locking strategy — without creating deadlocks or contention.

Universal — ACID, isolation levels, optimistic vs pessimistic locking, and the "no network calls in a transaction" rule are RDBMS principles; the transaction API differs by ORM.

Procedure

1. Keep transactions short — and never do I/O inside

   • No HTTP calls, no queue publishes, no slow computation inside a transaction
   • A transaction holds locks; long-held locks → contention → deadlocks
   • Do external work before (gather data) or after (publish via Outbox)

2. Choose isolation level explicitly

   • Read Committed (Postgres default) — fine for most; each statement sees committed data
   • Repeatable Read — consistent snapshot for multi-read consistency
   • Serializable — strongest; for invariants across rows (e.g., "sum must not exceed limit"); expect serialization failures

3. Handle serialization conflicts with retry

   • Serializable / Repeatable Read can abort with a serialization failure (SQL-standard SQLSTATE 40001)
   • Wrap in a bounded retry loop (this IS idempotent — re-running the whole transaction)
   • Pair with resilience-patterns backoff (ORM-specific error codes are in Implementation)

4. Pick locking strategy

   • Optimistic (version column / WHERE updated_at = ?) — low contention, retry on mismatch; default for user-edit flows
   • Pessimistic (SELECT ... FOR UPDATE) — high contention on a hot row (inventory decrement, counter); serializes access
   • FOR UPDATE SKIP LOCKED — work-queue pattern; competing workers each claim a different row without blocking each other
   • Advisory locks (pg_advisory_xact_lock(key)) — application-level mutual exclusion (one-leader job, cron coordination) that doesn't tie to a specific row
   • Don't pessimistic-lock when optimistic suffices (throughput cost)

5. Co-locate the event with the state change (Outbox)

   • When a transaction must also emit an event, write to the outbox table IN the same transaction
   • Guarantees event ⟺ state consistency (see async-messaging)

5b. Use savepoints for partial rollback inside a long transaction

• When one sub-step of a transaction may fail without invalidating the whole, set a savepoint before it and roll back to the savepoint on error — the outer transaction continues
• Don't over-use: many savepoints in one transaction means the transaction is doing too much; consider splitting into shorter transactions instead

6. Validate (validation loop)
   • Simulate concurrency (two parallel updates to the same row)
   • Verify: no lost update, no double-spend; conflicts retried, not silently dropped
   • If a deadlock appears → check lock acquisition order (acquire in consistent order across transactions)

Anti-patterns

Severity tiers

Stop & Ask (AI must pause for user approval)

• Before a bulk UPDATE / DELETE > 10k rows in a single transaction — chunk + commit per batch, or replication / lock storm risk
• Before changing the isolation level of an existing endpoint — retry / read-consistency behavior changes for every caller
• Before running a backfill inside one long-held transaction — lock duration + WAL pressure can take the DB down

Completion Criteria

• No network/IO calls inside any transaction
• Isolation level set explicitly where it matters
• Serialization conflicts retried (bounded)
• Hot-row updates use appropriate locking (verified under concurrency)
• Outbox events written in the same transaction as the state change

Output

• Transaction code: short, I/O-free, explicit isolation
• Locking decision: optimistic vs pessimistic, documented per flow
• Commit format: fix(tx): add FOR UPDATE lock to inventory decrement / fix(tx): retry on P2034 serialization conflict

Implementation

TypeScript + Prisma + Postgres (default)

• prisma.$transaction(async (tx) => { ... }, { isolationLevel: 'Serializable' })
• Retry on P2034 (write conflict / deadlock) with backoff
• Pessimistic: raw SELECT ... FOR UPDATE (Prisma $queryRaw); optimistic: version column + updateMany({ where: { id, version } }) check affected count
• Interactive transactions: use sparingly, set a timeout

Other stacks

• Python: SQLAlchemy Session.begin() + with_for_update(); retry on OperationalError
• Go: database/sql Tx; SELECT ... FOR UPDATE; retry on serialization error
• Universal: isolation levels are SQL-standard; the "no I/O in transaction" rule is universal; Outbox pattern is DB-agnostic

Related skills

• resilience-patterns — idempotency keys often stored in the same transaction
• schema-design — constraints + transactions enforce integrity together
• async-messaging — the Outbox event is written in the same transaction as the state change

Reference

• Key insight encoded: Keep transactions short and never do network calls inside them (long-held locks cause deadlocks and contention); set isolation explicitly and retry on P2034.