ROW SHARE lock in PostgreSQL

What takes it

ROW SHARE (RowShareLock in pg_locks) is taken on a table by a SELECT with a locking clause: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE or FOR KEY SHARE. Other tables in the same query that aren’t named in the clause get the usual ACCESS SHARE.

BEGIN;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
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           | RowShareLock
 orders_pkey      | RowShareLock
 orders_total_idx | RowShareLock
(3 rows)

The four clauses differ in which row locks they take on the selected rows, not in the table lock: all four take ROW SHARE on the table.

What it blocks

At table level, very little. ROW SHARE conflicts only with EXCLUSIVE and ACCESS EXCLUSIVE. While a session holds it, other sessions can still read, insert, update and delete, run VACUUM, and even build an index with CREATE INDEX (which takes SHARE, compatible with ROW SHARE). DDL that needs ACCESS EXCLUSIVE, such as most ALTER TABLE forms, has to wait.

The waits people usually see come from the row locks. FOR UPDATE locks the rows it returned; another transaction that updates, deletes or locks one of those rows waits until the first transaction ends. In pg_locks that wait doesn’t show up on the table at all. It shows as a transactionid lock in ShareLock mode, which is a session waiting for another transaction to finish, not the SHARE table lock:

SELECT a.pid, l.locktype, 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 NOT l.granted;

  pid   |   locktype    |   mode    | granted | blocked_by |                  query                   
--------+---------------+-----------+---------+------------+------------------------------------------
 287153 | transactionid | ShareLock | f       | {287152}   | UPDATE orders SET total = total + 1 WHER
(1 row)

With a lock_timeout, the waiting UPDATE fails and names the row it was waiting for:

ERROR:  canceling statement due to lock timeout
CONTEXT:  while locking tuple (0,1) in relation "orders"

Two transactions that lock the same rows in different orders can wait on each other forever; PostgreSQL detects that and ends one of them with deadlock detected. FOR UPDATE SKIP LOCKED (skip rows that are locked) and FOR UPDATE NOWAIT (fail at once) avoid waiting on row locks.

See who holds it

Who holds ROW SHARE on a table, and who they block:

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

With one session idle in a transaction after SELECT … FOR UPDATE, and another waiting on LOCK TABLE orders IN EXCLUSIVE MODE:

 table_name |  pid   | granted |        state        | open_for |                  query                   | blocking 
------------+--------+---------+---------------------+----------+------------------------------------------+----------
 orders     | 276862 | t       | idle in transaction | 00:00:01 | SELECT * FROM orders WHERE id = 1 FOR UP | {276863}
(1 row)

To release it, the holder commits or rolls back. If it’s idle in a transaction and won’t, SELECT pg_terminate_backend(<pid>); ends the session and rolls its transaction back, freeing both the table lock and its row locks.

How we checked

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

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

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

ERROR:  canceling statement due to lock timeout

We also ran each SELECT … FOR … form in a transaction and read pg_locks for our own session: all four took RowShareLock on the table.

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.