EXCLUSIVE lock in PostgreSQL

What takes it

EXCLUSIVE (ExclusiveLock in pg_locks, with locktype = relation) allows only ACCESS SHARE beside it: other sessions can read, and that’s all. In core PostgreSQL, the command that takes it is REFRESH MATERIALIZED VIEW CONCURRENTLY, on the view:

BEGIN;
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals;
SELECT relation::regclass AS relation, mode
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype = 'relation'
  AND relation = 'customer_totals'::regclass
ORDER BY 1, 2;
ROLLBACK;

    relation     |       mode       
-----------------+------------------
 customer_totals | AccessShareLock
 customer_totals | ExclusiveLock
 customer_totals | RowExclusiveLock
(3 rows)

Without CONCURRENTLY, the refresh takes ACCESS EXCLUSIVE instead, and queries on the view wait until it finishes:

    relation     |        mode         
-----------------+---------------------
 customer_totals | AccessExclusiveLock
 customer_totals | ExclusiveLock
 customer_totals | ShareLock
(3 rows)

CONCURRENTLY needs a unique index on the view. Without one:

ERROR:  cannot refresh materialized view "seo_locks.customer_totals" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

You can also take it yourself with LOCK TABLE … IN EXCLUSIVE MODE, for example to stop all writes to a table for a moment while still letting reports read it.

ExclusiveLock in pg_locks isn’t always this

Every transaction holds an ExclusiveLock on its own virtual transaction ID, and one that has written something also holds one on its transaction ID:

BEGIN;
UPDATE orders SET total = total + 1 WHERE id = 1;
SELECT locktype, mode, granted
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype <> 'relation';
ROLLBACK;

   locktype    |     mode      | granted 
---------------+---------------+---------
 virtualxid    | ExclusiveLock | t
 transactionid | ExclusiveLock | t
(2 rows)

These are how other sessions wait for a transaction to finish, and they’re normal. Only rows with locktype = relation are the table lock this page is about.

What it blocks

Everything except plain reads. It conflicts with ROW SHARE, so even SELECT … FOR UPDATE waits, and with ROW EXCLUSIVE, so every write waits. It also conflicts with SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, itself and ACCESS EXCLUSIVE.

For a materialised view that means: while a concurrent refresh runs, queries on the view carry on (that’s the point of CONCURRENTLY), but a second concurrent refresh of the same view waits for the first. If refreshes are scheduled more often than they take to run, they pile up.

See who holds it

Who holds or waits for EXCLUSIVE on a table or materialised view, 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 = 'ExclusiveLock'
  AND l.relation = 'customer_totals'::regclass
ORDER BY l.granted DESC, a.query_start;

With one concurrent refresh in an open transaction and a second one waiting:

   table_name    |  pid   | granted |        state        | open_for |                  query                   | blocking 
-----------------+--------+---------+---------------------+----------+------------------------------------------+----------
 customer_totals | 277097 | t       | idle in transaction | 00:00:01 | REFRESH MATERIALIZED VIEW CONCURRENTLY c | {277108}
 customer_totals | 277108 | f       | active              | 00:00:00 | REFRESH MATERIALIZED VIEW CONCURRENTLY c | {}
(2 rows)

A SELECT from the view in the same test returned at once. The l.locktype = 'relation' condition is what keeps the per-transaction ExclusiveLock rows out of this list.

How we checked

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

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

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

ERROR:  canceling statement due to lock timeout

Both forms of REFRESH MATERIALIZED VIEW we ran inside BEGIN … ROLLBACK on a test view with a unique index, and read pg_locks for our own session.

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.