PostgreSQL Deep dive: the lock hierarchy

Every query you run in PostgreSQL acquires locks. A SELECT takes an ACCESS SHARE lock. An UPDATE takes a ROW EXCLUSIVE lock. A VACUUM takes SHARE UPDATE EXCLUSIVE. Most of the time these locks are invisible; they conflict with nothing and your queries proceed without blocking. But when they do conflict, understanding the lock hierarchy is the difference between “why is my query stuck” and “I know exactly what’s holding it.”

PostgreSQL has four layers of locking: table locks, row locks, page locks, and advisory locks. Each layer serves a different purpose, and the interactions between them determine what blocks and what proceeds concurrently.

The eight table-level lock modes

Table-level locks are the coarsest granularity in PostgreSQL’s lock system. Every SQL command that touches a table acquires at least one table-level lock, regardless of whether it also acquires row-level locks. The lock mode determines what other operations can proceed concurrently on the same table.

The eight modes, from weakest to strongest:

ACCESS SHARE (AccessShareLock). Acquired by every SELECT. Conflicts only with ACCESS EXCLUSIVE. This is why ordinary reads almost never block anything. ACCESS SHARE is compatible with every lock mode except the most restrictive one.

ROW SHARE (RowShareLock). Acquired by SELECT FOR UPDATE/SHARE on the target table. Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE. The name is misleading; this is a table-level lock, not a row lock. The “row” in the name is historical.

ROW EXCLUSIVE (RowExclusiveLock). Acquired by UPDATE, DELETE, INSERT, and MERGE on the target table. Conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE. This is the most common write-mode table lock. It does not conflict with other ROW EXCLUSIVE locks, so multiple transactions can modify different rows in the same table concurrently.

SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock). Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, REINDEX CONCURRENTLY, CREATE STATISTICS, and some ALTER TABLE forms. Conflicts with itself, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE. The self-conflict means only one VACUUM or CREATE INDEX CONCURRENTLY can run on a table at a time.

SHARE (ShareLock). Acquired by CREATE INDEX (without CONCURRENTLY). Conflicts with ROW EXCLUSIVE and everything above it. This blocks writes but allows reads. Using CONCURRENTLY instead downgrades this to SHARE UPDATE EXCLUSIVE, which is why CREATE INDEX CONCURRENTLY doesn’t block writes.

SHARE ROW EXCLUSIVE (ShareRowExclusiveLock). Acquired by CREATE TRIGGER and some ALTER TABLE forms. Self-conflicting (only one session can hold it) and conflicts with ROW EXCLUSIVE and above. Blocks writes and other schema changes.

EXCLUSIVE (ExclusiveLock). Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY. Allows only concurrent ACCESS SHARE locks. Reads can proceed, but nothing else.

ACCESS EXCLUSIVE (AccessExclusiveLock). The nuclear option. Acquired by DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, ALTER TABLE (most forms), and LOCK TABLE with no mode specified. Conflicts with everything, including SELECT. This is the only lock mode that blocks ordinary reads.

The conflict matrix

The entire lock system reduces to a single conflict matrix. Two transactions can hold locks on the same table simultaneously if their lock modes don’t conflict. If they do conflict, the second transaction blocks until the first releases its lock (or the deadlock_timeout fires and the deadlock detector intervenes).

What you need to know:

SELECT never blocks. ACCESS SHARE conflicts with nothing except ACCESS EXCLUSIVE. A DROP TABLE or TRUNCATE on a table that’s being read will block until the reads finish.
Multiple writers don’t block at the table level. ROW EXCLUSIVE does not conflict with itself. Two UPDATE statements on different rows proceed without blocking each other’s table-level lock.
VACUUM (without FULL) doesn’t block writes. SHARE UPDATE EXCLUSIVE conflicts with SHARE and above, but not with ROW EXCLUSIVE. Concurrent vacuuming and DML are designed to coexist.
SHARE UPDATE EXCLUSIVE is self-conflicting. Two CREATE INDEX CONCURRENTLY operations on the same table will serialize, even though neither blocks writes.
ACCESS EXCLUSIVE blocks everything. This is why ALTER TABLE and VACUUM FULL are so disruptive. Any query using the table must complete before the ACCESS EXCLUSIVE lock can be acquired.

