Database Deadlock Diagnosis
Detect, interpret, and eliminate database deadlocks in PostgreSQL and MySQL production systems.
Overview
Overview
A deadlock happens when two or more transactions each hold a lock the other needs, so neither can proceed. The database detects the cycle, aborts one transaction (the "victim") and lets the other finish. The victim gets an error and the application must retry it.
How PostgreSQL detects it
PostgreSQL does not check for deadlocks on every lock wait. Once a transaction has waited deadlock_timeout (default 1 second), it checks for a circular lock dependency.
- Victim choice: the transaction whose check finds the cycle is the one aborted; you cannot predict which side that will be.
- Logging: the event is logged at ERROR level with a DETAIL section showing exactly which processes waited for which locks.
> [!NOTE] Unlike InnoDB, PostgreSQL does not pick the victim by how much work each transaction has done. Always make both sides retry-safe.
The classic pattern
The most common web-app deadlock is two concurrent transactions updating the same rows in different order.
- 1 — Locks user record 1 — Locks order record 1
- 2 — Wants order record 1, waits for B — Wants user record 1, waits for A
- 3 — Circular wait: deadlock — Circular wait: deadlock
> [!TIP] Enforce one lock acquisition order in code. If every transaction locks user records before order records, this deadlock cannot occur.
Where to read the evidence
- PostgreSQL — ERROR line — The process number of the aborted transaction
- PostgreSQL — DETAIL section — Each wait: "Process X waits for ShareLock on transaction Y; blocked by process Z." Read it as a graph to trace the cycle
- PostgreSQL — HINT line — "See server log for query details."
- MySQL — SHOW ENGINE INNODB STATUS — LATEST DETECTED DEADLOCK section: the SQL of both transactions and the locks each held and waited for
- MySQL — innodb_print_all_deadlocks (MySQL 5.6.15+) — Writes every deadlock to the error log, so it survives restarts for post-incident analysis
Reduce transaction scope
Long transactions hold locks for their whole duration, which widens the deadlock window. A transaction that takes locks and then makes a network call (HTTP request, queue send) holds those locks for the full network latency.
> [!WARNING] Acquire locks, make the database changes, commit, and only then make the external call.
Key concepts
- Deadlock
- A circular lock dependency between two or more transactions where each holds a lock the other needs, requiring the database to abort one transaction to break the cycle.
- deadlock_timeout
- PostgreSQL configuration parameter (default 1s) controlling how long a transaction waits before the engine checks for deadlock cycles.
- Lock Acquisition Order
- The principle that all transactions must acquire locks on the same resources in the same order, eliminating the possibility of circular wait conditions.
- NOWAIT
- A SELECT FOR UPDATE modifier that causes the query to immediately fail with an error rather than blocking if the requested lock is held by another transaction.
- SKIP LOCKED
- A SELECT FOR UPDATE modifier that causes the query to skip rows that are currently locked by other transactions, useful for queue-style processing patterns.
When to use it
- Diagnosing sporadic transaction rollback errors in production with message "deadlock detected"
- Explaining the lock acquisition order fix to prevent recurring deadlocks
- Reading and interpreting PostgreSQL deadlock error logs or MySQL INNODB STATUS
- Designing transaction boundaries to minimize deadlock probability
- Choosing between SELECT FOR UPDATE, NOWAIT, and SKIP LOCKED for concurrent row processing
Interview questions
- How do you read a PostgreSQL deadlock log entry to identify the root cause?
- What is the difference between SELECT FOR UPDATE, NOWAIT, and SKIP LOCKED, and when should you use each?
- What is the canonical application-code fix for deadlocks caused by row lock ordering?
Sign up free to read the full topic
More Troubleshooting topics
Start free on Cariara · Coding problems · System design studio