PostgreSQL ACCESS EXCLUSIVE Lock: What It Blocks and Why SELECT Waits

ACCESS EXCLUSIVE blocks everything on the table:

Concurrent operation	Typical lock	Blocked by ACCESS EXCLUSIVE?
Plain SELECT	ACCESS SHARE	Yes
SELECT ... FOR UPDATE	ROW SHARE	Yes
INSERT, UPDATE, DELETE	ROW EXCLUSIVE	Yes
VACUUM, ANALYZE	SHARE UPDATE EXCLUSIVE	Yes
CREATE INDEX	SHARE	Yes
Another schema change	Varies	Yes

The PostgreSQL explicit locking documentation states the defining rule: ACCESS EXCLUSIVE conflicts with all table-level lock modes. It is also the only table lock that blocks a plain SELECT.

That makes it the lock to look for when an ordinary read suddenly waits behind a schema migration.

Commands that take ACCESS EXCLUSIVE

The PostgreSQL documentation names these directly:

• DROP TABLE
• TRUNCATE
• REINDEX without CONCURRENTLY
• CLUSTER
• VACUUM FULL
• REFRESH MATERIALIZED VIEW without CONCURRENTLY
• Many forms of ALTER TABLE
• Many forms of ALTER INDEX

The last two categories are where blanket rules become dangerous. Some ALTER TABLE subcommands only change catalog metadata and finish quickly. Others scan or rewrite the full table. Both can request ACCESS EXCLUSIVE, but the time spent holding it is very different.

For example, a constant default on PostgreSQL 11 and newer can make this metadata-only:

ALTER TABLE accounts
  ADD COLUMN status text NOT NULL DEFAULT 'pending';

The statement still needs ACCESS EXCLUSIVE. It can finish in milliseconds once acquired, but acquisition may wait behind a long-running read or an idle transaction.

A volatile default changes the cost:

ALTER TABLE accounts
  ADD COLUMN sampled_at timestamptz NOT NULL DEFAULT clock_timestamp();

That can rewrite existing rows while holding the strongest table lock. Read ADD COLUMN with a DEFAULT: sometimes instant, sometimes catastrophic for the version and volatility distinction.

The queue is often worse than the statement

Suppose session A started a read ten minutes ago and still holds ACCESS SHARE. Session B now requests ACCESS EXCLUSIVE for a short ALTER TABLE. It has to wait.

Session C arrives with a new SELECT. Its ACCESS SHARE lock is compatible with session A, but granting it would let new readers cut ahead of the queued schema change forever. PostgreSQL queues it behind session B. New writes queue too.

The result looks like the migration blocked the table before it acquired the lock. That is the lock queue death spiral: one old transaction, one waiting DDL request, then a growing line of application queries.

A short lock_timeout limits that queue:

SET lock_timeout = '2s';
SET statement_timeout = '5min';

ALTER TABLE accounts RENAME COLUMN state TO status;

lock_timeout applies while waiting to acquire a lock. statement_timeout covers the whole statement. Keep them separate. A two-second statement_timeout would also cancel legitimate work after the lock is acquired.

Find the blocker before deciding what to cancel

Start with the waiting migration:

SELECT
  pid,
  application_name,
  state,
  wait_event_type,
  wait_event,
  query_start,
  query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY query_start;

Then use pg_blocking_pids with its PID:

SELECT
  pid,
  usename,
  application_name,
  state,
  xact_start,
  query_start,
  query
FROM pg_stat_activity
WHERE pid = ANY (pg_blocking_pids(12345));

If customer traffic is already queued behind a migration that has not acquired its lock, canceling the migration is usually the fastest way to drain the queue. Killing the old blocker instead can hand ACCESS EXCLUSIVE to the migration immediately, which starts the real schema work while the application is already backed up.

The incident workflow is covered in more detail in Postgres ALTER TABLE Hangs: Find the Blocker, Then Decide Who to Cancel.

Check the lock before the migration runs

pgfence maps each statement to its table lock and the operations it blocks:

pgfence explain "ALTER TABLE accounts DROP COLUMN legacy_state"

For migration files:

pgfence analyze --ci --max-risk medium migrations/*.sql

The important question is not only whether the statement is fast in an empty test database. Ask which lock it requests, how long acquisition can wait, and whether the work after acquisition scans or rewrites the table. ACCESS EXCLUSIVE turns all three into production concerns.

For the full matrix, see The Postgres Lock Mode Cheat Sheet Nobody Gave You. For the queue mechanics, see The Lock Timeout Death Spiral.