Deadlocks
Two transactions taking the same two locks in opposite orders. Step through it and watch the cycle close.
Overview
Waiting is normal; a cycle is not
When a transaction updates a row it takes an exclusive lock and holds it until it commits or rolls back. Anything else wanting that row waits.
Waiting is ordinary and self-resolving: the holder finishes, the waiter proceeds. A deadlock is different. It is a *cycle* in who is waiting for whom, and a cycle cannot resolve itself, because every participant is waiting for another participant that will never move.
Step through the timeline with "opposite order" selected. Nothing is wrong at step 4 — two transactions holding different rows is just concurrency. The problem appears at step 6, when the second edge closes the loop: A holds row 1 and wants row 2; B holds row 2 and wants row 1. Neither can proceed and neither will give up what it has.
Deadlocks
Two transactions updating the same two rows. Step through and watch which statement blocks.
Worth knowing
Deadlocks
Two transactions, two rows, and the one detail that decides whether they finish.
The database will break it, badly
Engines run a deadlock detector that periodically looks for cycles in the wait-for graph. When it finds one it picks a victim — usually the transaction that has done the least work, so the least is lost — and kills it with a deadlock error. The survivor continues.
That resolution is necessary and it is not a fix. The victim's work is gone. If the application does not 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 reproduce.
The fix is ordering
Switch the timeline to "same order" and step through again. The schedule is almost identical; the only change is that B asks for row 1 before row 2, exactly as A does.
Now B blocks at step 4 and stays blocked. A proceeds, finishes and releases both locks. B unblocks and completes. Both transactions succeed, no error is raised, and nothing is retried.
This is the whole technique: acquire locks in a consistent order everywhere in the application. A cycle requires two transactions to disagree about the order, so if nobody disagrees, no cycle can form. Ordering by primary key is the usual choice because it is total, arbitrary 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 immediately. 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. Do not 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 examines and rejects can still be locked. Adding the index narrows the query and the locking together, which is why an index sometimes fixes a deadlock nobody 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 specifically, not every error.
- Retry the whole transaction from the beginning. Re-running the failed statement alone is wrong — the transaction was rolled back entirely.
- Back off before retrying, with some randomness, so two victims do not collide again on the same schedule.
- Cap the attempts and log what happened, so a genuine design problem does not 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 detector kills one of them. Ordered 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 requires the two to disagree about order, and "always lock the lower id first" removes the disagreement. That is the entire technique, and it is code discipline, not a database 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 at the same time, so if both retry immediately — or both after exactly 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 randomness 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.
Where it goes wrong
Treating it as a database configuration problem. No setting prevents deadlocks. The order in which your code takes locks does.
Retrying the statement instead of the transaction. The rollback undid everything, so the retry starts from a state that no longer exists.
Assuming reads are safe. SELECT ... FOR UPDATE takes locks and participates in cycles exactly like an update.
Silently swallowing the retry. A rising deadlock rate is a signal about the system's design. Log it.
Check yourself
0 of …Answer without scrolling back up.
What distinguishes a deadlock from ordinary lock waiting?
What is the reliable way to prevent deadlocks?
After a deadlock error, what must the application retry?
Deadlocks
When a transaction updates a row it takes an exclusive lock and holds it until it commits or rolls back. Anything else wanting that row waits.