Table locks & DDL
PostgreSQL 18 documents table-lock modes for operations on relations. Ordinary SELECT acquires ACCESS SHARE. DROP TABLE, TRUNCATE and many ALTER TABLE forms need ACCESS EXCLUSIVE, which conflicts with every table-lock mode from another transaction. ALTER TABLE specifies exceptions per form. DDL duration is not measured here.
The outage, reproduced
A reads accounts inside a transaction and retains ACCESS SHARE. B's ADD COLUMN waits for ACCESS EXCLUSIVE. C's new SELECT then waits behind B's queued request, as the asserted B-to-A and C-to-B edges show:
A reads accounts inside an explicit transaction and retains its table lock.
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)The migration needs ACCESS EXCLUSIVE, so it waits for A. Expected. But now —
B> ALTER TABLE accounts ADD COLUMN note text;
⏳ B is waiting for a lock…C's new SELECT now waits behind the queued ALTER request.
C> SELECT balance FROM accounts WHERE id = 1;
⏳ C is waiting for a lock…
M> SELECT waiter.application_name AS waiter, blocker.application_name AS blocker
FROM pg_stat_activity waiter
JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(waiter.pid))
WHERE waiter.wait_event_type = 'Lock'
ORDER BY waiter.application_name, blocker.application_name;
waiter | blocker
--------+---------
B | A
C | B
(2 rows)A commits; B's standalone ALTER and C's read then complete.
A> COMMIT;
COMMIT
⏵ B resumes:
ALTER TABLE
⏵ C resumes:
balance
---------
100
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
A's COMMIT lets the standalone ALTER complete, and then C's read completes. This schedule demonstrates a queued DDL request delaying a new read. It does not prove that every later query must wait: a transaction that already has the required lock is a different case.
Bound migration lock waits
Set a suitable session lock_timeout and handle failure. A timed-out DDL request can still delay later requests while it remains queued.
The fix: lock_timeout + retry
A> BEGIN;
BEGIN
A> SELECT balance FROM accounts WHERE id = 1; -- the same long transaction as before
balance
---------
100
(1 row)Same migration — but this time it gives up after 100ms instead of camping in the queue.
B> SET lock_timeout = '100ms';
SET
B> ALTER TABLE accounts ADD COLUMN note text;
ERROR: 55P03: canceling statement due to lock timeoutAfter B's timeout removes its request, C's read returns 100. No read during B's wait is tested.
C> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> COMMIT;
COMMITRetry the migration when it can actually get the lock — now it sails through.
B> ALTER TABLE accounts ADD COLUMN note text;
ALTER TABLE
C> SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'accounts' AND column_name = 'note';
column_name
-------------
note
(1 row)Verified against PostgreSQL 18.6 · Run it yourself · Scenario source
B first waits and gets 55P03. C is dispatched only after that failure, so its successful read demonstrates recovery after the request leaves the queue, not zero disruption during the wait. After A commits, B retries and the asserted column exists. These are standalone DDL attempts; explicit-transaction error recovery needs rollback or a prior savepoint.
The client-setting contract discourages global lock_timeout because it affects every session. It limits each acquisition, not total migration time. Retry success is not guaranteed under continuing contention.
As Documented contracts, SET STATISTICS uses SHARE UPDATE EXCLUSIVE, compatible with ordinary reads and writes but conflicting with some maintenance and DDL. CREATE INDEX without CONCURRENTLY blocks writes but permits reads. CONCURRENTLY permits concurrent table writes but has extra phases, waits, and failure restrictions; it is not a no-wait promise. These variants are not executed in this chapter. Use the monitoring lesson to inspect the actual wait.