Diagnose and Break a Postgres Lock Wait Chain

Query pg_locks and pg_stat_activity to expose the blocking session and its waiting victims, then decide to terminate, wait, or fix the transaction scope.

~25 mindatabasepostgresoperations

Task

Identify which session holds the lock everyone waits on, measure the queue depth, and clear the blockage safely.

When to use this

The API slows to a crawl. Connections pile up, and every request waits on a lock held by one long-running transaction nobody remembers starting.

Prerequisites

Postgres superuser or pg_monitor privileges to inspect activity
Permission to terminate a backend after confirming it is safe
A reproduction of concurrent writes in a test environment

Steps

1. List who waits on whom right now.

SELECT blocked.pid AS waiting_pid,
       blocked.query AS waiting_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       now() - blocking.query_start AS held_for
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Expected output: One row per waiter with the blocker, its query, and how long it has held the lock, for example a manual UPDATE open in a psql session for 20 minutes.

2. Measure the total queue depth.

SELECT count(*), state FROM pg_stat_activity WHERE state = 'active' GROUP BY state;

Expected output: A count of active waiters far above normal quantifies the incident scope.

3. Check whether the blocker is doing work or sitting idle.

SELECT pid, state, now() - state_change AS idle_for, query FROM pg_stat_activity WHERE pid = <blocking_pid>;

Expected output: A session in idle in transaction for twenty minutes is an abandoned transaction, safe to terminate after review.

4. Terminate the confirmed blocker.

SELECT pg_terminate_backend(<blocking_pid>);

Expected output: The waiters proceed immediately, and connection pressure on the API drops back to baseline.

5. Prevent recurrence by setting an idle-transaction timeout.

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';
ALTER SYSTEM SET lock_timeout = '10s';

Expected output: Abandoned transactions are killed after sixty seconds, and any statement waits at most ten seconds for a lock before failing loudly.

Troubleshooting

Problem

pg_blocking_pids returns empty but waits persist

Solution

The wait may be on an advisory lock or lightweight latch rather than a heavyweight lock; check pg_locks with granted = false and the wait_event column of the waiters.

Problem

Same session keeps becoming the blocker

Solution

That code path opens a transaction, does slow external I/O, then commits; move the external call outside the transaction or shrink the transaction to strictly database work.

Problem

Terminating the blocker rolls back needed work

Solution

Confirm the transaction is truly abandoned before terminating; otherwise prefer waiting with lock_timeout on the victims and alerting on the blocker runtime.

Limitations

Inspecting activity requires privileges that application roles rarely hold; use a monitoring role for diagnosis.
Terminating backends is an incident response, not a fix; the underlying transaction scope defect still needs a code change.

Find the head of the snake

A lock queue looks like a hundred sick requests but is usually one healthy-looking villain: a session holding a lock while doing nothing. The pg_blocking_pids join turns the pile of waiters into a readable table of who blocks whom and for how long. Once the head of the chain is visible, the decision to wait, terminate, or fix becomes obvious, and the idle-transaction timeout prevents the next abandoned session from repeating the incident.

SELECT blocked.pid AS waiting_pid,
       blocking.pid AS blocking_pid,
       now() - blocking.query_start AS held_for
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Idle in transaction is the usual suspect

The classic cause is code that opens a transaction, calls a slow external API, and commits only after the response. Every other writer queues behind that lock while the application does network I/O it never needed to hold a lock for. Moving external calls outside transactions removes the entire class of incident.

Timeouts are seatbelts, not fixes

idle_in_transaction_session_timeout kills abandoned sessions automatically, and lock_timeout makes victims fail fast instead of queuing silently. Both convert a slow-motion outage into a visible, alertable error. They are excellent defaults, but the blocker that legitimately needs ten minutes still deserves a code-level fix.