Deadlock Detection and Prevention
Deadlocks are cycles in the waits-for graph of lock holders; databases detect them, abort one victim, and applications must make victim handling and lock ordering first-class.
Definition
A deadlock occurs when transactions hold locks and wait for each other in a cycle: neither can proceed. Engines run deadlock detection over the waits-for graph and abort a victim based on rollback cost or lock age. Prevention is design: acquire locks in a consistent global order and keep transactions short.
Context and scope
Hits any multi-row write flow: transfer between accounts, bulk updates, and ORM-driven cascades. MySQL InnoDB detects immediately; PostgreSQL detects on lock acquisition with a deadlock_timeout delay to avoid wasted scans.
Common misconceptions
- Deadlocks are random corruption — they are deterministic cycles in lock ordering; reproduce them with interleaving tests
- Retrying the victim transaction always fixes it — retry without addressing ordering re-enters the same cycle under load
- Reducing isolation removes deadlocks — most deadlocks come from multi-statement write ordering, not isolation level
Anti-patterns
- Locking rows in whichever order the iteration produced and creating cycles on every bulk path
- Swallowing deadlock errors without retry logic and losing writes silently
- Tuning deadlock_timeout lower to fail faster while burning CPU on detection scans under heavy lock traffic
Related concepts
Related examples
Authoritative source
https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html
What it is
Locks create a directed graph: who holds, who waits. A cycle in that graph is a deadlock — every member waits for another. Engines detect cycles by periodically scanning the graph, choose a victim whose rollback is cheapest, roll it back, and return an error the application must handle.
How it works
InnoDB checks for cycles when a lock wait begins. PostgreSQL waits deadlock_timeout — about one second by default — then runs detection, trading detection latency against CPU spent scanning a busy lock table. The victim gets a specific SQLSTATE; retries should be bounded, jittered, and instrumented, because deadlock frequency is a design signal.
When to intervene
Deadlocks above a threshold indicate unordered locking: sort the ids before updating, use consistent join orders, and split giant transactions. Queue claim patterns should use SKIP LOCKED to avoid the waits-for edges entirely.
Pitfalls
ORM cascades lock in object-graph order, not call order — audits must trace actual statements. Victim selection can hit either side; both code paths need retry handling. Under sustained cycles, retries amplify load and can cascade into pool saturation.