How does a Postgres lock queue turn one idle transaction into an outage?

Picture three sessions against an orders table. Session A runs BEGIN, reads from orders, then stalls, perhaps waiting on an HTTP call. It now sits idle in transaction, holding ACCESS SHARE until it commits or rolls back. Session B runs ALTER TABLE orders ADD COLUMN ..., which needs ACCESS EXCLUSIVE. That conflicts with A's lock, so B waits. Session C runs an ordinary SELECT. It only needs ACCESS SHARE, which does not conflict with A's lock, yet C waits too.

time ──────────────────────────────────────────────►
A  BEGIN; SELECT ... ── idle in transaction (holds ACCESS SHARE) ──
B        ALTER TABLE ── waiting for ACCESS EXCLUSIVE ──────────────
C              SELECT ── waiting (queued behind B) ────────────────
D                 SELECT ── waiting ───────────────────────────────

lock queue on orders:  granted [A]  →  waiting [B, C, D, ...]

Postgres grants a lock request only if it conflicts with neither the locks already granted nor the requests already waiting ahead of it. C's ACCESS SHARE conflicts with B's pending ACCESS EXCLUSIVE, so C lines up behind B. This rule prevents B from starving, but it means every new query on orders joins the queue. Connection pools fill, request latency climbs, and the application goes down. The only real fault is an idle transaction.

This article covers blocking chains, not slow queries. No individual statement is expensive, so query-duration dashboards stay quiet until the outage has already started. Backend engineers who write migrations need to understand this failure mode. Platform engineers who own alerting need to detect it while the queue is still short.

-- Reproduce the wait chain with four psql sessions on a test database.

-- Session A: take ACCESS SHARE, then leave the transaction open.
BEGIN;
SELECT count(*) FROM orders;
-- (do not COMMIT; A is now 'idle in transaction')

-- Session B: needs ACCESS EXCLUSIVE and conflicts with A, so it waits.
ALTER TABLE orders ADD COLUMN note text;

-- Session C: needs only ACCESS SHARE, but queues behind B's pending request.
SELECT * FROM orders LIMIT 1;

-- Session D: observe the chain.
-- pg_blocking_pids() reports hard blockers (holding a conflicting lock)
-- and soft blockers (waiting ahead in the queue with a conflicting request).
SELECT pid,
       state,
       wait_event_type,
       pg_blocking_pids(pid)  AS blocked_by,
       now() - xact_start     AS xact_age,
       left(query, 50)        AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
ORDER BY xact_start;

-- Expected shape (pids will differ):
--  A  idle in transaction      blocked_by {}
--  B  active, wait type Lock   blocked_by {A}
--  C  active, wait type Lock   blocked_by {B}  <- soft block: queued behind B

