How to find blocking and deadlocks in PostgreSQL

Blocking or deadlock?

• Blocking: session A holds a lock, session B waits for it. B waits until A commits, rolls back, or B gives up. Nothing resolves it automatically.
• Deadlock: A waits for B and B waits for A. Neither can finish, so PostgreSQL detects the cycle after deadlock_timeout (1 second by default), cancels one of them, and that client gets ERROR: deadlock detected.

Blocking shows up as slow requests and timeouts. Deadlocks show up as errors in the application and the server log. The queries below cover both.

1. Who is blocked, by whom

pg_blocking_pids() returns the sessions that block a given session. It is the simplest reliable starting point.

SELECT a.pid,
       pg_blocking_pids(a.pid) AS blocked_by,
       now() - a.query_start  AS waiting_for,
       a.wait_event_type,
       a.wait_event,
       left(a.query, 120)     AS query
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

2. Find the root blocker

Chains are common: A blocks B, B blocks C. The session to look at is one that blocks others but is not blocked itself.

SELECT b.pid,
       b.usename,
       b.application_name,
       b.client_addr,
       b.state,
       now() - b.xact_start AS transaction_age,
       left(b.query, 120)   AS last_query
FROM pg_stat_activity AS b
WHERE b.pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)
  AND cardinality(pg_blocking_pids(b.pid)) = 0;

3. What is locked

To see which tables are involved, join the waiting locks to the relations they are on.

SELECT l.pid,
       l.locktype,
       l.mode,
       l.relation::regclass AS locked_table,
       l.granted
FROM pg_locks AS l
WHERE l.relation IS NOT NULL
  AND l.pid IN (SELECT pid FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0)
ORDER BY l.pid;

Locks with granted = false are the ones being waited for. An AccessExclusiveLock on a busy table, usually from an ALTER TABLE or a migration, blocks every read and write behind it.

4. Cancel or terminate

Two levels, gentlest first. Replace 12345 with the root blocker's pid.

-- stop the current statement, keep the connection
SELECT pg_cancel_backend(12345);

-- end the whole session and roll back its open transaction
SELECT pg_terminate_backend(12345);

• pg_cancel_backend does not help an idle in transaction session: there is no running statement to cancel. Use pg_terminate_backend.
• Terminating rolls back the session's open transaction. Its locks are released when the rollback completes.
• Both need superuser, the pg_signal_backend role, or the same role as the target session.

5. Reading deadlock errors

When PostgreSQL breaks a deadlock it writes the details to the server log: the processes involved, what each was waiting for, and the statements. Count how often it happens per database:

SELECT datname, deadlocks
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY deadlocks DESC;

Turn on log_lock_waits so PostgreSQL also logs any lock wait longer than deadlock_timeout. That catches serious blocking even when it never becomes a deadlock.

Deadlocks are almost always fixed in application code, not the database:

• Touch rows and tables in the same order in every code path.
• Keep transactions short; don't call external services while holding locks.
• Retry the transaction that received the deadlock error; it was rolled back cleanly.

6. Timeouts that prevent it

Set these per role or per application rather than globally, so batch jobs can keep longer limits.

-- give up waiting for a lock after 5 seconds instead of queueing forever
ALTER ROLE app_user SET lock_timeout = '5s';

-- end sessions that sit idle inside a transaction
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';

-- cap any single statement
ALTER ROLE app_user SET statement_timeout = '30s';

For migrations, set a short lock_timeout before an ALTER TABLE and retry on failure. A migration that waits behind a long query also blocks everything that queues behind it.

The same steps in Quorum

Quorum draws who waits for whom, with the root blocker identified, its transaction age and the program that owns it, and keeps observed facts apart from likely causes.

• Blocking and deadlocks, drawn: who waits for whom, with the root blocker, transaction age and owning program.
• Review before you act: the exact statement, target and side effects before you run it, confirmed once.
• Honest outcomes: confirmed, failed or unknown, never a silent retry, then verified with the same checks.