What is a deadlock in a database and how is it handled?
A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. The database detects the cycle and aborts one transaction as the victim, and the application should retry it.
Explain it simply
Two people on a narrow bridge from opposite sides, each refusing to reverse: both are stuck forever. In a database, transaction A holds a lock on row 1 and wants row 2, while transaction B holds row 2 and wants row 1.
Example
-- Transaction A -- Transaction B
BEGIN; BEGIN;
UPDATE acc SET bal=bal-10 WHERE id=1; UPDATE acc SET bal=bal-10 WHERE id=2;
UPDATE acc SET bal=bal+10 WHERE id=2; UPDATE acc SET bal=bal+10 WHERE id=1;
-- waits for B -- waits for A, deadlockThe database finds the circular wait and rolls one transaction back with an error (for example "deadlock detected" in PostgreSQL, error 1213 in MySQL).
Ways to reduce deadlocks:
- Access rows in a consistent order, such as lowest id first.
- Keep transactions short.
- Lock only what you need, and use proper indexes so fewer rows are locked.
- Retry the aborted transaction in the application code.
What to say in the interview
"A deadlock is a circular wait for locks. Databases detect it and kill one victim. I prevent it by ordering lock acquisition consistently, keeping transactions short and writing retry logic."
Common mistakes
- Treating a deadlock error as a bug in the database rather than handling it with a retry.