PostgreSQL Deadlock Detected: Complete Troubleshooting Guide for ERROR: deadlock detected

Understanding PostgreSQL Deadlock Errors

A PostgreSQL deadlock occurs when two or more concurrent transactions each hold a lock that another transaction needs to proceed — a circular wait that neither side can break on its own. PostgreSQL's lock manager runs deadlock detection every deadlock_timeout interval (default 1 second) and resolves detected cycles by choosing one transaction as the victim and aborting it.

The aborted transaction's client receives this error:

ERROR:  deadlock detected
DETAIL:  Process 23847 waits for ShareLock on transaction 9182; blocked by process 31204.
         Process 31204 waits for ShareLock on transaction 9183; blocked by process 23847.
HINT:  See server log for query details.

The surviving transaction continues normally. The victim transaction must be retried by the application. The server log — not the client error — contains the full query text for both sides, making log access essential for diagnosis.

Related Errors That Accompany or Mimic Deadlocks

PostgreSQL deadlocks rarely occur in isolation. These errors frequently co-occur or are mistaken for deadlocks:

• FATAL: remaining connection slots are reserved for non-replication superuser connections — postgresql too many connections; forces apps to queue connections and increases deadlock probability by serializing transactions in unpredictable order.
• ERROR: canceling statement due to lock timeout — postgresql timeout from lock_timeout; fires before the deadlock detection cycle completes when a single transaction waits too long for one lock.
• ERROR: could not connect to server: Connection refused — postgresql connection refused; PostgreSQL process is down, often after postgresql crash recovery from OOM or disk-full events.
• FATAL: password authentication failed for user "app" — postgresql authentication failed; common when connection pools reconnect after a server restart with stale credentials or changed pg_hba.conf.
• ERROR: out of memory — postgresql out of memory; low work_mem causes slow query plan choices that hold locks longer, increasing deadlock windows.
• PANIC: could not write to file "pg_wal/000000010000000000000042": No space left on device — postgresql disk full; cascades into replication lag, WAL sender death, and potential postgresql corruption if the crash is unclean.

Step 1: Diagnose the Active Deadlock

Find Blocking and Blocked Sessions

Connect as a superuser and run this query to see the full blocking graph with query text:

SELECT
  blocked.pid            AS blocked_pid,
  blocked.usename        AS blocked_user,
  blocked.application_name,
  blocked.query          AS blocked_query,
  blocking.pid           AS blocking_pid,
  blocking.usename       AS blocking_user,
  blocking.query         AS blocking_query,
  now() - blocked.query_start AS blocked_for
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0
ORDER BY blocked_for DESC;

The pg_blocking_pids() function (PostgreSQL 9.6+) returns all PIDs blocking a given session. If this query returns no rows during a deadlock, it means PostgreSQL already resolved it — check the server log instead.

Enable Lock Wait Logging Immediately

Add these settings to postgresql.conf and apply without a restart:

log_lock_waits = on
deadlock_timeout = 1s
log_min_duration_statement = 1000

SELECT pg_reload_conf();

With log_lock_waits = on, PostgreSQL logs any lock wait exceeding deadlock_timeout — giving you visibility before a full cycle forms. The log entry shows the waiting query, the holder's query, the relation, and the lock mode.

Check the Server Log

# systemd systems
journalctl -u postgresql --since '1 hour ago' --no-pager | grep -i 'deadlock\|lock wait\|ERROR'

# File-based logging
tail -200 /var/log/postgresql/postgresql-$(date +%Y-%m-%d)_*.log | grep -i 'deadlock\|lock wait'

# Find the log directory if unsure
psql -U postgres -c "SHOW log_directory;"

Step 2: Terminate the Blocking Session

Once you have the blocking PID:

-- Cancel active query only (transaction stays open, locks still held)
SELECT pg_cancel_backend(31204);

-- Terminate session entirely (releases all locks immediately)
SELECT pg_terminate_backend(31204);

Use pg_cancel_backend() first — it sends SIGINT and lets the transaction roll back cleanly. If the session is idle in transaction (no active query to cancel), you must use pg_terminate_backend() which sends SIGTERM.

