PostgreSQL Locks and Deadlocks: Finding the Session That Blocks Everyone

A lock wait is one session holding a lock another needs; the waiter sits in state active with wait_event_type Lock until someone acts. A deadlock is two sessions each holding what the other needs; PostgreSQL's deadlock detector notices within deadlock_timeout (default 1s) and aborts one with ERROR: deadlock detected. Deadlocks resolve themselves; lock waits do not. Production "the database is frozen" is almost always the second kind.

Run this as a superuser (or a role with pg_monitor). It shows every waiting session together with the session blocking it, including the blocker's current statement and how long it has been idle in transaction:

SELECT
  blocked.pid            AS blocked_pid,
  blocked.query          AS blocked_query,
  blocking.pid           AS blocking_pid,
  blocking.state         AS blocking_state,
  blocking.query         AS blocking_query,
  now() - blocking.xact_start AS blocker_tx_age
FROM pg_stat_activity blocked
JOIN pg_locks  wl ON wl.pid = blocked.pid      AND NOT wl.granted
JOIN pg_locks  hl ON hl.locktype = wl.locktype
     AND hl.database IS NOT DISTINCT FROM wl.database
     AND hl.relation IS NOT DISTINCT FROM wl.relation
     AND hl.page IS NOT DISTINCT FROM wl.page
     AND hl.tuple IS NOT DISTINCT FROM wl.tuple
     AND hl.pid <> wl.pid AND hl.granted
JOIN pg_stat_activity blocking ON blocking.pid = hl.pid
WHERE blocked.state <> 'idle';

The dangerous row is blocking_state = idle in transaction with a large blocker_tx_age: someone began a transaction, took a lock, and walked away.

Common origins: an application that opens a transaction, does work, then holds the connection through user think-time or an exception path that never commits; a psql session where someone ran BEGIN, an ALTER TABLE, and went to lunch.

-- find them:
SELECT pid, now() - xact_start AS tx_age, state, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY tx_age DESC;

Always try the graceful cancel first; it interrupts the current query but keeps the connection. Terminate only when cancel does nothing, because termination rolls back the whole transaction and may break the client's connection pooling assumptions:

SELECT pg_cancel_backend(<pid>);      -- Ctrl-C equivalent
SELECT pg_terminate_backend(<pid>);   -- last resort, full rollback

Before terminating a session that holds a lock wanted by an ALTER TABLE queue, remember the second trap below.

ALTER TABLE needs ACCESS EXCLUSIVE. If it queues behind a long-running SELECT, every new query — even simple SELECTs — then queues behind the ALTER. The symptom is a sudden total freeze. In pg_stat_activity you see the ALTER with state active waiting on a lock, and dozens of queries behind it. Fix: terminate or cancel the original long SELECT (the head of the queue), not the pile of victims. Schedule DDL with lock_timeout so it fails fast instead of queueing:

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN region text;

The server log's deadlock report names both processes and both statements. The durable fix is consistent lock and statement ordering across transactions (all code paths touch tables A→B→C in the same order), plus keeping transactions short. Set log_lock_waits = on so waits longer than deadlock_timeout are logged — you get early warning of hot rows before they become incidents.

ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

Set them per role so batch and reporting workloads keep their own limits: ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';

Queues and counters create a single row every transaction updates. Symptoms: lock waits concentrate on one tuple, CPU is fine, and throughput collapses as cores serialise behind the row lock. Confirm with the blocker query — dozens of waiters, one blocker holding the row for milliseconds but constantly. Remedies: process queue items with SELECT ... FOR UPDATE SKIP LOCKED so workers never queue behind each other; batch counter updates; or move the hot counter into Redis and reconcile asynchronously.

Autovacuum takes lightweight locks and never blocks reads — but VACUUM FULL and some ALTER TABLE variants take ACCESS EXCLUSIVE and will freeze a busy table. If a "routine maintenance" script ran VACUUM FULL in production, you have manufactured the queue trap from the previous section. Schedule exclusive operations in windows, and let autovacuum do ordinary maintenance.

• Enable log_lock_waits and alert on its log lines.
• Set idle_in_transaction_session_timeout on every application role.
• Run DDL in maintenance windows with a short lock_timeout and retry logic.
• Keep transactions to the minimum statement count; never hold one across external calls or user input.
• Review connection-pooler settings — pools that reuse connections mid-transaction convert one bad client into a fleet of blockers.