Locking, isolation levels, and MVCC

Postgres isolation is MVCC: every UPDATE writes a new row version; readers see the version snapshot valid for their transaction; old versions linger until VACUUM. Writers don't block readers, readers don't block writers — only writer-writer conflicts on the same row block.

Isolation levels (Postgres's actual behavior):

Write skew (two transactions each read overlapping data and write disjoint rows, violating a combined invariant) is the subtle one; SERIALIZABLE catches it via predicate locks (SSI). Your systems run Read Committed + explicit invariants in WHERE clauses — the pragmatic 90% answer.

Your real races, mapped to the theory

Race 1 — approve vs expire (zoth). Both flip status conditionally. Under MVCC: T1 (approve) locks the row, writes ACTIVE; T2 (expiry tick) blocks, re-checks WHERE status='ACTIVE' AND expires_at<now()... note the expiry loop first selects candidates, then updates. Between select and update, the approver can act — the update's WHERE re-check saves you (0 rows → "a concurrent transition beat us" → swallowed ErrConflict). Say it in these words: the select is advisory, the update's predicate is authoritative.

Race 2 — two approvals (zoth). Two managers click approve: both pass read-side authority checks (the pending approval row exists for both reads), then both fire the conditional update; one wins, the other gets 0 rows → 409. The invariant "exactly one transition" is enforced by the row itself, not by application locking.

Race 3 — duplicate submission (traverse). Extension retries: two POSTs race past the read-side dedup check (findUnique finds nothing for both) → both INSERT → the @@unique([userId, idempotencyKey]) constraint makes the DB reject the second with P2002 → mapped to 200-duplicate. Constraint as last line of defense after check-then-insert races — the standard three-layer answer: application check (fast path), unique constraint (truth), idempotent handling of the error (ergonomics).

Race 4 — notification claim (traverse). FOR UPDATE SKIP LOCKED + lease (Day 62 full treatment) — readers-writer coordination implemented as a claiming UPDATE.

FOR UPDATE semantics to recite: after a row is locked by one transaction, a competing SELECT FOR UPDATE waits (or skips, with SKIP LOCKED); a competing UPDATE waits, then re-evaluates its WHERE against the committed version — this "EvalPlanQual" re-check is precisely why your conditional updates are race-free. If the predicate no longer matches: 0 rows, no error, no write.

Deadlocks

Two transactions updating rows in opposite orders (A→B and B→A) deadlock; Postgres detects and kills one (ERROR: deadlock). Your code avoids it by updating single rows (nothing to order). The classic mitigation — consistent lock ordering — becomes real when a workflow touches multiple rows (e.g., approving while updating the approval row AND the request row: zoth's decide does approval-row update then request-row conditional update — if anywhere in the code the opposite order existed, you'd have a deadlock window; it doesn't, but knowing to check is the skill).

The SERIALIZABLE pitch (and why you didn't use it)

If your invariant spans rows ("an approver can't approve two requests that together exceed a risk budget"), Read Committed + conditional updates can't express it — you'd need SERIALIZABLE (with retry-on-40001) or explicit advisory locks. Honest zoth answer: current invariants are all single-row, so Read Committed suffices; the day a multi-row invariant appears (approval chains with quotas), SERIALIZABLE-with-retry or SELECT FOR UPDATE aggregates is the tool. Knowing when to escalate is the interview point.