Table, Row, Page, Advisory, and Predicate Locks with Lock Compatibility

Learning outcomes

When operators see dozens of rows in pg_locks, an easy mistake is to conclude that “the database is heavily blocked.” PostgreSQL normally holds many compatible locks during healthy work. The right question is: which resource, requested mode, holder, waiter, and compatibility rule form a wait relationship? This lesson builds that vocabulary across table, row, page, advisory, and Serializable predicate locks.

1. Lock families solve different problems

2. Set up lock targets

DROP TABLE IF EXISTS app.ch07_lock_demo;CREATE TABLE app.ch07_lock_demo (    item_id integer PRIMARY KEY,    payload text NOT NULL,    category text NOT NULL);INSERT INTO app.ch07_lock_demo VALUES(1,'alpha','A'),(2,'beta','A'),(3,'gamma','B');

3. Table locks: predict waits from compatibility

PostgreSQL has eight table-level modes. Rather than memorize their names in isolation, connect them to operations and conflicts. Ordinary SELECT takes ACCESS SHARE. INSERT/UPDATE/DELETE take ROW EXCLUSIVE. Many DDL operations require stronger modes; ACCESS EXCLUSIVE conflicts with every table mode.

The full compatibility matrix in the PostgreSQL manual is authoritative; this compact table is for reasoning, not a substitute for checking unusual modes.

BEGIN;LOCK TABLE app.ch07_lock_demo IN SHARE MODE;SELECT pg_backend_pid() AS session_a_pid;-- Keep transaction open.

BEGIN;LOCK TABLE app.ch07_lock_demo IN ACCESS SHARE MODE; -- succeedsSET LOCAL lock_timeout = '750ms';LOCK TABLE app.ch07_lock_demo IN ROW EXCLUSIVE MODE; -- waits, then errors\errverboseROLLBACK;

ERROR:  canceling statement due to lock timeoutSQLSTATE: 55P03  (lock_not_available)

Session B successfully held Access Share while A held Share because those modes are compatible. Its later Row Exclusive request conflicted. The existence of A's lock alone did not imply blocking.

4. Observe holder and waiter, not just rows in pg_locks

To see a true wait, repeat the conflict without a very short timeout and inspect from a third session. pg_stat_activity.wait_event_type = 'Lock' plus pg_blocking_pids(pid) are easier to interpret than guessing from lock rows alone.

SELECT a.pid,       a.usename,       a.state,       a.wait_event_type,       a.wait_event,       pg_blocking_pids(a.pid) AS blocking_pids,       left(a.query, 90) AS queryFROM pg_stat_activity AS aWHERE a.datname = current_database()ORDER BY a.pid;SELECT pid, locktype, mode, granted, relation::regclass AS relation,       transactionid, virtualxidFROM pg_locksWHERE relation = 'app.ch07_lock_demo'::regclass   OR pid = ANY (       SELECT pid FROM pg_stat_activity WHERE datname = current_database()   )ORDER BY pid, granted, locktype, mode;

granted = false identifies an ungranted lock request, but blocker diagnosis still needs resource/mode context. Also, pg_locks spans the cluster. Relation OIDs can only be resolved reliably through the current database's catalogs for locks belonging to that database.

5. Row locks: normal SELECT still does not conflict with a writer

A row-level FOR UPDATE lock blocks conflicting writers and row lockers, but an ordinary SELECT uses MVCC and continues to read a visible version.

ROLLBACK; -- release any table-lock exercise firstBEGIN;SELECT * FROM app.ch07_lock_demoWHERE item_id = 1FOR UPDATE;-- Keep open.

SELECT * FROM app.ch07_lock_demo WHERE item_id = 1; -- succeedsBEGIN;SELECT * FROM app.ch07_lock_demoWHERE item_id = 1FOR UPDATE NOWAIT;\errverboseROLLBACK;

ERROR:  could not obtain lock on row in relation "ch07_lock_demo"SQLSTATE: 55P03  (lock_not_available)

Do not be surprised if the holder's row lock is not displayed as a simple locktype='tuple' row in pg_locks. PostgreSQL records row read/write locks directly in tuple headers. A waiter commonly appears as waiting on the holder's transaction ID. The supplied pgrowlocks extension can inspect row-lock information, but it is optional and not required for this chapter.

6. Page locks are mostly internal

PostgreSQL does use page-level locks internally, including short-lived locks in index access methods. These are not a general SQL API for “locking page 42.” Application designs should not coordinate work using physical page identities; pages change as storage evolves. Observe them only as diagnostic internals when relevant.

7. Advisory locks coordinate application-defined resources

