When transactions wait in a cycle
A deadlock happens when two or more transactions each hold a lock the other needs, forming a cycle of waits that can never resolve on its own. InnoDB maintains a wait for graph of which transaction is waiting on which lock holder.
Automatic detection
InnoDB checks for cycles in the wait for graph as locks are requested. When it finds a cycle it picks a victim, usually the transaction that has modified the fewest rows, and rolls it back to break the cycle. The victim receives a deadlock error and can simply retry.
What an application should do
- Treat the deadlock error as retryable: catch it and replay the transaction.
- Acquire locks in a consistent order across the codebase to reduce cycles.
- Keep transactions short so locks are held briefly.
Tuning notes
- A lock wait timeout is a separate safety net: a transaction that waits too long for a lock is aborted even without a detected cycle.
- On very high concurrency, detection cost grows, and some deployments disable it and rely on the timeout instead.
Key idea
InnoDB detects deadlocks by finding cycles in its wait for graph and rolls back the cheapest victim, so applications should treat deadlock errors as retryable and lock in a consistent order.