To kill all idle-in-transaction sessions older than 5 minutes in one shot:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes';

Step 3: Fix the Root Cause

Fix 1 — Enforce Consistent Lock Ordering

The canonical deadlock pattern is Transaction A locking rows in order (id=1, id=2) while Transaction B locks them in order (id=2, id=1). Fix by always sorting the set of rows to lock before operating on them:

# BROKEN: order depends on caller, deadlock possible
def transfer(conn, from_id, to_id, amount):
    with conn.cursor() as cur:
        cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", [amount, from_id])
        cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", [amount, to_id])

# FIXED: always acquire locks in ascending ID order
def transfer(conn, from_id, to_id, amount):
    low_id, high_id = sorted([from_id, to_id])
    with conn.cursor() as cur:
        # Lock both rows in consistent order before modifying either
        cur.execute("SELECT id FROM accounts WHERE id = ANY(%s) ORDER BY id FOR UPDATE", [[low_id, high_id]])
        cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", [amount, from_id])
        cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", [amount, to_id])

Fix 2 — Use SKIP LOCKED for Queue Patterns

If deadlocks occur on a jobs or tasks table, replace SELECT FOR UPDATE with:

-- Each worker grabs a row nobody else has locked, zero deadlock risk
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Fix 3 — Shorten Transaction Scope

Long transactions hold row locks for their entire duration. Move non-database work outside transaction boundaries:

# BROKEN: API call inside transaction holds locks for seconds
with conn.transaction():
    row = cur.execute("SELECT ... FOR UPDATE WHERE id = %s", [job_id]).fetchone()
    result = requests.post("https://api.example.com/process", json=row)  # SLOW
    cur.execute("UPDATE jobs SET status='done', result=%s WHERE id=%s", [result, job_id])

# FIXED: minimize lock hold time
with conn.transaction():
    row = cur.execute("SELECT ... FOR UPDATE WHERE id = %s", [job_id]).fetchone()
    cur.execute("UPDATE jobs SET status='processing' WHERE id=%s", [job_id])
# Transaction commits here, lock released
result = requests.post("https://api.example.com/process", json=row)
with conn.transaction():
    cur.execute("UPDATE jobs SET status='done', result=%s WHERE id=%s", [result, job_id])

Fix 4 — Resolve Connection Pool Exhaustion

When postgresql connection pool exhausted occurs, deploy PgBouncer in transaction-pooling mode:

# /etc/pgbouncer/pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 20
server_idle_timeout = 600
log_connections = 0

Update your application's connection string to point at PgBouncer (port 6432) instead of PostgreSQL directly (port 5432). Reduce PostgreSQL's max_connections to match the pool size plus headroom for admin connections.

Step 4: Prevent Recurrence with Configuration

# postgresql.conf — deadlock and lock tuning
deadlock_timeout = 1s                      # Detection interval; lower to 500ms for high-OLTP
lock_timeout = 5s                          # Abort rather than wait indefinitely for any lock
idle_in_transaction_session_timeout = 30s  # Kill abandoned open transactions automatically
log_lock_waits = on                        # Log all waits exceeding deadlock_timeout

# Connection management
max_connections = 100                      # Keep low; use PgBouncer for scale

# Memory tuning (faster queries = shorter lock hold times)
shared_buffers = 4GB                       # 25% of total RAM
work_mem = 64MB                            # Per operation; prevents disk spills on sorts/hashes

# Monitoring
track_activities = on
track_counts = on
log_min_duration_statement = 2000          # Log slow queries (postgresql slow query detection)

Apply without restart where possible: SELECT pg_reload_conf(); picks up most of these. max_connections and shared_buffers require a full restart.

Monitor with These Queries

-- Current deadlock count per database (alert when this increments)
SELECT datname, deadlocks FROM pg_stat_database ORDER BY deadlocks DESC;

-- postgresql replication lag in bytes
SELECT client_addr, state, pg_size_pretty(sent_lsn - replay_lsn) AS lag
FROM pg_stat_replication;

-- Check for postgresql slow query candidates causing lock buildup
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;