InnoDB says it 'detects deadlocks instantly,' but your app still sees intermittent deadlock errors under load — walk through exactly how InnoDB picks which transaction to kill, and why lock ordering alone doesn't always prevent it.

Picture two delivery drivers on a narrow one-lane bridge, each entering from opposite ends at the same moment, each blocking the other's path forward and unable to reverse without help. Neither driver did anything individually wrong — they just happened to start crossing at the same instant from opposite directions. Someone has to back one truck out to break the standoff, and the sensible choice is to back out whichever truck has the lighter, easier-to-reverse load, not whichever truck happened to enter the bridge first.

InnoDB (MySQL's default storage engine since 5.5) uses row-level locking to allow high concurrency, but that means two transactions can each hold a lock the other one needs — transaction A holds a lock on row 1 and is waiting for row 2, while transaction B holds row 2 and waits for row 1. InnoDB maintains an internal wait-for graph representing which transactions are blocked on which locks held by which other transactions, and it checks this graph for cycles proactively rather than waiting for a timeout, which is why deadlocks are detected in milliseconds rather than after lock_wait_timeout expires (that timeout is for genuine long waits, not deadlocks).

When a cycle is detected, InnoDB picks a victim transaction to roll back based on an internal heuristic approximating rollback cost — generally the transaction that has done less work (fewer modified rows) is killed, since undoing it is cheaper than undoing the other transaction's larger set of changes. This is a deliberate engineering tradeoff: InnoDB isn't trying to be 'fair' about which transaction started first, it's trying to minimize the total wasted work of resolving the deadlock, which is the right objective for overall system throughput even though it can feel arbitrary to the specific application that gets its transaction killed.

In production, engineers reduce deadlock frequency by keeping transactions short (holding locks for the shortest possible window), always accessing tables and rows in a consistent order across all code paths that touch multiple tables, ensuring proper indexes exist so UPDATE and DELETE statements don't lock far more rows than necessary via a full table scan, and considering READ COMMITTED isolation instead of the default REPEATABLE READ when gap locking behavior is causing unnecessary contention. Monitoring innodb_deadlocks and enabling innodb_print_all_deadlocks surfaces the exact competing statements so a team can identify a recurring pattern rather than treating each deadlock as a one-off.

The gotcha that defeats teams who did enforce consistent application-level lock ordering: InnoDB's default REPEATABLE READ isolation level uses gap locks and next-key locks (locks that cover the empty space between index entries, not just the index entries themselves) to prevent phantom reads, and these locks are taken based on how a query's WHERE clause maps onto the index it uses — not based on any explicit ordering the application controls. Two transactions can update completely different specific rows, in what looks like non-overlapping order from the application code, and still deadlock because their range-scan queries acquired overlapping gap locks on the same index. This is why deadlocks can appear between transactions whose application-level logic never seems to conflict, and the actual fix often requires either a covering index that avoids the gap-locking range scan, or explicitly switching that transaction to READ COMMITTED, not touching the application's lock ordering at all.