Diagnose PostgreSQL lock waits and deadlocks

Lock wait versus deadlock

• Lock wait: a session waits for another transaction to release a conflicting lock; it may eventually succeed or time out.
• Deadlock: sessions form a wait cycle and none can progress; PostgreSQL detects it and aborts one transaction.

The deadlock SQLSTATE is 40P01. The transaction must roll back; perform a bounded retry only when the entire business transaction is safe to replay.

Inspect the blocking graph

SELECT
  blocked.pid AS blocked_pid,
  blocker.pid AS blocker_pid,
  now() - blocked.query_start AS blocked_for,
  blocked.wait_event_type,
  blocked.wait_event,
  left(blocked.query, 120) AS blocked_query,
  left(blocker.query, 120) AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = b.pid
ORDER BY blocked.query_start;

Check whether the blocker is idle in transaction, what it changed, and whether the application is alive. Do not terminate a PID on sight.

Reduce deadlocks

1. Lock resources in the same order on every code path, such as ascending account id.
2. Keep only database work that must be atomic inside the transaction; exclude user input and external APIs.
3. Index predicates used to locate rows for update, reducing scan and lock scope.
4. Chunk bulk work and keep concurrent workers out of overlapping key ranges.
5. Set evidence-based lock_timeout and statement_timeout values.

BEGIN;
SET LOCAL lock_timeout = '1s';
SET LOCAL statement_timeout = '10s';

SELECT id
FROM accounts
WHERE id = ANY($1::bigint[])
ORDER BY id
FOR UPDATE;

-- bounded writes
COMMIT;

Before terminating a session

pg_cancel_backend(pid) requests cancellation of the current statement. pg_terminate_backend(pid) ends the session and rolls back its transaction. Confirm that:

• the PID still belongs to the target session, not an old screenshot;
• rollback may take time and generate additional I/O;
• the application will not reconnect and repeat the same blocker immediately;
• interrupted work is retryable or has a business compensation path.

See PostgreSQL 18 explicit locking for lock modes and the conflict matrix.