Transactions and Locking skill for Claude Code

Concurrent code is correct when you can name the anomaly each transaction is protected against and the mechanism that protects it (constraint, atomic statement, row lock, version check, or serializable isolation), when every lock wait has a timeout, and when the code retries the errors that the chosen mechanism is designed to raise. “It works in testing” means nothing: tests run one request at a time.

Workflow

- [ ] 1. Identify the invariant (e.g. balance >= 0, stock never oversold, one active subscription)
- [ ] 2. Find every code path that reads and writes the data behind it
- [ ] 3. Name the race (lost update, write skew, phantom, double insert)
- [ ] 4. Pick the cheapest correct mechanism (decision table)
- [ ] 5. Set timeouts and retries
- [ ] 6. Prove it with a concurrent test

3. Name the race

Anomaly	Example	Prevented at Read Committed (PG default)?
Lost update	Two requests read stock=5, both write 4	No, unless atomic update or lock
Write skew	Two doctors each check “another doctor on call” and both go off call	No; needs Serializable or locking the checked rows
Phantom / double insert	Two requests check “no active subscription” and both insert one	No; needs a unique (partial) index, or Serializable
Non-repeatable read in reports	Report totals change mid-transaction	No; use Repeatable Read for consistent snapshots

4. Pick the mechanism

Situation	Mechanism
Uniqueness rule (“one active subscription per user”)	Unique (partial) index; handle the unique violation (23505)
Simple counter or balance change	Atomic update with a guard in the WHERE clause
Read, decide in app code, write back; conflicts rare	Optimistic concurrency: version column, check affected rows
Read, decide, write; conflicts common or the work is expensive	Pessimistic: SELECT ... FOR UPDATE
Invariant spans rows/tables that are not easily locked (write skew)	SERIALIZABLE + retry, or lock a parent row that represents the invariant
Work queue consumed by many workers	FOR UPDATE SKIP LOCKED
Mutual exclusion on something that is not a row (a tenant’s nightly job)	Advisory lock
Consistent multi-query report	REPEATABLE READ (read-only)

Atomic update (preferred whenever the logic fits in SQL):

UPDATE inventory SET stock = stock - $2
WHERE sku = $1 AND stock >= $2
RETURNING stock;                 -- 0 rows returned = insufficient stock

Optimistic concurrency:

UPDATE documents SET body = $3, version = version + 1
WHERE id = $1 AND version = $2;  -- affected rows 0 = someone else won; reload and retry or report conflict

Expose the version to clients (ETag / If-Match) when edits happen across HTTP requests; a database lock cannot span a user’s think time.

Pessimistic locking:

BEGIN;
SELECT * FROM accounts WHERE id IN ($1, $2) ORDER BY id FOR UPDATE;   -- fixed order prevents deadlocks
-- checks in application code
UPDATE accounts SET balance = balance - $3 WHERE id = $1;
UPDATE accounts SET balance = balance + $3 WHERE id = $2;
COMMIT;

Lock strength (Postgres):

Clause	Use
FOR UPDATE	You will update/delete the row, including key columns
FOR NO KEY UPDATE	You will update non-key columns; does not block FK inserts referencing the row (less contention)
FOR SHARE	Row must not change while you read, others may also share-lock
FOR KEY SHARE	Taken implicitly by FK checks
NOWAIT	Fail immediately if locked (55P03)
SKIP LOCKED	Skip locked rows; for queues only, never for correctness checks

Queue with SKIP LOCKED:

