What causes deadlocks and how do you prevent and handle them?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
A deadlock is a lock cycle: transaction A holds row 1 and waits for row 2 while B holds row 2 and waits for row 1 — neither can proceed, so the database aborts one (the victim) and the app must retry it. Prevent with short transactions, consistent lock order, and low isolation; handle by catching the deadlock error and retrying the whole transaction.
Detailed explanation
Locks serialize access to rows, and any multi-row transaction acquires them one at a time. When two transactions acquire the same locks in opposite order, each ends up waiting on the other forever — a cycle the database's deadlock detector spots (typically within a second in PostgreSQL) and breaks by killing one participant with a deadlock error. The victim loses all its work; retrying it usually succeeds because timing differs. Prevention is mostly discipline: keep transactions short so locks are held briefly (never wait on network calls or user input inside a transaction); always lock rows in a consistent order (ORDER BY id when selecting multiple rows FOR UPDATE); touch the fewest rows possible and prefer row locks over escalating to page/table locks; use the weakest isolation level that is correct; and design hot paths (queues, counters) to avoid contended single rows. Detection and handling: log deadlock errors with both queries involved, fix recurring pairs by reordering, and wrap every transaction in retry logic for deadlock/serialization errors with bounded backoff. Foreign keys and their supporting indexes matter too — an unindexed foreign key can force table-level locks on the child during parent updates, manufacturing deadlocks that an index eliminates.
Example
-- Classic cycle: opposite lock order
-- Txn A: UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- Txn B (concurrent): UPDATE accounts SET balance = balance - 10 WHERE id = 2;
-- UPDATE accounts SET balance = balance + 10 WHERE id = 1;
-- → deadlock: each waits on the other's row. Engine aborts one; retry it.
-- Fix: consistent ordering in both code paths
-- SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;Interview tip
Sketch the two-transaction cycle in one breath, then give the two-part answer: prevent with short transactions plus consistent lock order, handle with whole-transaction retry. Mentioning unindexed foreign keys as a deadlock source shows depth.
Common mistakes
- Retrying only the failed statement instead of the whole transaction.
- Doing I/O or RPCs while holding open transactions.
- Locking rows in different orders across code paths.