-- Ending A (COMMIT, ROLLBACK, or pg_terminate_backend(<A's pid>))
-- lets B run, then C proceeds.

Reproduces the A/B/C scenario and uses pg_stat_activity with pg_blocking_pids() to show that the SELECT is blocked by the waiting ALTER TABLE, not by the idle transaction that caused the chain.

Who should use these techniques?

The queries, settings, and runbook in this article are for anyone who runs schema changes or carries the pager for a Postgres database that serves live traffic:

  • Backend engineers who write migrations. They decide whether a change takes ACCESS EXCLUSIVE, whether it sets lock_timeout, and whether the migration runner retries. Most queuing outages start in a migration file.
  • Platform engineers and SREs who own alerting. They choose which pg_stat_activity signals to poll and what thresholds page someone. The transaction-age and lock-wait signals described below belong in their dashboards.
  • On-call engineers. During an incident they need the root-blocker query and the cancel-or-terminate decision, not a full understanding of the lock manager.
  • DBAs and database owners. They set role-level defaults such as lock_timeout for the migration role and idle_in_transaction_session_timeout for application roles.

The techniques apply to self-hosted and managed Postgres. pg_stat_activity, pg_locks, and pg_blocking_pids() are available to any role that can see the relevant sessions. Some managed services restrict ALTER SYSTEM and expose server parameters such as log_lock_waits through their own configuration interface instead. In that case, the SQL in the alerting section still works for polling, and the logging settings are set through the provider's mechanism.

The material matters less if your database has low concurrency, if you take scheduled downtime for every schema change, or if no table is busy enough for a queue to form. Most teams outgrow those conditions without noticing, which is usually how they meet this failure mode.

Which Postgres lock modes conflict, and which statements take them?

Every table-level lock is held until the transaction ends, not until the statement finishes. A migration that waits behind an open transaction is waiting on that transaction's commit or rollback. Only a handful of modes come up in day-to-day incidents:

Mode Typically taken by Conflicts with
ACCESS SHARE SELECT ACCESS EXCLUSIVE only
ROW EXCLUSIVE INSERT, UPDATE, DELETE SHARE and every mode stronger than it
SHARE UPDATE EXCLUSIVE VACUUM (non-FULL), ANALYZE, CREATE INDEX CONCURRENTLY, some ALTER TABLE forms such as VALIDATE CONSTRAINT Itself, SHARE, and every mode stronger than SHARE
SHARE CREATE INDEX ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, and every mode stronger than SHARE, but not itself
ACCESS EXCLUSIVE Most ALTER TABLE forms, DROP TABLE, TRUNCATE, VACUUM FULL, CLUSTER Every mode

Two rows explain most surprises:

  • Plain CREATE INDEX takes SHARE. Reads continue, but writes stop for the entire build. Two plain index builds can run on the same table at once, because SHARE does not conflict with itself.
  • SHARE UPDATE EXCLUSIVE conflicts with itself. A manual VACUUM or a concurrent index build will queue behind autovacuum on the same table.

ACCESS EXCLUSIVE blocks even SELECT because the statements that take it change the table's definition or replace its storage. TRUNCATE, VACUUM FULL and CLUSTER swap in a new data file. A column change alters the row format that readers depend on. A reader working from the old catalog state or the old file would see inconsistent data, so Postgres keeps everyone out.

This table is a simplification. The official PostgreSQL documentation on explicit locking has the full eight-mode conflict matrix and the exact lock level for each ALTER TABLE subcommand. Check it before you assume a particular change is cheap.

How does Postgres lock waiting work internally?

Table-level locks are heavyweight locks managed by the lock manager. Each backend that wants to touch a table first requests a lock on that object in a specific mode. The lock manager compares the request against the conflict matrix above, checking both the modes already granted and the requests already queued. If there is no conflict, the lock is granted and the statement proceeds. If there is a conflict, the request joins that object's wait queue and the backend sleeps. While it sleeps, pg_stat_activity shows wait_event_type = 'Lock', and pg_locks shows its row with granted = false.

The lock table lives in shared memory, so every backend sees the same queues. Its capacity is governed by max_locks_per_transaction together with max_connections and max_prepared_transactions. As an optimisation, Postgres records the weak relation locks that ordinary queries take (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE) in a per-backend fast-path area when no conflicting strong lock exists on that relation. pg_locks exposes this through its fastpath column. When a session requests a strong lock, such as ACCESS EXCLUSIVE for an ALTER TABLE, the lock manager transfers the relevant fast-path entries into the main lock table so it can detect the conflict.

Row locks follow a different path. They are recorded in the tuple header on disk, not in the shared lock table. A session that needs a row another transaction has locked waits on that transaction's ID instead. That is why row-level blocking shows up in pg_locks as an ungranted lock on a transactionid rather than on a row.

Two timers matter while a backend waits:

  • deadlock_timeout. After waiting this long, the backend runs the deadlock detector. If it finds a cycle, it cancels one participant with an error. If log_lock_waits is on, the same moment is when a "still waiting" message is written to the log.
  • lock_timeout and statement_timeout. If either is set and expires, the waiting statement is cancelled and its queue entry is removed.

Locks are released only when the holding transaction commits or rolls back. Advisory locks are the exception, since they can be session-scoped and released explicitly. At release, the lock manager walks the wait queue and wakes the requests that can now be granted, in queue order. This is why ending the root blocker resolves an entire chain: B is granted ACCESS EXCLUSIVE, runs, commits, and releases it, and then C and the rest of the queue are granted in turn.

How do you find blocking queries with pg_locks and pg_blocking_pids?

Start with pg_locks, which has one row per lock held or requested. The columns that matter first are pid, mode, granted, and locktype. A row with granted = false is a backend waiting in the queue. For locktype = 'relation', the relation column holds a table or index OID. Join it to pg_class on oid to get a name, or cast it with relation::regclass. Other common types are transactionid, tuple, and advisory. Advisory locks are application-defined, so their meaning lives in your code, not the catalog.

Row-level conflicts are easy to misread. Postgres records row locks on the tuple itself, not in shared memory, so pg_locks has no per-row entries. A session blocked on a row usually appears as an ungranted ShareLock on the other session's transactionid. It may also hold a tuple lock while it waits. To find the cause, look for the transaction holding that ID.

For a readable view, call pg_blocking_pids(pid) for each row in pg_stat_activity where wait_event_type = 'Lock'. Show the blocked pid, the blocker array, query, state, and xact_start. A blocker in idle in transaction with an old xact_start is the classic culprit. A recursive CTE can follow blockers upward until it reaches a pid that is not itself waiting. That pid is the root you act on.

pg_blocking_pids briefly locks shared lock-manager state. Use it for diagnostics, not high-frequency polling. Also, pg_locks shows only heavyweight locks. LWLock contention and I/O stalls appear only as wait events in pg_stat_activity.

-- Map every lock waiter to the root blocker at the top of its wait chain.
WITH RECURSIVE blocked AS (
  -- Call pg_blocking_pids() once per waiting backend; it is a diagnostic, not a poller.
  SELECT pid, pg_blocking_pids(pid) AS blockers
  FROM pg_stat_activity
  WHERE wait_event_type = 'Lock'
),
chain AS (
  -- Anchor: each waiter and its direct blockers.
  SELECT b.pid AS waiter, bp AS blocker, ARRAY[b.pid] AS path
  FROM blocked b
  CROSS JOIN LATERAL unnest(b.blockers) AS bp
  UNION ALL
  -- Step: if a blocker is itself waiting, follow its blockers upward.
  SELECT c.waiter, nb, c.path || c.blocker
  FROM chain c
  JOIN blocked b ON b.pid = c.blocker
  CROSS JOIN LATERAL unnest(b.blockers) AS nb
  WHERE nb <> ALL (c.path || c.blocker)  -- guard against revisiting a pid
)
SELECT DISTINCT
  c.waiter,
  left(w.query, 60)         AS waiter_query,
  c.blocker                 AS root_blocker,
  r.state                   AS root_state,  -- 'idle in transaction' is the usual suspect
  now() - r.xact_start      AS root_xact_age,
  left(r.query, 60)         AS root_last_query
FROM chain c
JOIN pg_stat_activity w ON w.pid = c.waiter
JOIN pg_stat_activity r ON r.pid = c.blocker
-- A root is a blocker that is not waiting on a heavyweight lock itself.
WHERE c.blocker NOT IN (SELECT pid FROM blocked)
ORDER BY root_xact_age DESC NULLS LAST;

A recursive CTE combines pg_blocking_pids() with pg_stat_activity to walk each lock wait chain up to its root blocker and show that blocker's state and transaction age.

What should you alert on before a lock incident becomes an outage?

Alerting should come from pg_stat_activity. It is a cheap per-backend view and does not touch the lock manager, so you can poll it every 10–30 seconds. Track four signals:

  • Sessions waiting on locks: count rows where wait_event_type = 'Lock'.
  • Oldest lock wait: now() - state_change for those waiting sessions. Treat this as approximate.
  • Oldest open transaction: max(now() - xact_start) across client backends.
  • Idle-in-transaction sessions: the count, and the age of the oldest one, where state = 'idle in transaction'.

Transaction age predicts incidents better than query duration. A blocker is often idle: the application opened a transaction, took a row lock, then stalled on an HTTP call or leaked a connection. A slow-query alert never fires because nothing is running. Locks are held until commit or rollback, so the risk shows up in xact_start. A transaction open for several minutes is a lock incident waiting for the next ALTER TABLE.

Polling has limits. Each poll is a snapshot and adds some load. It also misses blocking that starts and clears between samples, even when that blocking adds latency. To cover the gap, enable log_lock_waits. Postgres then logs any wait longer than deadlock_timeout, including the waiting and holding process IDs. Deadlocks need no alert: the detector finds them and cancels one participant. Ordinary blocking chains are never resolved automatically. They grow until someone or a timeout intervenes.

This setup shortens diagnosis. When the page fires, the alert already names the oldest transaction and its PID, and the logs show who waited on whom. You start with the blocker identified.

-- 1) Complement polling: log any lock wait longer than deadlock_timeout.
--    Both settings take effect on reload; no restart needed.
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET deadlock_timeout = '1s';  -- also the threshold for log_lock_waits
SELECT pg_reload_conf();

-- 2) Lightweight poll (every 10-30s). Reads only pg_stat_activity,
--    so it does not touch the lock manager.
SELECT
  -- Sessions currently blocked on a heavyweight lock
  count(*) FILTER (WHERE wait_event_type = 'Lock')            AS lock_waiters,
  -- Approximate: state_change marks when the current state began
  max(now() - state_change) FILTER (WHERE wait_event_type = 'Lock')
                                                              AS oldest_lock_wait,
  -- Long transactions hold their locks until commit/rollback
  max(now() - xact_start)                                     AS oldest_xact_age,
  -- Idle blockers: transaction open, nothing running
  count(*) FILTER (WHERE state IN ('idle in transaction',
                                   'idle in transaction (aborted)'))
                                                              AS idle_in_xact,
  max(now() - state_change) FILTER (WHERE state IN ('idle in transaction',
                                   'idle in transaction (aborted)'))
                                                              AS oldest_idle_in_xact,
  -- PID of the oldest transaction, so the alert names a suspect
  (SELECT pid FROM pg_stat_activity
    WHERE backend_type = 'client backend'
      AND xact_start IS NOT NULL
      AND pid <> pg_backend_pid()
    ORDER BY xact_start
    LIMIT 1)                                                  AS oldest_xact_pid
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND pid <> pg_backend_pid();  -- exclude this monitoring session

