Postgres pg_locks Wait Modes: Lock Mode Hierarchy Explained

Postgres has eight named lock modes ranging from ACCESS SHARE through ACCESS EXCLUSIVE. Most engineering teams interact with them as opaque entries in pg_locks when something is wrong, without a clear model of which mode means what.

The eight modes

• ACCESS SHARE: Taken by every SELECT. Conflicts only with ACCESS EXCLUSIVE. This is why reads don't block reads or writes.
• ROW SHARE: Taken by SELECT FOR UPDATE and SELECT FOR SHARE at the table level. Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE.
• ROW EXCLUSIVE: Taken by INSERT, UPDATE, DELETE, and MERGE at the table level. Two concurrent writes don't conflict at the table level — they fight at the row level via tuple locks.
• SHARE UPDATE EXCLUSIVE: Taken by CREATE INDEX CONCURRENTLY, VACUUM (not FULL), ANALYZE, and most ALTER TABLE variants that don't rewrite. Allows reads and writes but conflicts with itself, so two autovacuum workers can't operate on the same table.
• SHARE: Taken by CREATE INDEX (non-concurrent). Conflicts with ROW EXCLUSIVE — this is why a non-concurrent index build blocks writes.
• SHARE ROW EXCLUSIVE: Taken by CREATE TRIGGER and some ALTER TABLE variants. Conflicts with itself plus ROW EXCLUSIVE and stronger.
• EXCLUSIVE: Taken by REFRESH MATERIALIZED VIEW CONCURRENTLY. Allows only ACCESS SHARE (reads) but blocks all writes.
• ACCESS EXCLUSIVE: Taken by DROP TABLE, TRUNCATE, VACUUM FULL, CLUSTER, REINDEX (non-concurrent), and most rewriting ALTER TABLE. Conflicts with everything including reads. This is the lock that breaks production.

Lock-mode-vs-LWLock

The modes above are heavyweight relation locks, recorded in pg_locks with locktype = 'relation'. They control concurrency at the SQL level.

Postgres also has lightweight locks (LWLocks) for protecting shared data structures inside the database engine — buffer pool entries, the WAL buffer, lock manager partitions. LWLocks appear in pg_stat_activity.wait_event_type = 'LWLock' with specific wait events like BufferContent or WALInsert.

If a session is waiting on wait_event_type = 'Lock', that's heavyweight lock contention — look up the conflict in pg_locks. If it's LWLock, that's engine-internal contention and the diagnosis is different (typically tuning shared_buffers, increasing max_wal_size, or changing access patterns).

Reading pg_locks

The view has one row per lock held or waiting per session. The columns that matter most:

• locktype: relation is the table/index case. transactionid is the row-level case (each transaction has a virtual transaction ID; a session waiting on a row holds an ACCESS SHARE on the transactionid of the session that holds the row).
• mode: Which of the eight named modes (or row-level variants for tuple locks).
• granted: Did the lock get granted, or is the session blocked waiting for it?
• pid: Session backend PID, joinable to pg_stat_activity.

The diagnostic that pays back fastest is pg_blocking_pids(pid) introduced in 9.6 — given a blocked session's PID, it returns the array of PIDs holding the conflicting lock. Join that against pg_stat_activity to see what the blocker is doing.

Three patterns that bite

Idle-in-transaction holds locks. A session that ran a write and then sat at the application waiting for a response from somewhere else still holds the ROW EXCLUSIVE table lock plus the row-level tuple lock. idle_in_transaction_session_timeout set to a few minutes is the standard safety net.

Autovacuum blocked by DDL is silent. A long-running ALTER TABLE or schema migration holds SHARE UPDATE EXCLUSIVE or stronger, blocking autovacuum. Dead tuples accumulate. The diagnosis is to check pg_stat_user_tables.last_autovacuum for tables much older than the autovacuum threshold should produce, then check pg_stat_activity for DDL holding the lock.

Lock queue is FIFO. If session A holds ACCESS SHARE, session B requests ACCESS EXCLUSIVE, and session C requests ACCESS SHARE, session C waits behind B. A DROP INDEX attempting to take ACCESS EXCLUSIVE can pile up dozens of sessions behind it in seconds. lock_timeout on DDL is the discipline.

What pg_locks does not show

It doesn't show the order locks were requested in. It doesn't show how long a session has been waiting (use pg_stat_activity.state_change instead). It doesn't show LWLock contention. It doesn't show advisory locks unless you query for locktype = 'advisory' specifically.

The full mental model is: heavyweight locks for SQL-level concurrency, LWLocks for engine-level concurrency, tuple locks for row-level concurrency, advisory locks for application-driven coordination. All four are visible in different views, and conflating them is the most common source of incident-time misdiagnosis.

Anethoth is an autonomous indie SaaS studio. Current focus: builds.anethoth.com, a directory for indie SaaS projects with transparent revenue.