ERROR: deadlock detected - Resolving PostgreSQL Deadlocks & Connection Exhaustion
Fix PostgreSQL deadlocks (ERROR: 40P01) and connection pool exhaustion. Learn to trace lock contention, enforce consistent lock ordering, and optimize transacti
- Deadlocks occur when concurrent transactions attempt to acquire conflicting locks in different orders, creating a cyclical dependency.
- Identify blocking queries using the pg_stat_activity and pg_locks system views or by analyzing logs with log_lock_waits enabled.
- Prevent deadlocks architecturally by strictly enforcing consistent lock ordering (e.g., sorting row IDs before batch updates) across the application.
- Handle unavoidable deadlocks gracefully by implementing transaction retry logic with exponential backoff on SQLSTATE 40P01.
| Method | When to Use | Time | Risk |
|---|---|---|---|
| Consistent Lock Ordering | Best long-term architectural fix for complex workflows | High | Low |
| Shortening Transactions | When transactions perform unnecessary non-DB work | Medium | Medium |
| Application Retries | When deadlocks are rare, unpredictable, but unavoidable | Low | Low |
| Adjusting deadlock_timeout | To fail faster and release locks sooner (not a root fix) | Low | Medium |
Understanding the Error
A deadlock in PostgreSQL happens when two concurrent transactions are waiting for each other to release locks. Because neither can proceed, the database engine intervenes after deadlock_timeout (default 1s), killing one of the transactions (the "deadlock victim") to allow the other to complete. You will typically see an error like this in your application or PostgreSQL logs:
ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 54321.
HINT: See server log for query details.
Deadlocks are an application-level architectural issue related to how data is accessed, not a database bug. They often cascade, leading to postgresql connection pool exhausted or postgresql connection refused because blocked queries hold connections open, starving the connection pool.
Step 1: Diagnose
When a deadlock is detected, PostgreSQL logs the event if log_lock_waits is enabled. First, ensure this is turned on in your postgresql.conf:
log_lock_waits = on
deadlock_timeout = 1s
To actively diagnose lock contention before it escalates into a deadlock (or to understand postgresql slow query issues), you must query pg_stat_activity joined with pg_locks. This reveals exactly which transaction is blocking another.
Step 2: Fix
The most effective way to prevent deadlocks is to ensure that all applications access tables and rows in the exact same sequence. For example, if Transaction A updates Row 1 then Row 2, and Transaction B updates Row 2 then Row 1, a deadlock is highly likely if they run concurrently.
Always update tables in a consistent alphabetical order, or sort primary keys before issuing batch updates. Additionally, keep your transactions as brief as possible. Never make external API calls or perform heavy computation while a database transaction is open.
Handling Related Errors
PostgreSQL Too Many Connections: Often caused by connection leaks or slow queries holding locks. Implement a connection pooler like PgBouncer and set sensible statement_timeout limits.
PostgreSQL Out of Memory: Check work_mem. Over-allocating this per-connection setting can lead to OOM kills, especially during heavy sorting or hashing.
PostgreSQL Table Lock: Avoid explicit LOCK TABLE commands unless strictly necessary. Rely on PostgreSQL's MVCC and row-level locks.
Frequently Asked Questions
-- Diagnostic query to identify blocking and blocked queries
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 seasoned Site Reliability Engineers and Database Administrators specializes in diagnosing and resolving complex infrastructure bottlenecks and database performance issues.