ACCESS EXCLUSIVE lock in PostgreSQL

What takes it

ACCESS EXCLUSIVE (AccessExclusiveLock in pg_locks) is the lock PostgreSQL takes when a command changes a table in a way no other session may see half-done. On PostgreSQL 18 these statements took it on the table:

• DROP TABLE, TRUNCATE
• VACUUM FULL and CLUSTER, which write a new copy of the table
• REFRESH MATERIALIZED VIEW without CONCURRENTLY
• most ALTER TABLE forms: ADD COLUMN, DROP COLUMN, ALTER COLUMN … TYPE, ALTER COLUMN … SET NOT NULL, ADD CONSTRAINT … CHECK or UNIQUE, RENAME, RENAME COLUMN, ENABLE ROW LEVEL SECURITY
• DROP INDEX without CONCURRENTLY (it locks the table as well as the index), DROP TRIGGER, CREATE POLICY
• LOCK TABLE orders with no mode: ACCESS EXCLUSIVE is the default

How long it’s held depends on the command. Adding a nullable column only changes the catalog, so the lock lasts milliseconds. Changing a column’s type, VACUUM FULL and CLUSTER rewrite the table and hold the lock for the whole rewrite. Either way, the lock is released when the transaction ends, not the statement: an ALTER TABLE inside BEGIN … COMMIT keeps everyone out until the commit.

Not every ALTER TABLE takes it. VALIDATE CONSTRAINT, SET STATISTICS and ATTACH PARTITION take SHARE UPDATE EXCLUSIVE; ADD FOREIGN KEY and ENABLE/DISABLE TRIGGER take SHARE ROW EXCLUSIVE.

REINDEX is a special case: it takes ACCESS EXCLUSIVE on the index it rebuilds and only SHARE on the table. In practice that still stops reads. The planner locks every index of a table while it plans a query, so in our test even SELECT id FROM orders WHERE id = 1, which uses the primary key, waited while a different index was being rebuilt. On a live table, use REINDEX INDEX CONCURRENTLY instead.

What it blocks

Everything. ACCESS EXCLUSIVE conflicts with all eight table lock modes, including ACCESS SHARE, the lock every SELECT takes. It is the only mode that blocks a plain SELECT.

The bigger danger is the queue. Lock requests wait in line: once an ALTER TABLE is waiting for ACCESS EXCLUSIVE, every later query on the table waits behind it, even queries that don’t conflict with whoever holds the table now. One forgotten idle in transaction session that read the table an hour ago can turn a millisecond ADD COLUMN into an outage.

So before DDL on a busy table, set a lock timeout and retry if it fails:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN note text;

If the lock isn’t granted within two seconds, the statement fails with canceling statement due to lock timeout instead of stalling the queue behind it.

See who holds it

This lists who holds or waits for ACCESS EXCLUSIVE on a table, and which sessions each one blocks (pg_blocking_pids returns the sessions blocking a given process). Leave out the l.relation line to see every table.

SELECT l.relation::regclass AS table_name,
       l.pid,
       l.granted,
       a.state,
       date_trunc('second', now() - a.xact_start) AS open_for,
       left(a.query, 40) AS query,
       ARRAY(SELECT w.pid FROM pg_stat_activity w
             WHERE l.pid = ANY (pg_blocking_pids(w.pid))
             ORDER BY w.pid) AS blocking
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype = 'relation'
  AND l.mode = 'AccessExclusiveLock'
  AND l.relation = 'orders'::regclass
ORDER BY l.granted DESC, a.query_start;

With one session in an open transaction after ALTER TABLE orders ADD COLUMN note text, a SELECT and an UPDATE waiting behind it:

 table_name |  pid   | granted |        state        | open_for |                  query                   |    blocking     
------------+--------+---------+---------------------+----------+------------------------------------------+-----------------
 orders     | 277144 | t       | idle in transaction | 00:00:01 | ALTER TABLE orders ADD COLUMN note text; | {277146,277158}
(1 row)

To see the whole line for the table, every mode, granted or waiting:

SELECT a.pid,
       l.mode,
       l.granted,
       pg_blocking_pids(a.pid) AS blocked_by,
       left(a.query, 40) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.locktype = 'relation'
  AND l.relation = 'orders'::regclass
ORDER BY a.query_start;

  pid   |        mode         | granted | blocked_by |                  query                   
--------+---------------------+---------+------------+------------------------------------------
 286911 | AccessExclusiveLock | t       | {}         | ALTER TABLE orders ADD COLUMN note text;
 286922 | AccessShareLock     | f       | {286911}   | SELECT count(*) FROM orders;
 286923 | RowExclusiveLock    | f       | {286911}   | UPDATE orders SET total = total + 1 WHER
(3 rows)

state matters. An active holder is still working: wait, or cancel its query with SELECT pg_cancel_backend(<pid>);. An idle in transaction holder has nothing running to cancel, so the lock stays until the client commits or rolls back; SELECT pg_terminate_backend(<pid>); closes that session and rolls its transaction back.

How we checked

On PostgreSQL 18.6, with two psql sessions on a test table. Session 1 took the lock and kept its transaction open; session 2 asked for each mode in turn with a short lock_timeout:

-- session 1
BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;

-- session 2
SET lock_timeout = '150ms';
BEGIN;
LOCK TABLE orders IN ACCESS SHARE MODE;
ROLLBACK;

ERROR:  canceling statement due to lock timeout

To see which lock a statement takes, we ran it in a transaction and read pg_locks for our own session before rolling back:

BEGIN;
ALTER TABLE orders ADD COLUMN note text;
SELECT relation::regclass AS relation, mode
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype = 'relation'
  AND relation <> 'pg_locks'::regclass
ORDER BY 1, 2;
ROLLBACK;

 relation |        mode         
----------+---------------------
 orders   | AccessExclusiveLock
(1 row)

Statements that can’t run in a transaction block (VACUUM FULL) we started while another session held a conflicting lock, and read the mode they were waiting for from pg_locks.

In Inlet

Inlet’s Activity monitor shows sessions and the locks they hold, which query blocks which, and can cancel a query or terminate a session. The structure editor shows the DDL before it runs and warns when a change rewrites or scans the table.