DDL lock queue timeline: blocked readers and lock_timeout retries

What this experiment demonstrates

A reporting transaction holds an ACCESS SHARE lock on a table for a number of seconds. Part way through, a deployment issues an ALTER TABLE that needs ACCESS EXCLUSIVE. The DDL cannot be granted until the report finishes, so it waits, and because a conflicting request is already waiting, every new reader that arrives afterwards waits behind it too. Each waiting reader holds a connection from a fixed pool. This page models one table, one long transaction, one DDL with a retry loop, and a steady stream of short readers, and it reports who waited, who failed and when the DDL finished.

The lock rules follow the PostgreSQL documentation: ACCESS SHARE conflicts only with ACCESS EXCLUSIVE, a request that conflicts with an earlier waiter also waits, lock_timeout cancels a statement that waits too long for a lock, and statement_timeout cancels a statement that runs too long for any reason. Everything else is a deliberate simplification.

Run controlled comparisons

The defaults are the recommended practice from the article: a 30 second report, the DDL issued at 5 seconds with a 2 second lock_timeout, a 3 second backoff and up to 8 attempts. Readers wait at most 2 seconds, the pool never drains, and the DDL completes just after the report ends. Now set lock_timeout to 0. The DDL waits 25 seconds, every reader that arrives in that window queues behind it, the 40-connection pool is exhausted within about ten seconds, and the readers that arrive after that fail immediately. Raise the pool to 200 and the failures disappear but the longest wait becomes the whole 25 seconds, which is the same outage seen from the user side.

Give the readers a 5 second statement_timeout with lock_timeout still at 0 and the failures change kind: readers now time out instead of exhausting the pool. Reduce the attempts to 3 with lock_timeout back at 2 and the DDL gives up before the report ends and never completes. Choose the reduced-lock DDL form, which takes only SHARE UPDATE EXCLUSIVE, and no reader is blocked at all. Finally, issue the DDL after the report has finished and only the readers that arrive during the one second it holds the lock wait.

Notice what does not change. The report holds its lock for the same 30 seconds in every run. What decides the outcome is whether the DDL is allowed to sit in the queue while readers pile up behind it, which is exactly what lock_timeout prevents.

Inspect the trace and keep evidence

The timeline shows the long transaction, each DDL wait attempt, the moment the DDL holds the lock, and the periods during which readers were blocked, with the DDL completion marked by a red line. The trace lists the DDL attempts, the first failures with their cause, and the final state. Start trace, Next step, Show all and the slider control how much of the trace is visible; the metrics always describe the whole run. Reset restores the defaults and Export JSON saves the inputs, every reader with its wait and outcome, and the trace.

Limits and production translation

Readers arrive at a fixed rate here and all take the same time; real traffic is bursty and a pool is shared by many tables. The model has one long transaction, one table and one DDL, and ignores deadlock detection, replication and the work the DDL itself does once it holds the lock. Use the page to reason about ordering and budgets, then measure in staging with a deliberately long transaction, pg_locks and pg_blocking_pids, and size lock_timeout, backoff and the attempt limit from what you see.