In practice, the most common blocking scenario is a write waiting for ACCESS EXCLUSIVE (someone running DDL or VACUUM FULL), or a DDL operation waiting for a long-running SELECT to finish so it can acquire ACCESS EXCLUSIVE.

Row-level locks

Row-level locks are finer-grained than table locks. They’re acquired when a transaction needs to prevent concurrent modification of specific rows, not the entire table. They’re stored in the tuple header (the xmax field combined with infomask bits), not in the shared lock table.

FOR UPDATE acquires an exclusive row-level lock on each returned row. Other transactions trying to UPDATE, DELETE, or SELECT FOR UPDATE the same row will block. The lock mode is internally FOR UPDATE (which maps to FOR NO KEY UPDATE for key columns).

FOR NO KEY UPDATE (PG 9.4+) is similar to FOR UPDATE but doesn’t block SELECT FOR KEY SHARE. It’s used internally by UPDATE statements that don’t modify key columns. The difference matters for concurrent foreign key operations.

FOR SHARE acquires a shared row-level lock. Other transactions can also hold FOR SHARE on the same row, but exclusive locks (FOR UPDATE/FOR NO KEY UPDATE) will block. Used when you want to read a row and prevent it from being modified while you process it.

FOR KEY SHARE (PG 9.4+) is the weakest row lock. It blocks FOR UPDATE but not FOR NO KEY UPDATE. This exists specifically for foreign key validation: when you UPDATE a referenced row’s non-key columns, the referencing table needs a FOR KEY SHARE lock that doesn’t conflict with concurrent non-key updates.

NOWAIT and SKIP LOCKED (PG 9.5+) modify row-lock behavior. NOWAIT causes the query to raise an error immediately if a requested row is locked. SKIP LOCKED silently skips locked rows, returning only the unlocked ones. The SKIP LOCKED pattern is the foundation of efficient job queue implementations:

SELECT id, payload
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

Each worker gets a different set of rows. No blocking, no errors, no coordination needed.

Fast-path locks

Not every row lock goes through the shared lock table. PostgreSQL has a fast-path optimization for uncontended row locks. When a transaction acquires a row lock and there’s no contention, the lock is recorded only in the tuple header (via xmax and infomask bits) without an entry in the shared lock table. This is significantly cheaper than a full lock acquisition.

The fast path works for FOR KEY SHARE, FOR SHARE, and FOR NO KEY UPDATE locks. FOR UPDATE always goes through the regular lock table. The fast-path lock is promoted to a regular lock when contention is detected (another transaction tries to lock the same row).

This is why SELECT FOR KEY SHARE on uncontended rows is much cheaper than SELECT FOR UPDATE: the former can use the fast path, the latter cannot.

Page-level locks

Page-level locks are internal to PostgreSQL’s storage engine. They’re acquired during index operations to protect internal data structures (specifically, B-tree pages during page splits and deletions). You can’t acquire page-level locks explicitly, and they’re rarely visible unless you’re diagnosing contention on index operations.

Page-level locks show up in pg_locks with locktype = 'page'. If you see them waiting, it usually means multiple sessions are concurrently modifying the same index page, often from inserts into a monotonically increasing key column (serial/identity) that all land on the rightmost page.

The fix for page-level lock contention is the same as the fix for B-tree bloat from sequential keys: use a key with better distribution (UUID v7 instead of v4, or a prefix-sharded serial), or lower the index fillfactor to leave room for inserts without page splits.

What pg_locks actually shows

pg_locks is the single source of truth for what’s locked and what’s waiting. The key columns:

Column	What it tells you
`locktype`	`relation` (table/index), `tuple` (row), `page`, `transactionid`, `virtualxid`, `object`, `advisory`
`relation`	OID of the table or index (for relation locks)
`page`	Page number (for page locks)
`tuple`	Tuple position on the page (for tuple locks)
`virtualxid`	Virtual transaction ID (for transaction locks)
`transactionid`	Real transaction ID (for transaction locks)
`classid`, `objid`	Key components (for advisory and object locks)
`mode`	Lock mode name (e.g., `AccessExclusiveLock`, `RowShareLock`)
`granted`	`true` if held, `false` if waiting
`waitstart`	When the lock started waiting (PG 16+)
`pid`	Backend process holding or requesting the lock

Finding blockers