Enables log_lock_waits to catch short-lived blocking between polls, and runs a single pg_stat_activity query that returns lock-waiter count, oldest lock wait, oldest transaction age and PID, and idle-in-transaction count and age for alerting.

How should you use lock_timeout for migrations and DDL?

Set lock_timeout on every risky DDL against a busy table. It bounds how long the ALTER TABLE can sit in the lock queue. While it waits there, every later query on that table queues behind it. With a timeout, the migration fails fast with SQLSTATE 55P03 (lock_not_available) instead of stalling traffic.

Scope it with SET LOCAL lock_timeout inside the migration's transaction, so it ends at commit or rollback. Use ALTER ROLE ... SET or ALTER DATABASE ... SET for defaults, such as a strict value for the migration role. Avoid plain session-level SET behind a transaction-mode pooler. Your next transaction may run on a different server connection without the setting, and the setting stays on the original connection for whichever client gets it next.

Setting What it measures What fails Typical use
lock_timeout Wait for each lock acquisition The waiting statement Migrations and DDL on hot tables
statement_timeout Total statement runtime, including lock waits The running statement Capping runaway queries
idle_in_transaction_session_timeout Idle time inside an open transaction The whole session Killing forgotten transactions that hold locks

A short timeout only helps if the tooling retries. Retry the whole transaction with exponential backoff and random jitter, so parallel deploys don't retry in lockstep. Cap the number of attempts, then fail loudly and alert. Without retries and alerting, short timeouts turn into flaky deploys that people learn to rerun by hand. Long timeouts avoid that friction but bring back the queuing outage you set the timeout to prevent.

