SQL · Queries that survive scale
What causes a deadlock and how do you handle it?
Short answer
A deadlock happens when transactions wait on each other in a cycle. Prevent it with consistent lock ordering, short transactions, and retries when the database aborts a victim.
Transaction A locks row 1 and waits for row 2. Transaction B locks row 2 and waits for row 1. Neither can proceed. The database detects the cycle and aborts one transaction, which the application must retry.
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;Prevention and recovery
- Always acquire locks in a deterministic order, such as ascending id
- Keep transactions short so lock windows are small
- Use a single statement where possible to avoid intermediate lock steps
- Retry the aborted transaction with exponential backoff and jitter
- Log deadlock details so the conflicting statements can be found
- Set lock and statement timeouts to bound the damage
- Avoid escalating to table locks unless the operation truly needs one
A deadlock is not always an application bug; it can happen under legitimate concurrency. The application must treat serialization and deadlock errors as retryable. Retries must be idempotent or protected by a transaction that fully rolled back.
Follow-ups you should expect
Interviewers use these to check whether the first answer was understanding or memorisation.
- 1How does the database resolve a deadlock?
- 2Why does consistent lock ordering prevent deadlocks?
- 3How should the application retry a deadlock victim?
- 4Can a deadlock happen with a single-row update?
Say it in the interview
A deadlock is a cycle of transactions each waiting for a lock the other holds. The database detects it and aborts one transaction. I prevent deadlocks by locking rows in a consistent order, keeping transactions short, and avoiding unnecessary lock escalation. The application still needs retry logic with backoff, because legitimate concurrency can produce deadlocks even in a well-designed system.
Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.
Related questions
Reading is step one
Now say it out loud.
Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.