The most common diagnostic query:

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked_locks.pid = blocked.pid
    AND blocked_locks.granted = false
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.page = blocked_locks.page
    AND blocking_locks.tuple = blocked_locks.tuple
    AND blocking_locks.granted = true
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE blocked.pid != blocking.pid;

This finds pairs where one session is waiting for a lock held by another. It works by matching on the same lock resource (same relation, page, and tuple) between a granted lock and a waiting lock.

For a simpler view of what’s waiting:

SELECT pid,
       locktype,
       mode,
       granted,
       waitstart,
       query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT granted
ORDER BY waitstart;

Lock counts per session

SELECT pid,
       count(*) FILTER (WHERE locktype = 'relation') AS table_locks,
       count(*) FILTER (WHERE locktype = 'tuple') AS row_locks,
       count(*) FILTER (WHERE locktype = 'advisory') AS advisory_locks,
       count(*) FILTER (WHERE NOT granted) AS waiting_locks
FROM pg_locks
GROUP BY pid
ORDER BY waiting_locks DESC, count(*) DESC;

A session with a high number of row locks (tens of thousands) might be holding too many locks from a DELETE or UPDATE without committing. This can exhaust the shared lock table and prevent other sessions from acquiring any locks at all.

Transaction and virtual transaction locks

Two additional lock types you’ll see in pg_locks:

virtualxid locks represent the internal virtual transaction ID assigned to each transaction. Every transaction holds one, and they’re used to ensure that a transaction that’s waiting for another transaction can detect when that transaction completes.

transactionid locks represent real (persisted) transaction IDs. These appear when a transaction needs to wait for another transaction to complete, for example when a SELECT needs to determine if a row modified by another transaction is committed or aborted. The visibility check may need to wait for the other transaction’s outcome.

In practice, transactionid locks waiting usually mean one transaction is trying to read a row that’s being modified by another uncommitted transaction in READ COMMITTED mode. In REPEATABLE READ, this would raise a serialization failure instead.

Savepoint interaction

Locks acquired after a savepoint are released if the savepoint is rolled back to. This applies to table locks, row locks, and advisory locks equally:

BEGIN;
SAVEPOINT sp1;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;  -- acquired after savepoint
ROLLBACK TO sp1;  -- lock released, not held
-- Other sessions can now access orders
COMMIT;

This is consistent with MVCC semantics: rolling back to a savepoint undoes everything that happened after it, including lock acquisitions. In PL/pgSQL, the same rule applies to exception blocks (an error escape releases locks acquired within the block).

The gotcha: LOCK TABLE without a mode

LOCK TABLE foo; acquires ACCESS EXCLUSIVE, the strongest mode. This blocks everything, including reads. The default is aggressive because LOCK TABLE without a mode specification is typically used for maintenance operations where you need exclusive access.

If you want to prevent writes but allow reads, specify the mode:

LOCK TABLE foo IN SHARE MODE;  -- blocks writes, allows reads
LOCK TABLE foo IN ROW EXCLUSIVE MODE;  -- allows writes, blocks SHARE+

Key takeaways

PostgreSQL has eight table-level lock modes. The only one that blocks SELECT is ACCESS EXCLUSIVE. The most restrictive lock your operations need is the one you should use.
The conflict matrix is the whole story. Two lock modes either conflict or they don’t. There’s no partial conflict or priority system.
Row-level locks are stored in tuple headers, not the shared lock table. Fast-path optimization skips the lock table entirely for uncontended FOR KEY SHARE/FOR SHARE/FOR NO KEY UPDATE locks.
Page-level locks are internal to the storage engine and indicate index page contention, usually from monotonically increasing keys.
pg_locks shows every lock in the system. Join it with pg_stat_activity to see which queries are blocking and which are blocked.
Savepoint rollbacks release locks acquired after the savepoint, including in PL/pgSQL exception blocks.
LOCK TABLE without a mode defaults to ACCESS EXCLUSIVE. Always specify the mode if you don’t need the strongest lock.

What’s next

Locks explain what’s blocking, but they don’t explain why some queries block for minutes while others resolve instantly. That answer is usually in how PostgreSQL detects deadlocks, and what happens when it doesn’t detect one in time. Next time: deadlock detection, the deadlock_timeout, and log_lock_waits.

Previous: Advisory locks