# Transaction Management

> Use transactions and isolation levels correctly — keep them short, no network calls inside, explicit isolation, retry on serialization conflicts, and choose optimistic vs pessimistic locking. Use when a write spans multiple tables, when concurrent updates corrupt data, or when designing money/inventory flows. Not for cross-service event delivery (use async-messaging Outbox) or schema-level constraints (use schema-design).

- Skill: `jaykim88/transaction-management` (Agent Skill)
- Install (CLI): `npx skillmds@latest add jaykim88/transaction-management`
- Raw SKILL.md: https://api.skillmd.com/api/skills/jaykim88/transaction-management/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- License: MIT
- Author: JayKim88 (https://skillmd.com/u/jaykim88)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/jaykim88/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

| ❌ Anti-pattern | ✅ Correct |
|---|---|
| HTTP/queue call inside a transaction | Do I/O outside; emit events via Outbox |
| Read-modify-write without locking | Optimistic (version) or pessimistic (`FOR UPDATE`) |
| Ignoring serialization-failure errors | Bounded retry on serialization failure (SQLSTATE `40001`) |
| Long-running transaction (batch loop) | Chunk into short transactions |
| Inconsistent lock acquisition order | Acquire locks in a consistent order to avoid deadlocks |
| Workers blocking on the same queue row | `FOR UPDATE SKIP LOCKED` for parallel queue consumption |
| App-level "only one runs" enforced with a flag column (race) | Advisory lock (`pg_advisory_xact_lock`) for coordination |

## Severity tiers

| Tier | Examples | Action SLA |
|---|---|---|
| **Critical** | Read-modify-write on money/inventory with no locking → lost update / double-spend; serialization failure swallowed on a financial invariant; outbox event written outside the state-change transaction (event ⟺ state divergence) | Block release; fix immediately |
| **Major** | Network/IO call inside a transaction (long-held locks → contention); serialization conflicts not retried (bounded); inconsistent lock-acquisition order producing deadlocks | Fix this sprint |
| **Minor** | Isolation level left implicit where Read Committed is acceptable; pessimistic `FOR UPDATE` used where optimistic versioning suffices (throughput cost) | Schedule within 2 sprints |

## 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`.