ROW EXCLUSIVE lock in PostgreSQL

What takes it

ROW EXCLUSIVE (RowExclusiveLock in pg_locks) is the lock a statement takes on a table it modifies: INSERT, UPDATE, DELETE, MERGE and COPY … FROM. Other tables the statement only reads (a subquery, a USING list) get ACCESS SHARE.

BEGIN;
UPDATE orders SET total = total + 1 WHERE id = 1;
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           | RowExclusiveLock
 orders_pkey      | RowExclusiveLock
 orders_total_idx | RowExclusiveLock
(3 rows)

Despite the name, it’s a table lock. The rows themselves are locked separately; two UPDATEs of the same row wait on a row lock, not on this one.

What it blocks

ROW EXCLUSIVE doesn’t conflict with itself, so any number of sessions can write to a table at once. It conflicts with four modes, all taken by statements that need the table’s data to hold still:

• SHARE: CREATE INDEX without CONCURRENTLY
• SHARE ROW EXCLUSIVE: CREATE TRIGGER, ALTER TABLE … ADD FOREIGN KEY, ENABLE/DISABLE TRIGGER
• EXCLUSIVE: REFRESH MATERIALIZED VIEW CONCURRENTLY (on the view)
• ACCESS EXCLUSIVE: most ALTER TABLE, DROP, TRUNCATE, VACUUM FULL

Reads are never blocked by it. The trap is the lock queue: a CREATE INDEX waiting for one long write transaction to finish blocks every later write, because they queue behind it. Reads carry on. Here, session 287011 updated a row and stayed in its transaction; a CREATE INDEX arrived, then another UPDATE, then a SELECT:

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                   
--------+------------------+---------+------------+------------------------------------------
 287011 | RowExclusiveLock | t       | {}         | UPDATE orders SET total = total + 1 WHER
 287012 | ShareLock        | f       | {287011}   | CREATE INDEX orders_customer_idx ON orde
 287013 | RowExclusiveLock | f       | {287012}   | UPDATE orders SET total = total + 1 WHER
 287024 | AccessShareLock  | t       | {}         | SELECT count(*) FROM orders;
(4 rows)

The second UPDATE (a different row) is stuck behind the CREATE INDEX, not behind the first UPDATE. On a live table, build indexes with CREATE INDEX CONCURRENTLY, which takes SHARE UPDATE EXCLUSIVE and lets writes continue, and run DDL with a lock_timeout so a waiting statement gives up instead of blocking everyone.

See who holds it

Who holds or waits for ROW 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 = 'RowExclusiveLock'
  AND l.relation = 'orders'::regclass
ORDER BY l.granted DESC, a.query_start;

In the same situation (an open UPDATE transaction, a CREATE INDEX waiting, a second UPDATE queued):

 table_name |  pid   | granted |        state        | open_for |                  query                   | blocking 
------------+--------+---------+---------------------+----------+------------------------------------------+----------
 orders     | 276901 | t       | idle in transaction | 00:00:01 | UPDATE orders SET total = total + 1 WHER | {276914}
 orders     | 276916 | f       | active              | 00:00:00 | UPDATE orders SET total = total + 1 WHER | {}
(2 rows)

On a busy table there are many granted rows here, one per writing transaction; that’s normal. Look for a holder that is idle in transaction with a long open_for and a non-empty blocking list. SELECT pg_terminate_backend(<pid>); ends it and rolls back its changes; cancelling the waiting DDL with SELECT pg_cancel_backend(<pid>); lets the queued writes through.

How we checked

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

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

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

ERROR:  canceling statement due to lock timeout

We checked the statements in the list by running each in a transaction and reading pg_locks for our own session (INSERT, UPDATE, DELETE, MERGE), or by starting it while another session held a conflicting lock and reading the mode it waited for (COPY … FROM).

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. Edits you make in the grid are staged and committed together in one short transaction when you press ⌘S, so they don’t hold ROW EXCLUSIVE while you work.