ConcurrencyIntermediate
What causes a deadlock and how do you prevent it?
“Explain deadlocks and your prevention strategy.”
What this tests
- Wait-for cycles
- Lock ordering
- Retry
Answers by level
Read the beginner answer first and notice what is missing.
A cycle in who-waits-for-whom: A holds row 1 and wants row 2, B holds row 2 and wants row 1. The database detects the cycle and aborts one transaction with 40P01, releasing its locks. It is detection, not prevention.
Prevent it by acquiring locks in a consistent order (e.g. ascending id), keeping transactions short, and never holding a lock across a network call. Treat the error as retryable.
Green flags · Red flags
Strong green flag · Says "never hold a lock across a network call".
Green flags
- Describes the cycle
- Consistent lock order
- Retries 40P01
Red flags
- "The database handles it" with no prevention
- Surfaces the error to the user
Follow-up questions
F1
Two transfers touch accounts 1 and 2 in opposite orders. How do you stop the deadlock?
Scenario
Checkout throws 40P01 under peak load but works when quiet. Diagnose and fix.