Advisory locks attach locking semantics to integer keys whose meaning is defined by your application—for example, “tenant 42 monthly close.” PostgreSQL does not know which table rows that key represents, so advisory locks complement rather than replace constraints and row locks.

-- Session ABEGIN;SELECT pg_advisory_xact_lock(7001);SELECT pg_backend_pid();-- Keep open.-- Session BBEGIN;SELECT pg_try_advisory_xact_lock(7001) AS acquired;-- Expect false while A holds it.ROLLBACK;-- Session ACOMMIT; -- transaction-level advisory lock is released automatically

SELECT pid, locktype, mode, granted, classid, objid, objsubidFROM pg_locksWHERE locktype = 'advisory'ORDER BY pid, granted;

Session-level advisory locks differ: repeated acquisitions stack and require matching unlock calls (or session termination). For short business operations, transaction-level advisory locks are easier to make failure-safe because PostgreSQL releases them at transaction end.

8. Predicate locks: Serializable conflict evidence without blocking

BEGIN ISOLATION LEVEL SERIALIZABLE;SELECT count(*)FROM app.ch07_lock_demoWHERE category = 'A';SELECT locktype, mode, relation::regclass, page, tuple, grantedFROM pg_locksWHERE pid = pg_backend_pid()  AND mode = 'SIReadLock'ORDER BY locktype, relation::regclass::text, page, tuple;-- Keep transaction open briefly for Session B.

UPDATE app.ch07_lock_demoSET payload = payload || '-changed'WHERE item_id = 1;-- This can commit; SIReadLock is not a blocking lock.

The exact SIReadLock granularity can differ with plan choice and predicate-lock promotion, so do not write monitoring logic that requires “one tuple lock per row read.” Serializable uses this metadata to recognize dependency structures; it does not turn predicate reads into blocking mutexes.

9. Row-lock compatibility: choose the weakest lock that protects the relationship

PostgreSQL has four row-level lock strengths. Their names matter because foreign-key checks and key-changing UPDATEs interact differently with them. A useful mental model is that FOR UPDATE is the strongest; FOR NO KEY UPDATE allows more concurrency when the row's key used by foreign keys will not change; FOR SHARE is a shared lock against stronger modifications; and FOR KEY SHARE protects a referenced key while still allowing non-key updates.

The exact conflict table in the PostgreSQL manual remains the source of truth. The production design point is to avoid escalating to FOR UPDATE merely because it is familiar. Stronger locks can turn safe concurrency into unnecessary waits.

Lock waits often surface as transaction-ID waits

Because row-lock state is stored in tuple headers, a backend that wants a conflicting row lock may discover which transaction currently owns the relevant state and then wait for that transaction ID to finish. This is why blocker diagnosis should join activity and lock-manager evidence rather than searching only for locktype='tuple'.

SELECT a.pid,       a.wait_event_type,       a.wait_event,       pg_blocking_pids(a.pid) AS blockers,       l.locktype,       l.mode,       l.granted,       l.relation::regclass AS relation,       l.transactionidFROM pg_stat_activity AS aLEFT JOIN pg_locks AS l ON l.pid = a.pidWHERE a.datname = current_database()  AND (a.wait_event_type = 'Lock' OR l.granted = false)ORDER BY a.pid, l.granted, l.locktype;

This query is a diagnostic starting point, not a complete blocking-tree algorithm. A blocker may itself be blocked, prepared transactions can hold locks without a normal backend PID, and rapidly changing waits can disappear between samples. For durable incident analysis, correlate logs, application traces, and repeated samples.

Savepoints and lock release

Locks acquired after a savepoint can be released when the transaction rolls back to that savepoint, while locks acquired before it remain. This can be useful for controlled error recovery inside a transaction, but it is not a substitute for short transaction scope. When application frameworks hide savepoints behind nested transaction APIs, make sure the team knows which database locks survive each rollback boundary.

10. Cleanup and checks

ROLLBACK;DROP TABLE IF EXISTS app.ch07_lock_demo;

11. Production judgment and bridge

Diagnose contention from waiting sessions and blockers, not lock counts alone. Keep lock scopes short, acquire resources in consistent order, use explicit locks only where the invariant needs them, and treat advisory key design as part of your application protocol. Lesson 4 deliberately creates a deadlock and then turns lock-aware semantics into a worker-queue pattern with NOWAIT, SKIP LOCKED, and bounded timeouts.

Authoritative references

• PostgreSQL 18 — Explicit Locking
• PostgreSQL 18 — LOCK
• PostgreSQL 18 — SELECT Locking Clause
• PostgreSQL 18 — pg_locks
• PostgreSQL 18 — Viewing Locks
• PostgreSQL 18 — Advisory Lock Functions
• PostgreSQL 18 — Transactions and Locking