PostgreSQL Troubleshooting: ERROR: deadlock detected
Comprehensive guide to resolving PostgreSQL 'ERROR: deadlock detected'. Learn how to diagnose lock contention, analyze logs, and implement strict locking orders
- Deadlocks occur when two concurrent transactions hold locks that the other needs, creating a circular dependency.
- PostgreSQL automatically resolves deadlocks by aborting one transaction (the victim) after the deadlock_timeout period.
- The root cause is almost always application-level concurrency design, specifically inconsistent lock acquisition order.
- Diagnose by enabling log_lock_waits and analyzing PostgreSQL server logs to identify the conflicting queries.
- Fix by enforcing a strict, globally consistent ordering for row and table updates across your entire application.
| Method | When to Use | Time | Risk |
|---|---|---|---|
| Consistent Locking Order | Primary prevention method for application design | High (Requires code refactoring) | Low (Permanent fix) |
| Batching / Sorting Updates | When updating multiple rows in a single transaction | Medium | Low |
| Short Transactions | General best practice to reduce lock contention windows | Medium | Low |
| Application Retry Logic | For transient, unpredictable deadlocks in high concurrency | Low | Medium (Can mask underlying design flaws) |
Understanding the Error
The ERROR: deadlock detected in PostgreSQL is a critical concurrency issue. It surfaces when two or more database transactions are indefinitely waiting for each other to release locks. Because this creates a circular dependency, the situation cannot resolve itself naturally. To maintain database availability, PostgreSQL employs a deadlock detector.
When a transaction waits for a lock longer than the deadlock_timeout parameter (which defaults to 1 second), PostgreSQL checks for a deadlock cycle. If a cycle is found, the database forcefully aborts one of the involved transactions—referred to as the 'victim'. The victim receives the ERROR: deadlock detected message, and its locks are released, allowing the surviving transaction(s) to proceed.
A typical error in your application logs or PostgreSQL server logs will look like this:
ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321.
Process 54321 waits for ShareLock on transaction 98765; blocked by process 12345.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,2) in relation "users"
Step 1: Diagnose the Root Cause
The most challenging part of fixing a deadlock is identifying the exact queries that caused it. Because deadlocks are transient, you cannot typically catch them "in the act" using real-time monitoring tools before PostgreSQL resolves them.
Enable Logging
To diagnose deadlocks, you must rely on PostgreSQL's logging mechanisms. Ensure that your postgresql.conf has the following settings:
log_lock_waits = ondeadlock_timeout = 1s(or a reasonably low value; do not set this too high as it degrades performance during contention)log_min_error_statement = error(or lower)
With these settings, PostgreSQL will write a detailed trace of the deadlock to its server logs. This trace will include the exact SQL statements executed by all processes involved in the cycle. You must analyze these logs to find the common tables and rows being manipulated.
Step 2: Implement the Fix
Fixing deadlocks requires application-level changes. There is no PostgreSQL configuration toggle that will magically make them disappear.
1. Enforce a Strict Locking Order This is the golden rule of deadlock prevention. If every transaction in your application acquires locks in the exact same sequence, deadlocks are mathematically impossible.
For example, if Transaction A needs to update Table1 then Table2, and Transaction B needs to update Table2 then Table1, a deadlock can occur if they run concurrently. You must refactor your code so that both transactions always update Table1 first, and Table2 second.
2. Sort Rows Before Updating A very common source of deadlocks is updating multiple rows within the same table. If Transaction A updates row ID 5 then ID 10, and Transaction B updates row ID 10 then ID 5, a deadlock will happen.
To fix this, before executing a bulk update or a series of UPDATE statements within a transaction, retrieve the primary keys of the target rows, sort them ascending, and execute the updates in that sorted order.
3. Keep Transactions Short and Fast The longer a transaction remains open, the longer it holds its locks. This dramatically increases the probability of another transaction colliding with it.
- Avoid doing expensive non-database work (like calling external APIs, processing large files, or heavy CPU computations) while inside an active transaction.
- Gather all required data, perform the calculations in your application memory, open the transaction, execute the SQL statements rapidly, and commit immediately.
4. Add Retry Logic
In highly complex, highly concurrent systems, it may be practically impossible to guarantee a strict locking order across hundreds of microservices. In these cases, you must implement robust retry logic in your application code. When the application catches the 40P01 SQLSTATE error code (which corresponds to deadlock_detected), it should wait for a randomized exponential backoff period and safely retry the entire transaction from the beginning.
Frequently Asked Questions
-- Diagnostic Query: Find currently blocked processes and the queries blocking them.
-- This helps identify lock contention hotspots before they become full deadlocks.
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;Error Medic Editorial
Our team of Senior Site Reliability Engineers and Database Administrators specializes in diagnosing complex concurrency issues and providing actionable, production-ready solutions for enterprise infrastructure.