Handle database deadlocks
Deadlocks occur when transactions wait on each other's locks. Learn detection, prevention, and retry strategies.
- 1
A deadlock: Transaction A locks row 1, wants row 2. Transaction B locks row 2, wants row 1. Both wait forever. The database detects the cycle and aborts one transaction (usually the one that did less work). The aborted transaction gets a deadlock error and must be retried.
- 2 SQL Formatter →
Format the SQL statements in the transaction to identify the lock acquisition order. If different transactions acquire locks in different orders, deadlocks are likely. Reformat to document the intended lock order.
- 3 Hash Calculator →
Use a hash of the resource IDs to determine a consistent lock order. Always lock resources in ascending hash order. This prevents the circular wait condition that causes deadlocks. Hash-based ordering works when resources can be identified by a string or number.
Frequently asked questions
- How do I prevent deadlocks?
- Acquire locks in a consistent order across all transactions. Keep transactions short (less time holding locks). Use appropriate isolation levels (lower levels hold fewer locks). Use lock timeouts (SET lock_timeout) so transactions fail fast instead of waiting. For batch operations, sort by primary key before processing.
- Should I retry on deadlock?
- Yes, but with limits. Retry 3-5 times with exponential backoff. If the deadlock persists, fail and log for investigation. Track deadlock frequency — a high rate indicates a systemic issue (lock ordering, long transactions) that needs architectural changes, not just retries.
Workflows that solve this
Learn more
Want to understand why?