high riskservice affecting~30 min

Runbook: Diagnose and Clear a Lock Queue

1 · Prerequisites

Confirm every item is in place before any state change.

A database connection that is not itself blocked, which on a busy cluster may require a reserved connection slot
Authority to cancel or terminate sessions, and a rough idea of what each application does when its statement is cancelled
Knowledge of what change is in flight: a migration, a maintenance job, or a deploy — because the head of the queue is usually deliberate work
The ability to reach whoever owns the blocking session, since terminating somebody else work without telling them is how trust is lost
A place to record what was terminated and why

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

· Confirm this is a lock problem. SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE state = 'active' GROUP BY 1,2 ORDER BY 3 DESC; Lock dominating is a queue. LWLock dominating is internal contention, usually from too many connections, and terminating sessions will not help.
· Find the head of the queue, not the victims. SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, now() - state_change AS in_state, left(query,60) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0; A session with an empty blocked_by that appears in others' lists is the root.
· Read the blocker's state before deciding anything. active means it is doing work; idle in transaction means it is holding locks and doing nothing. These deserve different treatment and the difference is one column.
· Check how long the queue has existed. now() - state_change on the blocked sessions. A queue seconds old may clear on its own; one minutes old will not.
· Check whether the queue is growing. Run the query twice, thirty seconds apart. A stable queue behind a long-running statement is different from one growing behind a lock nobody will release.
· Identify the application behind the blocker. application_name, client_addr and usename. Terminating an unidentified session is a decision made without information.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1Take a full picture before acting. Save the output of the blocking query and of pg_locks joined to pg_stat_activity. Once you terminate something, the evidence about why the queue formed is gone.
2Read the lock modes involved. SELECT l.pid, l.locktype, l.relation::regclass, l.mode, l.granted FROM pg_locks l WHERE l.relation = '<table>'::regclass ORDER BY l.granted DESC, l.pid; An ungranted AccessExclusiveLock in the middle of the list is the classic shape: something heavy is waiting, and everything behind it is waiting for that.
3Understand that a queued heavy lock blocks readers. A SELECT needs AccessShareLock, which conflicts with nothing except AccessExclusiveLock. But a queued AccessExclusiveLock sits in front of subsequent lock requests, so readers arriving after it wait — even though the lock they want conflicts with nothing currently held.
4Decide whether the blocker should finish or be stopped. A migration two minutes from completion is usually worth waiting for. A forgotten idle in transaction session is not.
5Prefer cancelling to terminating. SELECT pg_cancel_backend(pid); ends the current statement and leaves the connection alive. pg_terminate_backend(pid) closes the connection entirely, which some connection pools handle badly.
6Note that cancelling does nothing to an idle session. pg_cancel_backend cancels a running statement; a session that is idle in transaction has no statement to cancel. For those, pg_terminate_backend is the only option, and it rolls back the open transaction.
7Act on the head of the queue only. Terminating blocked sessions clears the symptom for a moment and they return. The queue drains on its own the instant the blocker releases.
8Watch the queue drain. Re-run the blocking query. It should empty within seconds. If it does not, there is a second blocker behind the first.
9Tell whoever owned the terminated session. A migration that was cancelled needs to be re-run deliberately, and a team that discovers this from an error message rather than from you will work around the database next time.
10Look for the second-order damage. A cancelled CREATE INDEX CONCURRENTLY leaves an invalid index behind; a cancelled VACUUM keeps nothing and starts over. Check for both.
11Record what happened: the blocker, its state, how long the queue existed, what was cancelled or terminated, and what the root cause was.

4 · Verification

Confirm the procedure actually fixed the problem.

✓SELECT count(*) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0; returns zero.
✓Application latency and error rate have returned to normal, checked from the application's monitoring rather than from the database.
✓Connection count has fallen back. A lock queue fills the pool with blocked sessions, and services that never touch the affected table are affected through the pool.
✓The terminated session's owner has been told, and knows whether their work needs re-running.
✓If a concurrent index build was cancelled, SELECT c.relname, i.indisvalid, i.indisready FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid WHERE NOT i.indisvalid; has been run and its result acted on.
✓The root cause is recorded — not "a lock queue formed", but what took the lock and why it was held that long.

5 · Rollback

If verification fails, undo the procedure in reverse order.

