ACCESS SHARE lock in PostgreSQL

What takes it

ACCESS SHARE (AccessShareLock in pg_locks) is the lock a query takes on every table it reads. A plain SELECT takes it on each table it touches, and on their indexes while it plans. So does COPY orders TO …, and so does pg_dump, which starts by running LOCK TABLE … IN ACCESS SHARE MODE on every table in the dump.

On PostgreSQL 18:

BEGIN;
SELECT count(*) FROM orders;
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           | AccessShareLock
 orders_pkey      | AccessShareLock
 orders_total_idx | AccessShareLock
(3 rows)

Like every table lock, it’s held until the transaction ends. After SELECT returns, the lock is still there for as long as the transaction stays open.

What it blocks

Only ACCESS EXCLUSIVE. Reads never block other reads, inserts, updates, deletes, VACUUM or CREATE INDEX. What they do block is DDL that needs the table to itself: ALTER TABLE … ADD COLUMN, DROP TABLE, TRUNCATE, VACUUM FULL and the rest.

That sounds harmless until it meets the lock queue. A session that ran a SELECT inside a transaction and then went idle (an application that forgot to commit, a psql window left open) still holds ACCESS SHARE. An ALTER TABLE arrives, can’t get ACCESS EXCLUSIVE, and waits. Every query after it waits too, because PostgreSQL queues lock requests in order and the waiting ALTER is ahead of them. Here, the SELECT in the third row can’t run even though it only wants the same lock the first session already has:

  pid   |        mode         | granted | blocked_by |                  query                   
--------+---------------------+---------+------------+------------------------------------------
 286964 | AccessShareLock     | t       | {}         | SELECT count(*) FROM orders;
 286967 | AccessExclusiveLock | f       | {286964}   | ALTER TABLE orders ADD COLUMN note text;
 286968 | AccessShareLock     | f       | {286967}   | SELECT * FROM orders WHERE id = 1;
(3 rows)

pg_blocking_pids reports both kinds of blocking: the ALTER is blocked by the session holding the lock, and the last SELECT by the ALTER queued in front of it.

Two habits prevent this. Run DDL with SET lock_timeout = '2s' so it gives up instead of holding the queue (see lock timeout), and keep transactions short. idle_in_transaction_session_timeout makes the server close sessions that sit idle inside a transaction for too long.

See who holds it

Who holds or waits for ACCESS SHARE on a table, and which sessions each one blocks. Leave out the l.relation line to see every table, though on a busy server most rows will be harmless reads.

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

In the situation above (an idle transaction that read orders, an ALTER TABLE waiting, a SELECT queued behind it):

 table_name |  pid   | granted |        state        | open_for |               query                | blocking 
------------+--------+---------+---------------------+----------+------------------------------------+----------
 orders     | 276800 | t       | idle in transaction | 00:00:01 | SELECT count(*) FROM orders;       | {276806}
 orders     | 276814 | f       | active              | 00:00:00 | SELECT * FROM orders WHERE id = 1; | {}
(2 rows)

The row to act on is the granted lock with a non-empty blocking list and a state of idle in transaction; open_for shows how long its transaction has been open. It has no running query to cancel, so SELECT pg_terminate_backend(<pid>); is the way to release it (its transaction is rolled back). Or cancel the waiting ALTER TABLE with SELECT pg_cancel_backend(<pid>); to let the queued reads through, and try again later.

How we checked

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

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

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

ERROR:  canceling statement due to lock timeout

For pg_dump, we held ACCESS EXCLUSIVE on a table, started pg_dump -n <schema> --lock-wait-timeout=1500ms, and saw it waiting for AccessShareLock in pg_locks, then fail with:

pg_dump: error: query failed: ERROR:  canceling statement due to statement timeout
pg_dump: detail: Query was: LOCK TABLE seo_locks.customers, seo_locks.orders, seo_locks.events, seo_locks.events_2026, seo_locks.events_2025, seo_locks.totals_src IN ACCESS SHARE MODE

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, so an idle transaction holding up a migration is easy to spot.