SHARE ROW EXCLUSIVE lock in PostgreSQL

What takes it

SHARE ROW EXCLUSIVE (ShareRowExclusiveLock in pg_locks) protects a table against data changes, like SHARE, and also conflicts with itself, so only one session can hold it at a time. On PostgreSQL 18 these took it:

• CREATE TRIGGER
• ALTER TABLE … ENABLE TRIGGER and DISABLE TRIGGER
• ALTER TABLE … ADD FOREIGN KEY, on the table being altered and on the table it references, with or without NOT VALID

Adding a foreign key, in one transaction:

BEGIN;
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id);
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          
------------------+-----------------------
 customers        | AccessShareLock
 customers        | RowShareLock
 customers        | ShareRowExclusiveLock
 customers_pkey   | AccessShareLock
 orders           | AccessShareLock
 orders           | ShareRowExclusiveLock
 orders_pkey      | AccessShareLock
 orders_total_idx | AccessShareLock
(8 rows)

The extra ACCESS SHARE and ROW SHARE locks come from the check that every existing customer_id has a matching customer. DROP TRIGGER is stronger: it took ACCESS EXCLUSIVE in our test.

What it blocks

It conflicts with ROW EXCLUSIVE, so INSERT, UPDATE, DELETE and MERGE wait. It also conflicts with SHARE UPDATE EXCLUSIVE (VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY), SHARE (CREATE INDEX), itself, EXCLUSIVE and ACCESS EXCLUSIVE. SELECT and SELECT … FOR UPDATE carry on.

For a foreign key this happens on both tables, and for as long as the check of existing rows takes. Adding orders.customer_id REFERENCES customers stops writes to customers as well as orders. In our test, an UPDATE customers … and an INSERT INTO orders … both waited while the constraint was being added; a SELECT from customers didn’t.

The usual fix is to split the work. Add the constraint NOT VALID: it still takes SHARE ROW EXCLUSIVE on both tables, but only for a moment, because existing rows aren’t checked. Then run VALIDATE CONSTRAINT, which checks them under weaker locks:

SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

On PostgreSQL 18 the VALIDATE step took SHARE UPDATE EXCLUSIVE on orders and ROW SHARE on customers, so writes to both continued. See adding a foreign key.

See who holds it

Who holds or waits for SHARE ROW EXCLUSIVE, and which sessions each one blocks. For a foreign key, include both tables:

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

With one session in an open transaction after adding the foreign key, an UPDATE customers and an INSERT INTO orders waiting:

 table_name |  pid   | granted |        state        | open_for |                  query                   |    blocking     
------------+--------+---------+---------------------+----------+------------------------------------------+-----------------
 customers  | 277053 | t       | idle in transaction | 00:00:01 | ALTER TABLE orders ADD CONSTRAINT orders | {277054,277065}
 orders     | 277053 | t       | idle in transaction | 00:00:01 | ALTER TABLE orders ADD CONSTRAINT orders | {277054,277065}
(2 rows)

One session, two tables, both writers blocked. Commit or roll back the migration’s transaction to release it; if it’s abandoned, SELECT pg_terminate_backend(<pid>); ends the session and rolls the constraint back.

How we checked

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

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

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

ERROR:  canceling statement due to lock timeout

CREATE TRIGGER, ENABLE/DISABLE TRIGGER, DROP TRIGGER, and ADD FOREIGN KEY with and without NOT VALID we ran inside BEGIN … ROLLBACK and read from 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. The structure editor shows the DDL for a new constraint before it runs.