Diagnosing A Blocked Query Queue

You already ran the detection query above. If it returned no rows, stop -- no sessions are waiting on locks, so this runbook is not your current issue. If it returned one or more rows, work straight down these five steps. Every diagnostic step is READ-ONLY; only Step 4 changes anything, and only under the conditions listed there.

1. Detect active waits -- done above (the detection query).
2. Identify the root blocker -- the one session everyone is waiting on.
3. Confirm its state and business context -- idle, or actually working?
4. Choose a controlled action -- only if the conditions are met.
5. Verify the queue cleared.

What to run. Resolve the blocking chain across client backends and isolate the one session that blocks others but is itself blocked by no one. This is read-only.

SELECT pid, pg_blocking_pids(pid) AS blocked_by, state,
       left(query, 80) AS query_preview
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY array_length(pg_blocking_pids(pid), 1) NULLS FIRST;

CREATE TABLE IF NOT EXISTS t(id int primary key, v int);
NOTICE:  relation "t" already exists, skipping
TRUNCATE t;
INSERT INTO t VALUES (1,1);
BEGIN;
UPDATE t SET v=v+1 WHERE id=1;
BEGIN;
UPDATE t SET v=v+1 WHERE id=1;
BEGIN;
UPDATE t SET v=v+1 WHERE id=1;

----- blocking tree (who waits on whom) -----
 pid | blocked_by |        state        |  wait_event   | xact_age_s |             query
-----+------------+---------------------+---------------+------------+--------------------------------
 150 | {152}      | active              | transactionid |          5 | UPDATE t SET v=v+1 WHERE id=1;
 151 | {150}      | active              | tuple         |          3 | UPDATE t SET v=v+1 WHERE id=1;
 152 | {}         | idle in transaction | ClientRead    |          6 | UPDATE t SET v=v+1 WHERE id=1;
(3 rows)

----- ROOT blocker (head of the queue: blocks others, blocked by none) -----
 root_blocker_pid |     root_state      | root_idle_s |        root_last_query
------------------+---------------------+-------------+--------------------------------
              152 | idle in transaction |          18 | UPDATE t SET v=v+1 WHERE id=1;
(1 row)

ROLLBACK;
ROLLBACK;
ROLLBACK;

What it means. pg_blocking_pids resolves the chain: pid 150 is blocked by {152}, pid 151 by {150}, and pid 152 by {} -- nobody. The session whose blocked_by is empty {} is the root blocker the entire queue waits on. Here it is pid 152, and the second result isolates it directly: idle in transaction, idle for 18 seconds, last statement the UPDATE that took the lock.

What to do next. Write down the root blocker's PID and its state, then go to Step 3 to confirm that state before you consider any action.

What to inspect. You already have the root blocker's state and idle time from Step 2 (the root_state and root_idle_s columns). No new query is needed -- read them against this table before deciding anything:

Also confirm the business context: ending this backend rolls back its open transaction, and the affected application or user must reconnect and retry. Make sure that is acceptable for the workload before moving on.

Terminating a backend is a real intervention, not a default. Confirm every item below before you run it:

• You have identified a single root blocker (its blocked_by is {}).
• You have its exact PID.
• Its state is idle in transaction (Step 3) -- it is holding the lock while doing no work.
• You have checked the business impact -- rolling back its open transaction is acceptable.
• You know the blast radius: pg_terminate_backend ends exactly one backend connection.
• You know the rollback expectation (below) and will run the verification in Step 5.

When all of those hold, terminate that one backend; the queue then drains in order:

CREATE TABLE IF NOT EXISTS t(id int primary key, v int);
NOTICE:  relation "t" already exists, skipping
TRUNCATE t;
INSERT INTO t VALUES (1,1);
BEGIN;
UPDATE t SET v=v+1 WHERE id=1;
BEGIN;
UPDATE t SET v=v+1 WHERE id=1;
BEGIN;
UPDATE t SET v=v+1 WHERE id=1;

----- FIX: terminate the idle-in-transaction root blocker -----
 pid | terminated
-----+------------
 187 | t
(1 row)

COMMIT;
COMMIT;

----- queue drained; final row state -----
 id | v
----+---
  1 | 3
(1 row)

pg_terminate_backend ends one backend connection. Any open transaction in that backend is rolled back, and the affected application or user may need to reconnect and retry. Here pid 187 was terminated (terminated = t): its uncommitted UPDATE rolled back and the row lock released, the next waiter acquired the lock and committed, and the last waiter committed behind it. The final row shows v=3 -- the two queued updates applied and the abandoned change was discarded.

Confirm no waiters remain and the data is consistent:

lock_waiters
--------------
            0
(1 row)

 id | v
----+---
  1 | 3
(1 row)

lock_waiters = 0: pg_stat_activity shows zero backends waiting on a Lock, so the queue is fully drained. The row value (v=3) confirms the two formerly-queued transactions completed and the abandoned transaction's change was rolled back -- no lost or double-counted update.

Why this happens

PostgreSQL row locks are held until the end of the holding transaction -- commit or rollback -- not until the statement finishes. A session that runs UPDATE and then goes idle in transaction keeps its row-level lock for as long as it stays open. Any other UPDATE/DELETE/SELECT FOR UPDATE on that row must wait. Lock requests are served in a FIFO-ish queue, so the second waiter waits behind the first, the third behind the second, and so on: pg_blocking_pids shows this as a chain (C blocked by B and A, B blocked by A, A blocked by no one).

