SHARE UPDATE EXCLUSIVE lock in PostgreSQL

What takes it

SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock in pg_locks) protects a table against concurrent schema changes and against two maintenance jobs running on it at once, while leaving reads and writes alone. On PostgreSQL 18 these took it on the table:

• VACUUM (without FULL) and ANALYZE, whether you run them or autovacuum does
• CREATE INDEX CONCURRENTLY and REINDEX … CONCURRENTLY
• CREATE STATISTICS and COMMENT ON
• ALTER TABLE … VALIDATE CONSTRAINT, ALTER COLUMN … SET STATISTICS, SET (fillfactor = …) and other storage parameters, CLUSTER ON
• ALTER TABLE … ATTACH PARTITION and DETACH PARTITION … CONCURRENTLY, on the partitioned (parent) table

BEGIN;
ALTER TABLE orders VALIDATE CONSTRAINT orders_total_positive;
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   | ShareUpdateExclusiveLock
(1 row)

This is why the safe way to add a constraint is in two steps: add it NOT VALID (a brief stronger lock, no scan), then VALIDATE CONSTRAINT, which scans the table under this lock while writes continue. See adding a foreign key.

What it blocks

It doesn’t block SELECT, INSERT, UPDATE, DELETE or SELECT … FOR UPDATE. It conflicts with:

• Itself. Two VACUUMs, a VACUUM and an ANALYZE, or two CREATE INDEX CONCURRENTLY on the same table run one after the other, not side by side.
• SHARE (CREATE INDEX), SHARE ROW EXCLUSIVE (CREATE TRIGGER, ADD FOREIGN KEY), EXCLUSIVE and ACCESS EXCLUSIVE (most ALTER TABLE).

Autovacuum gets special treatment. If your statement needs a lock that conflicts with an autovacuum worker’s SHARE UPDATE EXCLUSIVE, the worker is interrupted and your statement proceeds. The exception is an autovacuum running to prevent transaction ID wraparound (its query in pg_stat_activity ends with (to prevent wraparound)): that one isn’t interrupted, and DDL waits for it to finish.

A CREATE INDEX CONCURRENTLY that is still running holds this lock for its whole run, so a VACUUM or a second concurrent index build on the same table waits until it finishes.

See who holds it

Who holds or waits for SHARE UPDATE EXCLUSIVE on a table, and which sessions each one blocks:

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 = 'ShareUpdateExclusiveLock'
  AND l.relation = 'orders'::regclass
ORDER BY l.granted DESC, a.query_start;

With one session in an open transaction after VALIDATE CONSTRAINT and an ANALYZE waiting:

 table_name |  pid   | granted |        state        | open_for |                  query                   | blocking 
------------+--------+---------+---------------------+----------+------------------------------------------+----------
 orders     | 276960 | t       | idle in transaction | 00:00:01 | ALTER TABLE orders VALIDATE CONSTRAINT o | {276973}
 orders     | 276973 | f       | active              | 00:00:00 | ANALYZE orders;                          | {}
(2 rows)

An UPDATE started in the same test went straight through. If the holder is an autovacuum worker, pg_stat_activity.backend_type is autovacuum worker and its query starts with autovacuum:. Cancelling a VACUUM or ANALYZE with SELECT pg_cancel_backend(<pid>); is safe: it stops and leaves nothing half-changed, and you can run it again later.

How we checked

On PostgreSQL 18.6, with two psql sessions on a test table. Session 1 held SHARE UPDATE EXCLUSIVE in an open transaction; session 2 asked for each mode with a short lock_timeout:

-- session 1
BEGIN;
LOCK TABLE orders IN SHARE UPDATE EXCLUSIVE MODE;

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

ERROR:  canceling statement due to lock timeout

Statements that can run in a transaction (ANALYZE, CREATE STATISTICS, COMMENT ON, the ALTER TABLE forms) we ran inside BEGIN … ROLLBACK and read from pg_locks. VACUUM, CREATE INDEX CONCURRENTLY, REINDEX … CONCURRENTLY and DETACH PARTITION … CONCURRENTLY can’t run in a transaction block, so we started each while another session held SHARE UPDATE EXCLUSIVE and read the mode it waited for. The autovacuum behaviour is from the PostgreSQL documentation.

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.