↶A cancelled statement cannot be un-cancelled. The work must be re-run, and whoever owns it needs to know.
↶A terminated session's open transaction was rolled back. Any work it had done is gone, which is usually the intended outcome and occasionally not.
↶If a migration was cancelled partway, check what it had already applied before re-running it. A migration tool that is not idempotent will fail or duplicate on a second run.
↶If a CREATE INDEX CONCURRENTLY was cancelled, it leaves an index with indisvalid = false. If indisready is also true it is being maintained by every write and used by nothing: REINDEX INDEX CONCURRENTLY finishes it, DROP INDEX CONCURRENTLY removes it.
↶If a VACUUM was cancelled, nothing is lost and nothing is kept — the next run starts from the beginning of the table. On a large table that matters, because a repeatedly cancelled vacuum never completes.
↶If the wrong session was terminated, there is no undo. Say so, in the incident record, rather than hoping nobody notices.

6 · Escalation

When the runbook isn't enough, contact:

· The blocker is a migration owned by another team and cancelling it would leave the schema half-changed: escalate to that team before acting. A partially applied migration is worse than a lock queue.
· The queue reforms immediately after being cleared: escalate to the application owner. Something is retrying, and terminating sessions in a loop is not a fix.
· The blocker is idle in transaction and belongs to an application that keeps producing them: escalate. idle_in_transaction_session_timeout is the durable fix and it is a configuration change with an owner.
· Waits are dominated by LWLock rather than Lock: escalate to whoever owns capacity. That is internal contention from too many concurrent connections, and terminating sessions treats the symptom while the pool refills them.
· The lock queue formed behind a VACUUM FULL during business hours: escalate to the change owner and cancel it. That statement takes ACCESS EXCLUSIVE for its whole duration and should not run against a live table.
· You cannot get a connection because the pool is full of blocked sessions: escalate for a reserved connection, or use SIGTERM against a single backend process from the host — never SIGKILL, which forces a cluster-wide crash recovery.

A lock queue has one cause and many victims. The work is finding the cause, and the temptation is to act on the victims — because they are the ones producing errors.

Find the head of the queue

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       now() - state_change AS in_state,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY in_state DESC;

The session at the root has an empty blocked_by and appears in other sessions’ lists. That is the one to act on. Terminating anything else clears the symptom for a moment and it returns.

Why one idle session stops a whole table

A queued heavy lock blocks lock requests that arrive after it

  pid  |        state        | wait_event_type | wait_event |            query             
-------+---------------------+-----------------+------------+------------------------------
 11253 | idle in transaction | Client          | ClientRead | SELECT count(*) FROM orders;
 11269 | active              | Lock            | relation   | VACUUM FULL orders;
 11287 | active              | Lock            | relation   | SELECT count(*) FROM orders;
 11293 | active              | Lock            | relation   | SELECT count(*) FROM orders;

  pid  |        mode         | granted 
-------+---------------------+---------
 11253 | AccessShareLock     | t
 11269 | AccessExclusiveLock | f
 11287 | AccessShareLock     | f
 11293 | AccessShareLock     | f

AccessShareLock — what every SELECT takes — conflicts only with AccessExclusiveLock. So pids 11287 and 11293 do not conflict with 11253 at all.

They are waiting because 11269’s queued AccessExclusiveLock sits in front of them. One forgotten idle in transaction session, one heavy lock request, and every subsequent reader is behind both.

The queue grows for as long as the idle session stays open, and the statement that formed it — measured at 159 ms of actual work — took eleven minutes.

Cancel or terminate

	`pg_cancel_backend`	`pg_terminate_backend`
Effect	Ends the current statement	Closes the connection
On an `active` session	Works	Works
On `idle in transaction`	Does nothing — there is no statement	Works; rolls back the transaction
Cost to the client	An error on one statement	A dropped connection; some pools handle it badly

Prefer cancelling. Reach for terminating when the session is idle in transaction, because cancelling has nothing to cancel.

Blast radius

Action	Reversible?	What it costs if wrong
Reading `pg_locks` and `pg_stat_activity`	Yes	Nothing
`pg_cancel_backend` on the blocker	No	That statement’s work; it must be re-run
`pg_terminate_backend` on the blocker	No	The whole open transaction, plus a dropped connection
Terminating a migration partway	No	A half-applied schema change
Cancelling `CREATE INDEX CONCURRENTLY`	Leaves wreckage	An invalid index maintained by every write
Cancelling a `VACUUM`	No progress kept	The next run starts from the beginning of the table
`kill -9` on a backend	No	Cluster-wide crash recovery

After the queue drains

Two checks people skip:

-- did a cancelled concurrent build leave something behind?
SELECT c.relname, i.indisvalid, i.indisready,
       pg_size_pretty(pg_relation_size(c.oid))
FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid
WHERE NOT i.indisvalid;

And tell whoever owned the terminated session. A team that learns their migration was cancelled from an error message rather than from you will route around the database next time.