DDL Behind a Lock Queue: Why One Slow Query Makes ALTER TABLE Stall Every Reader, and the lock_timeout Retry Loop That Fixes It

The outage that a one-second migration causes

A nightly report runs a thirty-second SELECT over the orders table. Five seconds in, a deployment runs ALTER TABLE orders ADD COLUMN note text. The ALTER needs an ACCESS EXCLUSIVE lock, which conflicts with the ACCESS SHARE lock the report holds, so it waits. That part is expected. The surprise is what happens to every other query: new SELECTs on orders, which only need ACCESS SHARE, also stop. The PostgreSQL lock manager tracks not only the modes already granted on an object but also the modes that waiting requests have asked for, and a new request that conflicts with an earlier waiter waits too. The lock manager README describes both masks, and the pg_locks documentation refers directly to lock wait queues. Without that rule a stream of readers could starve a writer forever; with it, a queued ACCESS EXCLUSIVE blocks every later ACCESS SHARE.

Each blocked reader holds a connection. At four readers per second a forty-connection pool is empty in about ten seconds, and from then on the application fails fast with pool errors, for the rest of the report plus the time the ALTER itself takes. The migration took one second of lock time and caused twenty-five seconds of outage. The explicit locking documentation states the underlying fact plainly: a transaction seeking a table-level lock waits indefinitely for conflicting locks to be released, which is why holding transactions open for long periods is a bad idea.

Lock levels per ALTER TABLE subform

Two sentences from the ALTER TABLE page govern the whole table: an ACCESS EXCLUSIVE lock is acquired unless explicitly noted, and when multiple subcommands are given, the lock acquired is the strictest one required by any subcommand. A migration tool that bundles an index option change with an ADD COLUMN in one statement gets ACCESS EXCLUSIVE for both. The conflict table in the explicit locking page is the other half: ACCESS SHARE conflicts only with ACCESS EXCLUSIVE, and SHARE UPDATE EXCLUSIVE does not conflict with ACCESS SHARE or ROW EXCLUSIVE, so readers and writers never notice a VALIDATE CONSTRAINT.

Worked example with numbers

Take the scenario above with concrete figures: a 30 second report, the ALTER issued at 5 seconds, readers arriving four per second and each taking one second, a pool of 40 connections. Without lock_timeout the ALTER waits 25 seconds. Readers queue at four per second; after about nine and a half seconds 38 are waiting, the report and the ALTER hold the other two connections, and every reader from roughly second 15 to second 31 fails with a pool error. When the report ends at 30 seconds the ALTER acquires the lock, holds it for a second, and releases a burst of queued readers that all run at once.

Now set lock_timeout to 2 seconds on the migration session and retry every 3 seconds. The ALTER waits two seconds, is cancelled, and tries again at 10, 15, 20 and 25 seconds. Each attempt blocks at most eight readers for at most two seconds. The attempt at 30 seconds finds the report gone, waits for the readers already in flight to finish, holds the lock for one second and completes at about 32 seconds. No reader fails and the longest wait is two seconds. The interactive lab linked below runs this model with adjustable rates, pool sizes and timeouts.

The migration loop in code

Illustrative SQL; waitstart exists in pg_locks from PostgreSQL 14, and pg_blocking_pids is the documented way to find the blockers of a waiting process.

-- Run once per attempt, in the migration session only.
-- Never set these in postgresql.conf; the documentation says so because they would affect all sessions.
SET lock_timeout = '2s';
SET statement_timeout = '0';          -- the DDL itself may legitimately take longer than the lock wait

-- Pre-flight: who holds or waits for locks on the target table right now?
SELECT a.pid, a.state, a.wait_event_type, l.mode, l.granted, l.waitstart,
       now() - a.xact_start AS txn_age, left(a.query, 80) AS query
FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'orders'::regclass
ORDER BY l.granted DESC, l.waitstart;

-- While an attempt is waiting, from another session: which pids block it?
SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE pid = 12345;

Illustrative python, checked for syntax; adapt names and storage to your system.

import random, time

