SHARE lock in PostgreSQL

What takes it

SHARE (ShareLock in pg_locks, with locktype = relation) protects a table’s data from changing while still letting it be read. Its main user is CREATE INDEX without CONCURRENTLY, which needs the rows to hold still while it reads them all:

BEGIN;
CREATE INDEX orders_customer_idx ON orders (customer_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         
---------------------+---------------------
 orders              | ShareLock
 orders_customer_idx | AccessExclusiveLock
(2 rows)

REINDEX takes it on the table too, with ACCESS EXCLUSIVE on the index being rebuilt. In practice that also stops reads: the planner locks every index of the table while planning, so our test SELECT by primary key waited during a REINDEX of another index.

Don’t confuse it with the ShareLock you see on a row of locktype = transactionid. That one means a session is waiting for another transaction to finish, usually to update the same row; it has nothing to do with this table lock.

What it blocks

SHARE conflicts with ROW EXCLUSIVE, so INSERT, UPDATE, DELETE, MERGE and COPY … FROM all wait. On a large table, CREATE INDEX can take minutes, and writes stop for all of that time. It also conflicts with SHARE UPDATE EXCLUSIVE (VACUUM, ANALYZE), SHARE ROW EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE.

It doesn’t conflict with ACCESS SHARE or ROW SHARE, so SELECT and SELECT … FOR UPDATE keep working. Nor does it conflict with itself: two sessions can build two indexes on the same table at the same time.

On a live table, use CREATE INDEX CONCURRENTLY instead. It takes SHARE UPDATE EXCLUSIVE, which lets writes continue, at the cost of a slower build that can’t run inside a transaction block. See CREATE INDEX and CREATE INDEX CONCURRENTLY. Even a plain CREATE INDEX has to wait for open write transactions first, and while it waits, every new write queues behind it, so set a lock_timeout before running it.

See who holds it

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

With two sessions each building an index in an open transaction, and an UPDATE waiting:

 table_name |  pid   | granted |        state        | open_for |                  query                   | blocking 
------------+--------+---------+---------------------+----------+------------------------------------------+----------
 orders     | 277304 | t       | idle in transaction | 00:00:01 | CREATE INDEX orders_customer_idx ON orde | {277319}
 orders     | 277316 | t       | idle in transaction | 00:00:00 | CREATE INDEX orders_total_id_idx ON orde | {277319}
(2 rows)

Both index builds hold SHARE at once, and the UPDATE (277319) is blocked by both. A build that is still active can be stopped with SELECT pg_cancel_backend(<pid>);; the half-built index is discarded with the transaction.

How we checked

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

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

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

ERROR:  canceling statement due to lock timeout

CREATE INDEX and REINDEX we ran in a transaction and read from pg_locks; the two-index test above ran both builds and the UPDATE in separate sessions at the same time.

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 CREATE INDEX statement before it runs.