Monitoring locks

PostgreSQL 18's pg_locks exposes held and requested lock-manager locks. pg_stat_activity supplies session state and wait events. Ordinary row locks are stored on disk and normally do not appear directly in pg_locks.

What one UPDATE really holds

A

B

UPDATE (takes the row lock)

UPDATE→ waits on A → ⏳ waits

COMMIT

⏵ UPDATE→ waits on A → completes

A> BEGIN;
BEGIN

A> UPDATE accounts SET balance = 200 WHERE id = 1;
UPDATE 1

This UPDATE's inspected lock entries: table, index, assigned xid and virtual xid.

M> SELECT l.locktype, l.relation::regclass AS relation, l.mode, l.granted
   FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
   WHERE a.application_name = 'A'
   ORDER BY l.locktype, l.relation::regclass::text, l.mode;
   locktype    |   relation    |       mode       | granted 
---------------+---------------+------------------+---------
 relation      | accounts      | RowExclusiveLock | t       
 relation      | accounts_pkey | RowExclusiveLock | t       
 transactionid |               | ExclusiveLock    | t       
 virtualxid    |               | ExclusiveLock    | t       
(4 rows)

B> UPDATE accounts SET balance = 300 WHERE id = 1;
⏳ B is waiting for a lock…

The waiter's tell: a lock row with granted = f — it wants A's transaction id.

M> SELECT l.locktype, l.mode, l.granted
   FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid
   WHERE a.application_name = 'B' AND NOT l.granted;
   locktype    |   mode    | granted 
---------------+-----------+---------
 transactionid | ShareLock | f       
(1 row)

You rarely need to decode pg_locks by hand — pg_blocking_pids() names the culprit.

M> SELECT waiter.application_name AS waiter, blocker.application_name AS blocker
   FROM pg_stat_activity waiter
   JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(waiter.pid))
   WHERE waiter.wait_event_type = 'Lock'
   ORDER BY waiter.application_name, blocker.application_name;
 waiter | blocker 
--------+---------
 B      | A       
(1 row)

A> COMMIT;
COMMIT

⏵ B resumes:
UPDATE 1

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The assertion checks four entries for this single-row UPDATE on this table and primary index: two RowExclusiveLock relation locks, its assigned transaction-id ExclusiveLock, and its virtual-transaction-id ExclusiveLock. This is not a universal lock count for an UPDATE. The table mode conflicts with some stronger table modes, not with another RowExclusiveLock.

B's row wait appears as an ungranted ShareLock on a transaction id. The view contract explains why: a request to wait for a transaction to end can wait on its xid lock. Every running transaction holds its virtual xid lock; an assigned permanent xid adds another lock. Tuple lock-manager entries can also appear, so absence of the ordinary on-disk row lock does not mean there can never be a tuple entry.

Finding the blocker

The demonstrated monitoring query asserts B-to-A. Its pid/query variant below is illustrative, not another executed schedule:

SELECT waiter.pid AS waiting_pid,
       waiter.query AS waiting_query,
       blocker.pid AS blocking_pid,
       blocker.state AS blocker_state,
       blocker.query AS blocker_query
FROM pg_stat_activity waiter
JOIN pg_stat_activity blocker ON blocker.pid = ANY (pg_blocking_pids(waiter.pid))
WHERE waiter.wait_event_type = 'Lock';

active means executing a query; it does not prove slow progress. idle in transaction means waiting for a client command inside a transaction; it does not prove the client will never continue. Use transaction age and application context to decide what to do. Visibility of other sessions' details depends on monitoring privileges. These live views are not one fully atomic history of the server. Chapter 8 provides operational follow-up.

Further reading

• PostgreSQL 18: pg_locks
• PostgreSQL 18: pg_stat_activity
• The same lesson on MySQL