The database will break it, badly
Engines run a deadlock finder that now and then looks for cycles in the wait-for graph. When it finds one it picks a victim — often the transaction that has done the least work. So The least is lost — and kills it with a deadlock error. The survivor continues.
That detail is needed and it isn't a fix. The victim's work is gone. If the use doesn't catch the error and retry, a user's action simply failed, and it failed intermittently, under load, in a way that is very hard to copy.
The fix is ordering
Switch the timeline to "same order" and step through again. The schedule is almost the same. The only change is that B asks for row 1 before row 2, just as A does.
Now B blocks at step 4 and stays blocked. A proceeds, finishes and releases both locks. B unblocks and ends. Both transactions succeed, no error is raised, and nothing is retried.
This is the whole technique: acquire locks in a steady order all over in the use. A cycle needs two transactions to disagree about the order, so if no one disagrees, no cycle can form. Sorting by main key is the usual choice because it's total, any and easy to apply mechanically.
The classic case is a transfer between two accounts. Written naively it locks the source then the destination, and two transfers in opposite directions deadlock right away. Written as "lock the lower id first, then the higher" they queue instead.
What makes it more likely
Long transactions. Locks are held to the end. So The longer a transaction runs the wider the window in which someone can interleave with it. Don't open a transaction and then call an external API inside it.
Broad locks. A statement without an index may lock far more rows than it needs, because rows it checks and rejects can still be locked. Adding the index narrows the query and the locking together. This Is why an index at times fixes a deadlock no one thought was an index problem.
Escalation. Some engines convert many row locks into one table lock past a threshold, turning a narrow conflict into a broad one.
Mixed access patterns. Different code paths touching the same tables in different orders is the underlying cause of most production deadlocks, and it tends to appear when two features written months apart meet under load.
Retry properly
Even with good discipline, deadlocks happen. The handling is a small amount of code and it has to be right:
- Catch the deadlock error in detail, not each error.
- Retry the whole transaction from the beginning. Re-running the failed statement alone is wrong — the transaction was rolled back fully.
- Back off before retrying, with some noise, so two victims don't collide again on the same schedule.
- Cap the attempts and log what happened. So A genuine design problem doesn't hide behind a retry loop that quietly succeeds on the fourth attempt.
The transfer, in both lock orders
The fix is one worked example away. Two transfers run at once — A moves money from account 1 to 2, B from account 2 to 1. And the only thing that differs is the order each takes its locks.
| step | naive (lock source first) | ordered (lock lower id first) |
|---|
| 1 | A locks 1 | A locks 1 |
| 2 | B locks 2 | B waits for 1 |
| 3 | A wants 2 → blocked | A locks 2, works, commits |
| 4 | B wants 1 → deadlock | B proceeds, commits |
Naively, A holds 1 and wants 2 while B holds 2 and wants 1. A cycle, and the finder kills one of them. Sorted by account id, B simply waits at step 2 for a lock A already holds, A finishes, and B continues. Same two transfers, same rows, no error — because a cycle needs the two to disagree about order, and "always lock the lower id first" drops the disagreement. That is the entire technique, and it's code discipline, not a DB setting.
Retry with backoff, not in lockstep
The retry after a deadlock has one detail that decides whether it helps: the wait before retrying must be *randomised*. Both victims of a deadlock were doing similar work also, so if both retry right away. Or both after just 100 ms — they collide again on the same schedule, and again after that:
| retry strategy | outcome under contention |
|---|
| immediate | the two collide again at once |
| fixed delay | they collide again after the delay |
| exponential backoff with jitter | the two drift apart and one wins |
Exponential backoff spreads successive attempts (100 ms, 200 ms, 400 ms), and the jitter — a random fraction added to each — breaks the symmetry so the two transactions stop arriving together. Without the noise a deadlock can become a livelock: no cycle in the wait graph. But Two transactions retrying in step and failing each other forever. The fix for a deadlock and the fix for the retry storm it can cause are different, and both are needed.
Questions people ask about Deadlocks
What is a deadlock? Two or more transactions each hold a lock the other needs, so none can proceed. The database detects the cycle and aborts one.
Is a deadlock the same as a slow lock wait? No. A wait ends when the holder commits. A deadlock is a cycle that can never resolve without intervention.
How do I prevent deadlocks? Make every transaction take locks in the same order, keep transactions short, and avoid waiting on user input while holding locks.
What should my code do after a deadlock error? Roll back and retry the whole transaction, ideally with a short random delay so the two sides do not collide again.
Which transaction does the database kill? Usually the one that is cheapest to roll back, though the rule differs between engines. You cannot rely on being the survivor.
Can an index help avoid deadlocks? Yes. Without a good index, a statement may lock many more rows than it needs, which raises the chance of overlap.
Where it goes wrong with Deadlocks
Treating it as a DB configuration problem. No setting blocks deadlocks. The order in which your code takes locks does.
Retrying the statement instead of the transaction. The rollback undid all, so the retry starts from a state that no longer exists.
given reads are safe. SELECT ... FOR UPDATE takes locks and participates in cycles just like an update.
Silently swallowing the retry. A rising deadlock rate is a signal about the system's design. Log it.