Intermediate LevelSQL & MySQL
What causes SQL Database Deadlocks and how do relational engines resolve them?
❓ Interview Prompt: Explain Database Deadlocks, cyclic lock dependency graphs, InnoDB deadlock detection, and application retry strategies.
A Deadlock occurs when two or more transactions hold locks on resources that the other transactions need, creating a circular dependency loop:
Example Deadlock Loop:
•
Transaction A locks Row 1, requests lock on Row 2.
•
Transaction B locks Row 2, requests lock on Row 1.
Resolution & Prevention:
•
Deadlock Detector: InnoDB automatically detects deadlock cycles, rolls back the transaction with the smallest weight (Deadlock Victim), and throws error '1213'.
•
Prevention: Always acquire locks on tables and rows in the exact same deterministic order across application code paths!
Code Implementation Example
sql
-- Always update rows in consistent primary key order to prevent deadlocks!
-- Good: Order IDs numerically before acquiring locks
START TRANSACTION;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;💡Preventing deadlocks by acquiring row locks in deterministic primary key order.
🎯 Interview Key Takeaway
Enforce deterministic lock acquisition ordering across application code paths to prevent database deadlocks.
#SQL#Deadlocks#InnoDB#Locking#Intermediate
Back to all SQL & MySQL Questions→