The manual notes that 'UPDATE, DELETE, and SELECT FOR UPDATE ... acquire a row-level lock on the rows ... the lock is held until the transaction commits or rolls back' (Explicit Locking -- Row-Level Locks). The decisive function is pg_blocking_pids(pid), which 'returns the process IDs of the sessions that are blocking the server process with the specified process ID' (System Information Functions). The session at the head of the queue -- the one that blocks others but is blocked by none -- is the root cause.

When that root blocker is idle in transaction, it is doing no work, so terminating it -- after you have confirmed its state (Step 3) -- rolls back only its uncommitted change and releases the lock, draining the queue. When the root blocker is instead actively running a statement, the same termination would discard real work, which is why confirming the state first is the deciding step.

Symptoms to recognize

• A group of identical or related queries all hang at once;
• pg_stat_activity shows several backends with wait_event_type = 'Lock' and state = 'active' that never finish;
• the table or row they touch appears 'frozen';
• connections start backing up and the app may hit its pool limit;
• crucially, one session is NOT waiting -- it is idle in transaction, quietly holding the lock the whole queue is stuck behind.

Quick at-a-glance check (waiters only)

A fast way to confirm a lock queue exists -- which sessions are blocked right now:

CREATE TABLE IF NOT EXISTS t(id int primary key, v int);
TRUNCATE t;
NOTICE:  relation "t" already exists, skipping
INSERT INTO t VALUES (1,1);
BEGIN;
UPDATE t SET v=v+1 WHERE id=1;
BEGIN;
UPDATE t SET v=v+1 WHERE id=1;
BEGIN;
UPDATE t SET v=v+1 WHERE id=1;

----- waiting sessions (which queries are blocked) -----
 pid | state  | wait_event_type |  wait_event   | waited_s |             query
-----+--------+-----------------+---------------+----------+--------------------------------
 124 | active | Lock            | tuple         |        5 | UPDATE t SET v=v+1 WHERE id=1;
 125 | active | Lock            | transactionid |        6 | UPDATE t SET v=v+1 WHERE id=1;
(2 rows)

ROLLBACK;
ROLLBACK;
ROLLBACK;

pg_stat_activity filtered to wait_event_type='Lock' lists the waiting backends and how long each has waited. This confirms you have a lock queue (not a crash, not a slow plan) and how many queries are stuck. To find the single session at the head of the queue, use the root-blocker query in Step 2 above.

Alternative finder (filtered pg_locks join)

An equivalent finder that joins pg_locks to pg_stat_activity and excludes constraint-backed objects. Use whichever form you prefer; both are read-only.

SELECT blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       blocked.pid  AS blocked_pid,
       blocked.query AS blocked_query,
       kl.mode AS lock_mode,
       now() - blocked.query_start AS wait_time
FROM pg_catalog.pg_locks kl
JOIN pg_catalog.pg_stat_activity blocking ON blocking.pid = kl.pid
JOIN pg_catalog.pg_locks blocked_locks ON blocked_locks.locktype = kl.locktype
    AND blocked_locks.DATABASE IS NOT DISTINCT FROM kl.DATABASE
    AND blocked_locks.relation IS NOT DISTINCT FROM kl.relation
    AND blocked_locks.page IS NOT DISTINCT FROM kl.page
    AND blocked_locks.tuple IS NOT DISTINCT FROM kl.tuple
    AND blocked_locks.virtualxid IS NOT DISTINCT FROM kl.virtualxid
    AND blocked_locks.transactionid IS NOT DISTINCT FROM kl.transactionid
    AND blocked_locks.classid IS NOT DISTINCT FROM kl.classid
    AND blocked_locks.objid IS NOT DISTINCT FROM kl.objid
    AND blocked_locks.objsubid IS NOT DISTINCT FROM kl.objsubid
    AND blocked_locks.pid <> kl.pid
JOIN pg_catalog.pg_stat_activity blocked ON blocked.pid = blocked_locks.pid
WHERE NOT kl.GRANTED
ORDER BY wait_time DESC;

blocking_pid |           blocking_query           | blocked_pid |    blocked_state     |           blocked_query
-------------+------------------------------------+-------------+----------------------+----------------------------------
          87 | BEGIN                              |          88 | active               | UPDATE t SET v = v+1 WHERE id=1;
(1 row)

Rollback

Version Matrix (PG 14--18)

The queue and its root-blocker diagnosis reproduce on every supported major version (fresh database per version, same idle-in-transaction holder plus two waiters). pg_blocking_pids, pg_stat_activity.wait_event_type, and pg_terminate_backend behave identically across PG 14 through 18.

Common Mistakes

Frequently Asked Questions

References

• Explicit Locking -- Row-Level Locks (Section 13.3.2)
• System Information Functions -- pg_blocking_pids()
• Server Signaling Functions -- pg_cancel_backend / pg_terminate_backend
• idle_in_transaction_session_timeout (Client Connection Defaults)
• pg_stat_activity (Monitoring stats -- wait_event_type)