WITH next AS (
  SELECT id FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  LIMIT 10
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs SET status = 'running', locked_at = now(), attempts = attempts + 1
FROM next WHERE jobs.id = next.id
RETURNING jobs.*;

Commit immediately after claiming; process outside the transaction; mark done in a new transaction; a reaper resets running jobs whose locked_at is stale.

Advisory locks:

SELECT pg_try_advisory_xact_lock(hashtext('nightly-billing'), $1::int);  -- released at commit/rollback

Prefer the _xact_ variants. Session-level advisory locks leak through connection poolers (PgBouncer transaction mode) and stay held if the app forgets to unlock.

Serializable:

BEGIN ISOLATION LEVEL SERIALIZABLE;
-- reads and writes
COMMIT;   -- may fail with 40001 (serialization_failure): retry the whole transaction

Postgres Serializable (SSI) does not block more; it aborts transactions that could produce a non-serial result. It is correct only if every transaction touching the data uses it and the app retries.

5. Timeouts and retries

SET lock_timeout = '3s';                          -- max wait for any single lock
SET statement_timeout = '30s';                    -- per statement
SET idle_in_transaction_session_timeout = '60s';  -- kill sessions that forgot to commit
-- PG17+: transaction_timeout caps the whole transaction

Set them per role (ALTER ROLE app SET lock_timeout = '3s';) so every connection inherits sane limits.

Retry policy (application side):

SQLSTATE	Meaning	Action
40001	serialization_failure	Retry whole transaction, exponential backoff + jitter, 3-5 attempts
40P01	deadlock_detected	Retry whole transaction; also fix lock ordering
55P03	lock_not_available (lock_timeout / NOWAIT)	Retry with backoff or return 409/503
23505	unique_violation	Not a retry: it is the business answer (“already exists”)

Retry the entire transaction from BEGIN, re-reading data. Never retry only the failed statement. Make side effects (emails, HTTP calls, queue publishes) happen after commit or through an outbox table so retries do not duplicate them.

Deadlocks

Causes, in order of frequency:

1. Two transactions lock the same rows in different orders (A then B vs. B then A).
2. Batch updates without a deterministic order (UPDATE ... WHERE id IN (...) touches rows in physical order, which differs between statements).
3. FK checks taking share locks on parent rows while another transaction updates the parent.
4. Lock upgrades: two transactions FOR SHARE the same row and then both try to update it.

Prevention:

• Lock in a global order (by primary key) with an explicit SELECT ... ORDER BY id FOR UPDATE.
• Keep transactions short; take locks late, commit early.
• Use FOR NO KEY UPDATE when not changing keys to reduce conflicts with FK checks.
• Batch jobs: process in key order with small batches.

Diagnose: set log_lock_waits = on (logs waits longer than deadlock_timeout, default 1 s); Postgres logs both statements of each deadlock. Live lock trees, blocker queries, and MySQL equivalents are in references/lock-diagnostics.md.

Transaction boundaries in application code

• One transaction per unit of business work, opened as late as possible.
• No network calls, file uploads, or LLM/API requests while a transaction is open: they hold locks and a pooled connection for seconds.
• ORMs: know whether the framework wraps each request in a transaction (Django ATOMIC_REQUESTS, Rails controller actions do not by default, Spring @Transactional proxies only work on external calls). Nested “transactions” are usually savepoints.
• Read-modify-write in ORM code (obj.count += 1; save()) is a lost update. Use the ORM’s atomic expression (F('count') + 1, increment, { increment: 1 }).

MySQL InnoDB notes

• Default isolation is REPEATABLE READ; locking reads and updates take next-key (gap) locks, so range updates block inserts into the gap. Many deadlocks in MySQL come from gap locks on non-unique index ranges.
• SELECT ... FOR UPDATE SKIP LOCKED / NOWAIT exist in 8.0+.
• innodb_lock_wait_timeout default is 50 s; lower it for OLTP sessions.
• Inspect with SHOW ENGINE INNODB STATUS\G (LATEST DETECTED DEADLOCK section) and performance_schema.data_locks / data_lock_waits.
• Deadlock error code 1213, lock wait timeout 1205; retry 1213 the same way as 40P01.

Gotchas

• SELECT ... FOR UPDATE on rows that do not exist locks nothing in Postgres. “Check then insert” needs a unique constraint or INSERT ... ON CONFLICT.
• Upserts: INSERT ... ON CONFLICT (key) DO UPDATE is atomic; ON CONFLICT DO NOTHING plus a follow-up SELECT can miss a row inserted by a concurrent uncommitted transaction (it becomes visible only after commit).
• Long-running transactions (even read-only, even idle) block vacuum cleanup and cause bloat; they also block DDL lock acquisition.
• PgBouncer transaction pooling: SET without LOCAL, session advisory locks, and prepared statements may leak or break across transactions.
• Repeatable Read in Postgres raises 40001 on concurrent updates of the same row; code using it must retry too.
• deadlock_timeout is not a lock wait limit; it is how long Postgres waits before checking for a deadlock. Use lock_timeout for limits.

Verification

Prove the fix with real concurrency, not reasoning:

# 50 concurrent attempts to buy the last 10 units; expect exactly 10 successes, stock 0, never negative
seq 1 50 | xargs -P 50 -I{} psql "$DATABASE_URL" -qtAc \
  "UPDATE inventory SET stock = stock - 1 WHERE sku = 'A1' AND stock >= 1 RETURNING stock" | wc -l
psql "$DATABASE_URL" -c "SELECT stock FROM inventory WHERE sku = 'A1'"

Or a test that runs the code path from N threads/goroutines/tasks against a real database (not mocks, not SQLite in place of Postgres), then asserts the invariant and that retries surfaced no errors to callers. Run it against a scratch database only.

Output: concurrency review note

**Invariant:** inventory.stock >= 0; one reservation per (user, drop).
**Race found:** checkout reads stock, checks in app, writes stock - 1 (lost update at RC).
**Fix:** atomic guarded UPDATE ... WHERE stock >= $qty RETURNING; unique index on reservations (user_id, drop_id).
**Timeouts:** app role lock_timeout 3s, statement_timeout 15s, idle_in_transaction 60s.
**Retries:** 40001/40P01 retried 3x with jitter in db.withRetry(); 23505 mapped to 409.
**Evidence:** 200 concurrent buyers for 25 units -> 25 successes, stock 0, no 5xx.