What is a Deadlock in Database?
Learn what a database deadlock is, how circular waits form between transactions, how engines detect and resolve them, and prevention strategies.
Expected Interview Answer
A deadlock in a database happens when two or more transactions each hold a lock the other needs, so every transaction involved waits forever and none can proceed.
This forms a circular wait: Transaction A holds a lock on Row 1 and waits for a lock on Row 2, while Transaction B holds the lock on Row 2 and waits for Row 1. Neither can release its lock because neither can finish. Database engines detect this cycle using a wait-for graph or a timeout, then break it by aborting one transaction (the "victim") and rolling back its work so the other can continue.
- Detection prevents indefinite hangs
- Rollback keeps data consistent
- Retry logic recovers automatically
- Consistent lock ordering avoids recurrence
AI Mentor Explanation
Picture two batters who have crossed mid-pitch for a run, each waiting for the other to reach their crease first before continuing, and neither moves. The umpire has to step in, call it dead, and send one batter back to restart the play. A database deadlock is the same standoff: two transactions each wait on a lock the other holds, so the engine acts as umpire, picks one transaction as the victim, and rolls it back so play can resume.
Step-by-Step Explanation
Step 1
Transactions acquire locks
Each transaction locks rows or tables it needs to read or write.
Step 2
Circular wait forms
Transaction A waits on a resource held by B, while B waits on a resource held by A.
Step 3
Engine detects the cycle
The database builds a wait-for graph or uses a timeout to spot the deadlock.
Step 4
Victim is rolled back
The engine aborts one transaction, releases its locks, and lets the other proceed.
What Interviewer Expects
- Clear definition of circular wait between transactions
- Knowledge of detection techniques (wait-for graph, timeout)
- Understanding of victim selection and rollback
- Awareness of prevention strategies like consistent lock ordering
Common Mistakes
- Confusing deadlock with normal lock contention or blocking
- Not mentioning rollback as the resolution mechanism
- Ignoring prevention techniques when asked how to avoid deadlocks
- Assuming deadlocks only happen with more than two transactions
Best Answer (HR Friendly)
“A deadlock happens when two database transactions each hold a lock the other one needs, so both end up waiting on each other forever. The database detects this situation and resolves it by rolling back one of the transactions so the other can finish successfully.”
Code Example
-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- waits here for id = 2, held by Session B
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Session B (running concurrently)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- waits here for id = 1, held by Session A
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;
-- The database detects the circular wait and aborts one session
-- with an error such as: ERROR: deadlock detectedFollow-up Questions
- How does a database detect a deadlock internally?
- What is the difference between deadlock and starvation?
- How can consistent lock ordering prevent deadlocks?
- What happens to the rolled-back transaction after a deadlock?
MCQ Practice
1. A database deadlock occurs when?
A deadlock is a circular wait where each transaction holds a lock the other needs.
2. How does a database typically resolve a deadlock?
The engine picks a victim transaction, aborts and rolls it back, freeing its locks.
3. Which structure do databases commonly use to detect deadlocks?
A wait-for graph models which transactions wait on which, and a cycle indicates a deadlock.
Flash Cards
What is a deadlock? — A circular wait where two or more transactions each hold a lock the other needs, so none can proceed.
How is a deadlock detected? — Via a wait-for graph cycle check or a lock-wait timeout.
How is a deadlock resolved? — The engine aborts and rolls back one transaction, the victim, releasing its locks.
How can deadlocks be prevented? — By acquiring locks in a consistent order across all transactions.