import random
import time

import psycopg2
from psycopg2 import errors

DSN = "postgresql://migrator:change-me@db.internal:5432/app"
DDL = "ALTER TABLE orders ADD COLUMN note text"
MAX_ATTEMPTS = 6

def run_migration():
    for attempt in range(1, MAX_ATTEMPTS + 1):
        conn = psycopg2.connect(DSN)
        try:
            with conn.cursor() as cur:
                # psycopg2 opens a transaction implicitly; SET LOCAL ends with it,
                # so nothing leaks to other clients through a pooler.
                cur.execute("SET LOCAL lock_timeout = '2s'")
                cur.execute("SET LOCAL statement_timeout = '60s'")
                cur.execute(DDL)
            conn.commit()
            print(f"migration applied on attempt {attempt}")
            return
        except errors.LockNotAvailable:  # SQLSTATE 55P03: gave up waiting in the lock queue
            conn.rollback()
            if attempt == MAX_ATTEMPTS:
                # Fail loudly so CI and alerting notice, instead of a silent manual rerun.
                raise RuntimeError("lock not acquired after max attempts; check for blockers")
            # Exponential backoff with full jitter so parallel deploys don't retry in lockstep.
            cap = min(30.0, 0.5 * 2 ** attempt)
            delay = random.uniform(0, cap)
            print(f"lock_timeout on attempt {attempt}, retrying in {delay:.1f}s")
            time.sleep(delay)
        finally:
            conn.close()

if __name__ == "__main__":
    run_migration()

A migration runner that sets lock_timeout with SET LOCAL inside each transaction, catches SQLSTATE 55P03, retries the whole transaction with capped exponential backoff and jitter, and fails loudly after a fixed number of attempts.

What are the alternatives to blocking DDL, and when should you avoid them?

Timeouts limit the damage. These guardrails remove the long-held locks that cause it.

