Lock queue: why one waiting ALTER TABLE makes every SELECT queue up Intermediate

The problem

A migration adds a column to orders: ALTER TABLE orders ADD COLUMN note text. There is no DEFAULT, so PostgreSQL only changes metadata without rewriting the table; on a dev machine it takes a few milliseconds.

In production, a report has already SELECTed from orders in a transaction that has not finished. The migration waits. Right after that, every API reading orders also waits, and the connection pool runs dry.

The report only reads, the API only reads, and they do not conflict with each other. Why does a “fast” DDL that has not even run yet block SELECTs that have nothing to do with the report?

What to choose (summary first)

Situation	Choose	Condition
DDL needs ACCESS EXCLUSIVE on a hot table	Short lock_timeout + retry with backoff	The migration is safely re-runnable
A session holds a long lock on the table	Find and handle the blocker first	You are allowed to cancel that session
DDL that rewrites the table (type change, volatile default)	Split per expand–contract	The app tolerates the transition phase

Core concepts

Each statement takes a table-level lock on the tables it touches. All 8 modes are covered in table-level locks; here we only need two:

Mode	Taken by	Conflicts with
ACCESS SHARE	Plain SELECT	Only ACCESS EXCLUSIVE
ACCESS EXCLUSIVE	Most forms of ALTER TABLE, DROP TABLE	Every mode

Three things decide the incident:

• Locks are held until the transaction ends: a report that finished reading but has not COMMITted still holds ACCESS SHARE.
• Per the ALTER TABLE docs, the command takes ACCESS EXCLUSIVE “unless explicitly noted”; ADD COLUMN is not among the exceptions.
• A request that has to wait goes into that object’s lock wait queue.

The rules of the queue

The Explicit Locking chapter does not describe queue ordering. Two authoritative sources spell it out:

1. The pg_blocking_pids() docs: a process blocks another if it holds a conflicting lock (hard block), or is waiting for a conflicting lock and is ahead in the queue (soft block).
2. src/backend/storage/lmgr/README (PostgreSQL 17 source): a request is granted immediately only if it conflicts neither with held locks nor with waiting requests; when a lock is released, a waiter is only woken if it does not conflict with waiters ahead of it.

The README sums it up: “conflicting requests are granted in order of arrival”. That way DDL is not starved by a continuous stream of SELECTs cutting in front.

How it works

Who holds the lock, who waits for whom, and what changes when the migration gives up?

Lock queue: ALTER waits for the report, the API's SELECT queues behind ALTER until ALTER times out

◀ Prev▶ PlayNext ▶↺ RestartStep 1/6

Report opens a transaction, reads orders, then sits idle: it still holds ACCESS SHARE until the transaction ends.

State after each step:

Step	Holds lock	Waiting	Change
1	Report: ACCESS SHARE	—	Report becomes idle in transaction
2	Report	Migration (ACCESS EXCLUSIVE)	Hard blocked by Report
3	Report	Migration, API	API soft blocked by Migration
4	Report	API	Migration fails with 55P03, leaves the queue
5	Report, API	—	API is granted ACCESS SHARE
6	API (until done)	—	Report commits

Without lock_timeout (default 0), step 4 never happens: every later SELECT queues until the report ends and the migration finishes.

A fast DDL does not mean a short blocking time

Blocking time = lock wait time + run time. An ADD COLUMN without a default runs in a few milliseconds, but the wait depends on the longest transaction open on the table.

Operational details

• lock_timeout applies to each individual lock request, explicit or implicit; default 0 (off); on expiry the statement is canceled with 55P03.
• Set lock_timeout per migration session/transaction, not in postgresql.conf (which affects every session).
• If statement_timeout is non-zero and ≤ lock_timeout, lock_timeout is pointless because statement_timeout always fires first.
• Calling pg_blocking_pids() too often has a cost: it needs brief exclusive access to the lock manager.
• On each retry, the API can still be blocked for up to lock_timeout. Choose a value based on the API latency you can tolerate.
• Details on statement_timeout, idle_in_transaction_session_timeout, transaction_timeout, log_lock_waits: see lock timeouts and monitoring.

A safe migration flow

Loading diagram…

Find blockers before running:

SELECT pid, application_name, state,
       now() - xact_start AS xact_age, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE pid IN (
  SELECT pid FROM pg_locks
  WHERE relation = 'orders'::regclass AND granted
)
ORDER BY xact_start;

Canceling a blocker is a business decision. For an idle in transaction session, pg_cancel_backend(pid) does not release the lock (tested: the lock stays granted); pg_terminate_backend(pid) closes the connection and rolls back the transaction.

Real-world example

A migration adds a column to orders while a report holds a transaction open

A report opens a transaction at 02:00, reads orders, then hangs because the client is waiting to export a file. The 02:05 migration runs ADD COLUMN without lock_timeout: from then on every API reading orders waits behind the migration, and the 20-connection pool drains.

With lock_timeout = '3s', the API is delayed by at most about 3 seconds per attempt; when retries run out, the pipeline stops and alerts the operator to find the hung report.

Demo on PostgreSQL 17.11 (examples/docker-compose.yml), schema demo_lock_queue, an orders table with 1,000 rows, three psql sessions in the background:

1. report (t=0s): BEGIN; SELECT count(*) FROM demo_lock_queue.orders; then left idle in transaction for 12 seconds.
2. migration (t=1s): ALTER TABLE demo_lock_queue.orders ADD COLUMN note text;
3. api (t=2s): SELECT count(*) FROM demo_lock_queue.orders;