def run_ddl_with_retries(connect, ddl, lock_timeout="2s", attempts=8, backoff=3.0):
    """Each attempt runs in a fresh transaction with lock_timeout set, so a cancelled wait releases the queue."""
    for attempt in range(1, attempts + 1):
        conn = connect()
        try:
            with conn:
                with conn.cursor() as cur:
                    cur.execute("SET lock_timeout = %s", (lock_timeout,))
                    cur.execute(ddl)
            return attempt
        except Exception as e:
            if "lock timeout" not in str(e).lower() and "canceling statement" not in str(e).lower():
                raise                                   # a real error, not a lock wait
            time.sleep(backoff + random.uniform(0, backoff / 3))
        finally:
            conn.close()
    raise TimeoutError(f"{ddl!r} could not acquire its lock in {attempts} attempts")

Three details matter. The lock_timeout is set inside the migration transaction, so it applies only to this statement, as the client configuration documentation recommends. The loop distinguishes a lock timeout from other errors, so a genuine syntax or permission error surfaces at once. And the backoff carries jitter so that several deployment pipelines retrying together do not line up their attempts. The attempt cap turns a stuck migration into a failed deployment, which is the correct outcome when a long transaction never ends; the right fix is then to find that transaction, not to wait longer.

Make the DDL itself short

A lock_timeout loop protects the wait; the hold still has to be short. Prefer subforms with weaker locks where they exist: add constraints as NOT VALID and run VALIDATE CONSTRAINT afterwards, which the documentation says needs only SHARE UPDATE EXCLUSIVE; build indexes with CREATE INDEX CONCURRENTLY and attach them with ADD CONSTRAINT USING INDEX; detach partitions CONCURRENTLY. Avoid the rewriting forms of ADD COLUMN listed in the table, and never run a data backfill in the same transaction as the DDL, because the ACCESS EXCLUSIVE lock is held until the transaction commits. One statement per transaction also keeps the strictest-lock rule from upgrading a harmless change.

Observability and verification

Alert on waiting ACCESS EXCLUSIVE requests: a row in pg_locks with granted false and mode AccessExclusiveLock is the leading indicator, and waitstart tells you how long it has been there. Use pg_blocking_pids rather than joining pg_locks against itself; the documentation warns that the view does not expose queue order. Enable log_lock_waits so that waits longer than deadlock_timeout are logged, and note that PostgreSQL 19 turns it on by default, as described in the PostgreSQL 19 RC 1 guide. To verify a migration procedure, reproduce the scenario in staging: open a transaction that reads the table and leave it idle, run the migration with the loop, and watch readers from a load generator. The migration should fail over to retries and the readers should never exceed the lock_timeout in wait time.

Failure modes

• lock_timeout in postgresql.conf. Application statements that briefly wait for row locks start failing. Set it per session or per role for the migration user only.
• statement_timeout shorter than lock_timeout. The documentation calls this pointless: the statement timeout always fires first and you lose the distinction between a lock wait and slow DDL.
• Idle-in-transaction sessions. An application that opened a transaction and went idle holds ACCESS SHARE forever. Set idle_in_transaction_session_timeout for application roles.
• Migration tools that wrap everything in one transaction. Several statements, one ACCESS EXCLUSIVE lock held for all of them, and the strictest-lock rule applies.
• Retries without an attempt cap. A deployment that retries forever hides a long transaction that someone needs to find.
• Hot standbys. The ACCESS EXCLUSIVE lock is replayed on streaming replicas and can conflict with queries there, governed by the standby delay settings; test the migration against a replica as well.

Practice and related reading

Hands-on: DDL lock queue timeline: blocked readers and lock_timeout retries, an interactive lab on synthetic data.

Further reading:

• PostgreSQL migrations and schema versioning for ADK Java agents
• Deadlock detection in databases
• Database connection pooling
• MVCC in databases
• PostgreSQL 19 RC 1: what changes and how to prepare

Sources and scope

Checked against these sources on 18 October 2026. Examples and figures are illustrative, not measurements.

• PostgreSQL documentation: explicit locking and the lock conflict table
• PostgreSQL documentation: ALTER TABLE, lock level per subform
• PostgreSQL documentation: client connection defaults (lock_timeout, statement_timeout, idle_in_transaction_session_timeout)
• PostgreSQL documentation: pg_locks and pg_blocking_pids
• PostgreSQL source: lock manager README (src/backend/storage/lmgr/README)