PostgreSQL — Queries Pile Up Waiting on Locks

Diagnose PostgreSQL statements blocked behind a long-running lock holder using pg_locks joins, find the blocking query and session, and fix the holder pattern.

intermediatedebuggingpostgresqllocksperformance

Symptoms

Queries that normally take milliseconds hang for minutes, all starting at the same timestamp
pg_stat_activity shows many sessions in wait_event Lock with the same blocking pid
An application deploy or maintenance transaction coincides with the pile-up

Possible causes

A transaction holds row or table locks while doing slow work (an API call or sleep inside the transaction)
A migration ran ALTER TABLE without timeout inside a long transaction, queuing all later access
Lock queue ordering is strict: even incompatible-but-short requests wait behind the head waiter
A session left idle in transaction holds locks acquired earlier

Diagnosis steps

Find who blocks whom

psql -c "SELECT w.pid AS waiting, w.query AS waiting_query, b.pid AS blocker, b.query AS blocker_query, b.state FROM pg_stat_activity w JOIN pg_stat_activity b ON w.waiting \|\| '' IS NOT NULL OR true WHERE w.wait_event_type = 'Lock' LIMIT 5;"

Interpretation

The classic blocked/blocker join names both sides; one blocker pid with many waiters is the queue head.

Inspect the lock types and mode

psql -c "SELECT relation::regclass, mode, granted, pid FROM pg_locks WHERE NOT granted;"

Interpretation

Not-granted rows are the waiters; cross-reference granted rows of the same relation to see which mode the head holds.

Check the holder session state

psql -c "SELECT pid, state, xact_start, query_start, query FROM pg_stat_activity WHERE pid = <blocker>;"

Interpretation

idle in transaction with an old xact_start is the smoking gun: locks held with no work happening.

Root cause

PostgreSQL lock queues are strict FIFO per lock mode: a waiting request blocks everything behind it, including requests whose modes would conflict only with the waiter, not the holder. One long-running holder therefore converts into a pile-up of unrelated queries that all started after it.

Fix

Terminate or let the blocking transaction finish consciously, move slow work (HTTP calls, sleeps, file IO) out of transactions, set lock_timeout and statement_timeout for migrations and interactive paths, and alert on idle-in-transaction sessions older than a threshold.

Risk note

Killing sessions forcibly rolls back their work. Prefer canceling the query; terminate only when the holder is stuck or unauthorized.

Verify the fix

Re-run the workload with slow work moved out of transactions; assert no wait_event Lock pile-ups and bounded queue depths under load.

Symptom walkthrough

The pile-up has a distinctive shape: many sessions in wait_event Lock, one shared blocking pid, all waiting since the same instant. Run the diagnostic join:

SELECT w.pid            AS waiting_pid,
       w.query          AS waiting_query,
       w.wait_event_type,
       b.pid            AS blocker_pid,
       b.state         AS blocker_state,
       b.xact_start    AS blocker_xact_start,
       b.query         AS blocker_query
FROM pg_stat_activity w
LEFT JOIN pg_stat_activity b
  ON w.waiting;  -- or use pg_blocking_pids(w.pid)
WHERE w.wait_event_type = 'Lock';

One blocker with state idle in transaction and an xact_start from ten minutes ago is the classic culprit: it holds locks while doing literally nothing. Check the mode pairing too — the queue rule that surprises everyone: if a SHARE request waits behind an exclusive holder, later SHARE requests that would be compatible with each other still queue behind the first waiter.

Fix in practice

Short-term, clear the head of the queue deliberately:

SELECT pg_cancel_backend(blocker_pid);    -- cancel current statement
SELECT pg_terminate_backend(blocker_pid); -- last resort

Long-term, change the holder patterns:

# slow work OUTSIDE the transaction:
data = fetch_from_api()          # seconds, no locks held
conn.begin()
apply_update(conn, data)         # milliseconds, locks held briefly
conn.commit()

Bound the blast radius of maintenance paths:

SET lock_timeout = '5s';
SET statement_timeout = '30s';
ALTER TABLE orders ... ;   -- fails fast instead of queueing everything

And keep a watchdog query (or pgwatchdog-style alerting) on idle-in-transaction older than 60 seconds, so the pattern pages someone before customers notice.