What causes a database deadlock and how do you prevent one?
Interviewer Expectations for this Question
01
Core Competency
Assesses fundamental understanding of DBMS conventions, runtime behavior, and memory/performance considerations.
02
Evaluation Criteria
Hiring managers look for precision, avoidance of ambiguous jargon, and ability to explain trade-offs under real production conditions.
Comprehensive Model Answer
Verified Solution
A database deadlock is a cycle where two or more transactions each hold a lock the other needs, so neither can proceed.
T1: UPDATE a; UPDATE b;
T2: UPDATE b; UPDATE a; -- cycle
Most engines detect deadlocks by maintaining a wait-for graph and aborting a victim, which then rolls back and surfaces an error that the application should retry.
Prevention and mitigation:
- Access tables and rows in a consistent order across the application.
- Keep transactions short and avoid user interaction inside them.
- Touch rows in a deterministic order and use appropriate indexes so locks target fewer rows.
- Use lower isolation where acceptable, or optimistic concurrency with version columns.
- Retry on deadlock with backoff, since avoiding all cycles in a busy system is unrealistic.
Deadlocks differ from lock waits: a long wait is usually contention, while a deadlock is a genuine cycle. Monitor deadlock graphs and index scans to find hot spots.
Candidate Response Strategy & Interview Tips
- Start with a concise one-sentence summary: Deliver a direct, confident answer first before expanding into nuances.
- Demonstrate real-world trade-offs: Discuss where this approach excels and when you would avoid it in production systems.
- Discuss complexity & edge cases: Proactively explain time/space complexity or boundary conditions (null values, scale limits).
- Prepare for interviewer follow-ups: Technical hiring panels frequently probe deeper into concurrency, backward compatibility, or alternative libraries.