Lock queues

PostgreSQL’s lock conflict matrix is the part everyone learns: SELECT takes ACCESS SHARE, ALTER TABLE takes ACCESS EXCLUSIVE, and those two conflict. Many readers coexist happily; a writer of the schema excludes everyone.

The part that causes outages is the queue.

Locks are granted in request order, and a new request that conflicts with an already-waiting request must wait behind it. It does not matter that the new request would be perfectly compatible with what is currently held:

session A   ACCESS SHARE        held        — an idle transaction, holding, doing nothing
session B   ACCESS EXCLUSIVE    waiting     — ALTER TABLE, blocked by A
session C   ACCESS SHARE        waiting     — a plain SELECT, compatible with A,
                                              blocked by B, which holds nothing at all

Session C is stopped by a lock that has not been granted. Every subsequent reader joins the same queue, so one idle transaction plus one DDL statement takes a table completely offline — while the database itself does no work at all. CPU and I/O near zero is consistent with a total outage here, and that combination is otherwise so unusual that it is worth treating as a signature.

Seeing it

SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start;
 
SELECT pg_blocking_pids(pid), pid, left(query, 60) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

The head of the queue is the interesting row, and it is usually not the one anyone is looking at: the DDL is the visible blocker, but the thing blocking the DDL is generally a session in idle in transaction that has been sitting there for a very long time doing nothing.

Bounding it

lock_timeout before every DDL statement. One line, and it converts the failure from an outage into a failed migration:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN discount_cents integer;
-- ERROR:  canceling statement due to lock timeout

A migration that fails is retried five minutes later at no cost. A migration that waits builds a queue behind it for as long as it waits — see a table that went offline behind a statement that never ran.

idle_in_transaction_session_timeout removes the usual blocker class outright, and is worth setting per role for anything interactive or analytical:

ALTER ROLE analytics SET idle_in_transaction_session_timeout = '60s';

Neither setting makes the lock conflict go away. They decide whether an unlucky moment costs one migration or one table.

A queue consumes something besides patience. Every blocked session holds its connection while it waits, so a lock queue drains the connection pool, and the first symptom to reach the application is usually connection exhaustion rather than anything mentioning locks. That is worth expecting in advance, because it sends the investigation to the pool — which will be full of blocked queries and not of leaked ones.