A fourth session observes at t=3s:

SELECT pid, application_name AS app, state,
       pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE application_name IN ('report', 'migration', 'api');

SELECT a.application_name AS app, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'demo_lock_queue.orders'::regclass;

  pid  |    app    |        state        | blocked_by
-------+-----------+---------------------+------------
 17673 | report    | idle in transaction | {}
 17680 | migration | active              | {17673}
 17708 | api       | active              | {17680}

    app    |        mode         | granted
-----------+---------------------+---------
 report    | AccessShareLock     | t
 migration | AccessExclusiveLock | f
 api       | AccessShareLock     | f

The API is blocked by the migration (pid 17680), not by the report: the lock it requests is compatible with the report’s, yet granted = f. That is a soft block.

Timings of two runs (PIDs and milliseconds vary each run):

Run	Migration	API SELECT
lock_timeout = 0 (default)	Succeeds after 10922 ms, when the report commits	Returns 1000 rows after 9914 ms
lock_timeout = '3s'	canceling statement due to lock timeout after 3001 ms	Returns 1000 rows after 2002 ms, report not yet committed

The second run shows the queue clearing as soon as the migration leaves, even though the report still holds its lock. The SQLSTATE (visible with VERBOSITY verbose) is 55P03.

Retry with backoff, tested while the report holds the lock for 5 seconds:

for attempt in 1 2 3 4 5; do
  if psql -v ON_ERROR_STOP=1 \
       -c "SET lock_timeout = '1s'" \
       -c "ALTER TABLE demo_lock_queue.orders ADD COLUMN note text"; then
    echo "attempt $attempt: ok"; break
  fi
  sleep_s=$(( 2 ** (attempt - 1) ))
  echo "attempt $attempt: lock timeout, sleep ${sleep_s}s"; sleep "$sleep_s"
done

ERROR:  canceling statement due to lock timeout
attempt 1: lock timeout, sleep 1s
ERROR:  canceling statement due to lock timeout
attempt 2: lock timeout, sleep 2s
attempt 3: ok

A minimal version: no jitter yet, does not filter specifically on 55P03 (syntax errors are retried too), no alert when retries run out.

Trade-offs

Pros

• lock_timeout caps how long a DDL can block the whole table.

• Retry with backoff gives the migration a chance to run once short blockers finish.

• pg_blocking_pids() reveals soft blocks too.

< slot="cons">

• Each attempt can still block the API for up to lock_timeout.

• A long-lived blocker exhausts the retries and the migration does not run.

• Canceling a blocker can lose the work of a report or batch.

• The migration must be safely re-runnable.

Cons

🎮 Try it

• A migration locks the whole table: the lock queue makes the whole API wait behind one ALTER TABLE.

Production checklist

1. Every migration with DDL sets a short lock_timeout in the same session/transaction.
2. Retry only on SQLSTATE 55P03, with a bounded number of attempts, increasing backoff and jitter; stop and alert when retries run out.
3. Before running, find transactions holding locks on the target table.
4. Set statement_timeout larger than lock_timeout to stop abnormally long DDL.
5. Do not combine DDL with backfill; split table-rewriting DDL per expand–contract.
6. Set idle_in_transaction_session_timeout for the application and report roles so hung transactions do not hold locks forever.

Common mistakes

A common mistake: setting a timeout for the DDL but bundling a backfill into the same transaction.

One migration transaction holds ACCESS EXCLUSIVE throughout the backfill

✕ Common

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
UPDATE orders SET note = '';  -- holds ACCESS EXCLUSIVE until COMMIT
COMMIT;

✓ Prefer

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
COMMIT;
-- Backfill separately, in small batches, one transaction per batch

Why

• The ALTER’s lock is held until COMMIT, so a whole-table UPDATE blocks every SELECT for its entire runtime.
• lock_timeout only caps the wait time, not the hold time.

Misconception	Consequence	What to do
SELECTs never conflict with SELECTs, so they never wait for each other	The API hangs behind a waiting DDL	Understand soft blocks; set lock_timeout for DDL
A report releases its lock once it finishes reading	idle in transaction holds ACCESS SHARE indefinitely	Commit early, idle_in_transaction_session_timeout
statement_timeout alone is enough	If ≤ lock_timeout, lock_timeout is pointless	lock_timeout < statement_timeout

Self-check

1. The API requests ACCESS SHARE, which is compatible with the report’s lock. Why does the demo still show granted = f with blocked_by pointing at the migration?
2. In the lock_timeout = '3s' run, why does the API get its result after about 2 seconds even though the report has not committed?
3. With statement_timeout = '2s' and lock_timeout = '5s' for the migration, which timeout fires first, and why?

Summary

Key takeaways

• A lock request that conflicts with a request waiting ahead of it must also wait (soft block), so one waiting DDL blocks even later SELECTs.
• Migrations set a short lock_timeout, retry only on 55P03, with backoff and a bounded number of attempts.
• Find and handle blockers (idle in transaction) first; split table-rewriting DDL per expand–contract.

References

• PostgreSQL 17 — Explicit Locking: Table-Level Locks: each command’s mode, locks held until the end of the transaction.
• PostgreSQL 17 — Session Information Functions: the hard block and soft block definitions of pg_blocking_pids().
• PostgreSQL 17 — lock_timeout: default, scope, relationship with statement_timeout.
• PostgreSQL 17 — ALTER TABLE: default lock, when ADD COLUMN rewrites the table.
• PostgreSQL source — lmgr README: first-come lock granting and how waiters are woken.