64
What is a deadlock and how do you prevent it?
Level: Advanced — A classic concurrency problem; interviewers want both the definition and prevention strategies.
A deadlock occurs when two or more transactions are each waiting for a resource that the other holds, creating a circular dependency where none can proceed. Both transactions are stuck forever unless something intervenes.
How MySQL handles it: InnoDB has an automatic deadlock detection mechanism. When it detects a deadlock, it rolls back the transaction that made the fewest changes (the "victim"), allowing the other to continue. Your app must then retry the rolled-back transaction.
Classic Deadlock Scenario
Two transactions lock the same rows in opposite order:
| Step |
Transaction A |
Transaction B |
| 1 |
Locks row 1 |
Locks row 2 |
| 2 |
Wants row 2 (waits) |
Wants row 1 (waits) |
| 3 |
Circular wait → DEADLOCK |
Prevention Strategies
- Consistent lock order: Always access tables/rows in the same order across all transactions (the #1 prevention).
- Keep transactions short: Hold locks for the minimum time; commit quickly.
- Use proper indexes: Good indexes lock fewer rows, reducing conflict.
- Lower isolation level: Use READ COMMITTED where appropriate to reduce gap locks.
- Add retry logic: Catch deadlock errors and retry the transaction automatically.
- Access fewer rows: Batch large operations into smaller chunks.
Quick Example — Prevention by Lock Ordering
-- BAD: two transactions lock in opposite order -> deadlock risk
-- Txn A: UPDATE row 1 then row 2
-- Txn B: UPDATE row 2 then row 1
-- GOOD: both always lock in ascending acc_id order
START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE acc_id = 1; -- lower id first
UPDATE accounts SET balance = balance + 500 WHERE acc_id = 2; -- higher id next
COMMIT;
-- Diagnose the most recent deadlock
SHOW ENGINE INNODB STATUS; -- see the LATEST DETECTED DEADLOCK section
Error to recognize: When a deadlock is chosen as the victim, MySQL returns "ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction." — the cue for your app to retry.
Interviewer tip: The one-liner they want — "A deadlock is a circular wait where two transactions each hold a lock the other needs. InnoDB auto-detects it and rolls back a victim. Prevent it with consistent lock ordering, short transactions, good indexes, and retry logic."