🔁Deadlocks
Cycles, victims, retries
A deadlock is a cycle of transactions waiting on each other's locks. The engine picks a victim and aborts one transaction, client code must retry idempotent operations. Cycles come from incompatible lock modes on the same resources acquired in different orders; MVCC reduces some lock needs but not all (DDL, FK checks, predicate locks).
📊Classic two-session cycle
Same two rows, opposite lock order.
| Session A | Session B |
|---|---|
| UPDATE rows WHERE id=1 | UPDATE rows WHERE id=2 |
| UPDATE id=2 (waits) | UPDATE id=1 (waits) |
Uniform lock ordering prevents many cycles
-- Always lock accounts in ascending id order inside transfers| pattern |
|---|
| Sort pair (min_id, max_id) then lock both in order |
What goes wrong
- Swallowing deadlock errors without bounded retries.
- ORM implicit ordering causing nondeterministic lock order.
- Long transactions widening the deadlock window.
What to do instead
- Standardize lock ordering across modules touching the same tables.
- Keep transactions short; avoid user prompts inside transactions.
- Expose deadlock metrics and alert when rates spike after deploys.
- Use deferrable constraints only with clear commit-phase semantics.
Design
Design lock acquisition order (for example always lock account then ledger) or use row-level hot spots intentionally with queues so concurrent flows do not reverse ordering by accident.
TL;DR
A deadlock is a cycle of transactions waiting on each other's locks; the engine aborts one victim with an error so the others can proceed (behavior is engine-specific). Retries with jitter are normal. Prevention: acquire locks in a consistent global order, shorten transactions, avoid overlapping updates to hot rows, and index foreign keys so child deletes/updates do not escalate to table scans that widen lock graphs.