Two database transactions are each waiting for a lock the other already holds, and neither can make progress. What is this situation called, how does a relational database typically get out of it, and what is the application expected to do?
answer
- cycle in the wait-for graph
- engine picks a victim, full rollback
- distinct retryable error code
- retry the whole transaction, with jitter
- usual cause: inconsistent lock order
basics
~20 sIt is a deadlock. The database detects the cycle of waits, picks one transaction as victim, and rolls it back with a deadlock error; the survivor proceeds. The application must catch that error and retry the whole transaction, ideally after a short backoff.
solid answer
~50 sThat is a **deadlock**: a cycle in the wait-for graph, where each transaction waits on a lock held by another. Nobody can proceed and no amount of waiting resolves it, so the database must break the cycle itself. Most engines run a deadlock detector that periodically builds the wait-for graph (a node per transaction, an edge per "waits for") and looks for a cycle. On finding one it selects a **victim** — often the transaction with the least work done or the fewest locks held — rolls it back, and returns a specific deadlock error. The other transactions immediately acquire their locks and continue. Some systems instead rely on a lock-wait timeout, which resolves the same situation more bluntly and more slowly. The application must treat the deadlock error as **transient and retryable**: catch it at the transaction boundary and re-run the entire unit of work, since the victim's changes were fully rolled back. Repeated deadlocks are a design signal — inconsistent lock ordering or long transactions.
code
text · 5 linesT1: locks row A ......... waits for row B
T2: locks row B ......... waits for row A
wait-for graph: T1 --> T2 --> T1 (cycle)
detector aborts one of them; the other proceedsgo deeper
State the definition (each transaction waits for a lock the other holds), that the database aborts one with a deadlock error and rolls it back fully, and that the application retries.
Add the wait-for graph model, the victim-selection heuristics, the difference from a lock-wait timeout, and the retry contract with bounded attempts and jittered backoff.
Treat the deadlock rate as a signal: locate the conflicting access orders from the engine's deadlock reports, fix the ordering or shorten the transactions, and keep retry as a safety net rather than the remedy.
Set the platform-wide policy — a shared retry wrapper, idempotent transaction boundaries, deadlock-rate SLOs and alerting — and decide when a hot contention point should be redesigned rather than retried.
## What a deadlock is A transaction that needs a row or object already locked in an incompatible mode waits. Waiting is normal and usually short. A **deadlock** is the pathological case: transaction T1 holds lock A and wants lock B; transaction T2 holds lock B and wants lock A. Each is waiting for something only the other can release, and neither will release anything before it finishes. This is a *cycle*, and it is permanent — waiting longer cannot help, so something external must break it. The standard model is the **wait-for graph**: one node per active transaction, and a directed edge T1 → T2 whenever T1 is blocked on a lock T2 holds. A deadlock is exactly a cycle in that graph. The cycle may involve two transactions or twenty; the two-transaction case is simply the common one. ## The everyday cause Overwhelmingly, deadlocks come from **inconsistent access order**. One code path updates account 1 then account 2; another updates account 2 then account 1. Run them concurrently and each grabs its first row before the other asks for it, and the cycle is complete. The same thing happens implicitly: two batch jobs updating the same set of rows in whatever order their result sets happened to arrive, or a transaction that reads a row and only later upgrades to a write on it while another does the same in the other direction. Note that a deadlock is not a bug in the database and not a sign of corruption. It is the concurrency-control mechanism doing its job: the alternative to detecting and breaking the cycle is two sessions hung forever. ## How the database resolves it Two strategies exist, and most systems use one of them as the primary mechanism. **Detection.** The engine builds or maintains the wait-for graph and searches for cycles — typically not on every wait (too expensive) but after a transaction has waited past a short threshold, or on a periodic sweep. On finding a cycle it must break it by aborting a member. The chosen transaction is the **victim**: it is rolled back completely, all its locks are released, the cycle disappears, and the remaining transactions immediately proceed. The victim's session receives a distinct error code identifying the failure as a deadlock. **Timeout.** Simpler systems (and some configurations of sophisticated ones) skip graph analysis: any transaction that waits longer than a configured lock-wait timeout is aborted. This resolves genuine deadlocks eventually, but it cannot distinguish a deadlock from a merely slow lock holder, so it also kills innocent waiters, and it makes every deadlock cost at least the full timeout in latency. ## Choosing the victim Engines aim to abort the transaction that is cheapest to redo, using heuristics such as: how much work it has done (rows changed, log records written), how many locks it holds, how long it has been running, and sometimes an operator-assigned priority. Because the criteria are internal, the application cannot assume it will or will not be the victim — either side of the cycle can lose, so *every* transaction that takes locks needs the same retry handling. ## What the application must do The victim's transaction is **entirely rolled back** — this is the key fact for a junior to internalise. Nothing it wrote survives, so there is no partial state to clean up and no half-finished work to reconcile. The correct handling is: 1. Catch the deadlock error specifically, distinguishing it from constraint violations and other non-retryable errors. 2. Retry the **whole transaction** from the start, re-reading everything, because the surviving transaction has since changed the data. 3. Bound the retries — a handful of attempts — and back off with a small randomised delay, so the retried transactions do not immediately collide again. 4. Keep non-transactional side effects (emails, payment calls, messages) outside the retried block, since the block may execute more than once. 5. Count deadlocks as a metric. A trickle is normal in a busy write-heavy system; a rising rate is a design problem to fix, not a retry budget to increase. ## What it is not A deadlock is not the same as a lock-wait timeout: the timeout means somebody held a lock too long, with no cycle involved, and the fix is usually the slow holder, not the ordering. It is not the same as a serialization failure under snapshot or serializable isolation, which involves no waiting cycle at all and is caused by conflicting reads and writes rather than by lock ordering. And it is not livelock — repeated aborts and retries that never make progress — which is what unbounded, unjittered retry loops turn a deadlock into.
- After the database rolls back the victim, is there any partial state the application needs to clean up?No. The rollback is complete — every row the victim changed reverts, and every lock it held is released. That is what makes a plain retry of the whole transaction correct. The only things that survive are effects outside the database, such as emails already sent or external API calls already made, which is why those belong after commit rather than inside the transaction.
- How is a deadlock different from a lock-wait timeout?A deadlock is a cycle: the waiting transactions can never make progress, so waiting longer is futile and the engine breaks the cycle by aborting a victim. A lock-wait timeout means a single transaction waited too long on a holder that may still be working normally, with no cycle involved. The fixes differ: deadlocks point at inconsistent access order, timeouts usually point at a transaction holding locks too long.
- Can more than two transactions be involved in one deadlock?Yes. Any cycle in the wait-for graph is a deadlock, so three or more transactions can form a ring where each waits on the next. The detector finds the cycle regardless of its length and aborts one member, which is enough to break the ring and let the remaining transactions proceed.
Two people meet in a narrow corridor, each holding the door the other needs to pass. No amount of politeness resolves it; someone has to step all the way back to the start of the corridor and try again.
saying these in an interview costs you the question
- Believing a deadlock resolves itself if you just wait longer
- Thinking the victim's work is partially applied and needs manual cleanup
- Retrying only the failed statement instead of the whole transaction
- Assuming your transaction will never be chosen as the victim
- Confusing a deadlock with a slow query or a lock-wait timeout