idle_in_transaction_session_timeout ends sessions that sit inside an open transaction doing nothing. That is the usual cause of the queuing outage. A role-level setting such as ALTER ROLE app SET idle_in_transaction_session_timeout = '30s' keeps it away from humans. Do not apply it globally. It kills interactive admin sessions mid-investigation and batch jobs that hold transactions open between steps. Behind a pooler, the error can appear on an unrelated client's next query.

Keep transactions short in application code. Never make HTTP calls, queue publishes, or other network round trips while a transaction is open. A slow downstream service then holds your row locks for its full latency.

CREATE INDEX CONCURRENTLY idx ON orders (customer_id) avoids blocking writes. It costs extra table scans, cannot run inside a transaction block, and can fail and leave an invalid index. You must drop that index and retry. Skip it for small tables where a plain build finishes in milliseconds.

ADD CONSTRAINT ... NOT VALID followed later by VALIDATE CONSTRAINT splits the work. The add is quick and checks only new rows. The validation scans existing rows under a weaker lock that still conflicts with other DDL and with VACUUM, so schedule it away from maintenance.

Batch large backfills. Run UPDATE ... WHERE id BETWEEN $1 AND $2 in small committed chunks. Each chunk holds locks briefly. The cost is a longer total runtime and a period where the data is partly migrated, which your code must tolerate.

How do you respond when a blocking chain is already causing an outage?

When the chain is already causing an outage, start with the root-blocker query. The goal is the pid at the head of the chain: a session that appears in other sessions' pg_blocking_pids() results but is not waiting on a lock itself. Many sessions in the pile-up are intermediate waiters. Killing one frees nothing, because the root still holds its lock and the next queued session takes the waiter's place. Before acting, check the root's state, xact_start, and query in pg_stat_activity.

pg_cancel_backend(pid) pg_terminate_backend(pid)
Effect Cancels the currently running statement Ends the backend process
Transaction Aborts the running statement. The session stays connected. Rolls back the open transaction
Locks released Only after the client rolls back. Has no effect on an idle in transaction blocker, which keeps its locks. Yes, immediately
Client impact Query error on an open connection Dropped connection

Try cancel first if the root is actively running a statement. Use terminate if the root is idle in transaction or the cancel does not clear the chain.

Both actions have costs. Terminating a batch job discards all uncommitted work, and the job may retry from the beginning. Applications see connection errors, so their reconnect logic needs to handle them. Behind a transaction-mode pooler, a backend pid does not identify an application client. Many clients share each server connection, and client_addr shows the pooler. Terminating that pid can therefore break whichever client is currently using the connection.

After the incident, record the root pid, its query, and the timestamps in the incident log. Add lock_timeout to the migration or DDL path that caused the queue. Tighten idle_in_transaction_session_timeout where long idle transactions serve no purpose.

How can this save engineering time?

The savings come from three places, each tied to a technique above.

Faster diagnosis. Without the root-blocker query, on-call engineers tend to start from symptoms: rising latency, a full connection pool, many sessions marked active. They then work backwards through pg_stat_activity by hand. Many of those sessions are intermediate waiters, and killing them frees nothing. The recursive CTE and the alert that names oldest_xact_pid skip that search. The first thing the responder sees is the session that has to end.

Fewer incidents. lock_timeout on migrations turns a potential outage into a failed deploy step that retries on its own. idle_in_transaction_session_timeout on application roles ends forgotten transactions before a migration queues behind them. Transaction-age alerts flag the risky state while the queue is still empty. Each of these removes an incident instead of shortening it, and a removed incident costs no one any time.

Less deploy friction. A migration runner that retries with backoff and fails loudly replaces the habit of rerunning stuck deploys by hand. It also replaces the habit of scheduling every schema change for off-hours "just in case". Engineers can ship routine DDL during working hours with a bounded worst case: the migration gives up, not the application.

Here is an illustrative estimate, not a measured result. Every input is assumed and should be replaced with your own numbers:

  • Assumed: 4 lock-queue incidents per quarter.
  • Assumed: 3 engineers engaged per incident.
  • Assumed: 60 minutes to identify and end the root blocker without tooling, versus 15 minutes with the root-blocker query and an alert that names the oldest transaction.

Under those assumptions, diagnosis time drops from 4 × 3 × 60 = 720 engineer-minutes per quarter to 4 × 3 × 15 = 180, saving about 9 engineer-hours per quarter. This ignores the larger benefit of incidents that lock_timeout and idle-transaction timeouts prevent outright. It also ignores the cost of the outages themselves. Plug in your own incident history to see whether the setup work, which is mostly a few role settings, one polling query, and a retry wrapper in the migration runner, pays for itself.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres blocking queries locks, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.