Scaling data / Advanced / 5 min
Database deadlocks: two transfers, two rows, stuck
Transfers lock two account rows each. Watch a wait-for cycle form, the detector kill a victim, and lock ordering make deadlocks impossible.
Start the simulation → Change the load, break a part, watch what happens. Free, no sign-up.
In short
- What it is
- A deadlock is two or more transactions each holding a lock the other needs; the database detects the cycle and aborts one of them.
- Why it matters
- Busy tables with transfers, inventory or counters hit it under load, and every abort means a retry and extra latency.
- The trade-off
- Fast detection frees rows quickly but costs checking; strict lock ordering or shorter transactions avoid deadlocks but take discipline in code.
- Say it in an interview
- "Lock rows in a consistent order (SELECT … FOR UPDATE ORDER BY id), keep transactions short, and retry on deadlock errors with a small backoff."
Check yourself
Transfer A→B locks A then B; transfer B→A locks B then A. What can happen?
Answer: Each holds one row and waits for the other forever: a deadlock. Opposite lock orders create a cycle in the wait-for graph.
What does the database do when it detects a deadlock?
Answer: Aborts one transaction (the victim) so the other can finish; the app should retry it. Postgres and MySQL both pick a victim and return an error such as "deadlock detected"; retrying is the app’s job.
How do you prevent this kind of deadlock?
Answer: Always lock rows in the same order, for example by lowest id first. With one global order no cycle can form; keeping transactions short also shrinks the window.