Monitoring locks

Everything this chapter demonstrated, you can watch live in two views: pg_locks (every lock held or wanted, right now) and pg_stat_activity (what every backend is doing). This lesson is the guided tour; the production runbook chapter builds alerting on top of it.

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

One innocent UPDATE = four locks: table, index, its own xid, its own 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.4 · Run it yourself · Scenario source

Four rows for one little UPDATE, and each earns its place. The two relation / RowExclusiveLock entries — one on the table, one on its index — are the "I am writing to this table" locks; they conflict with DDL, not with other writers. The transactionid / ExclusiveLock is the hook the whole row-wait mechanism hangs on: every transaction holds an exclusive lock on its own transaction id, and waiting for a row really means requesting a share lock on the xid that wrote it, which succeeds "only when the other transaction terminates". The virtualxid entry is the same idea for transactions that haven't written anything yet.

Notice what's missing: the locked row itself. Row locks live in the row header on disk (you'll watch xmax do this job in the MVCC chapter), not in pg_locks — otherwise memory would cap how many rows you could lock.

Finding the blocker

The one query worth memorizing — or bookmarking — is the wait chain:

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';

blocker_state is the column that solves incidents. A blocker that's active is slow; a blocker that's idle in transaction is stuck — an app that did BEGIN, made changes, and wandered off to do something else. That second kind never resolves on its own.

It helps to see that the two views are describing one fact from two angles: a waiter is a pg_locks row with granted = f, and the same waiter is a pg_stat_activity row with wait_event_type = 'Lock'. You could join pg_locks to itself by hand to find the culprit, but pg_blocking_pids(pid) already understands lock queues — including blockers that are merely ahead in line — so it saves you the archaeology. Pair it with blocker_state and you've answered the only question that matters mid-incident: is the blocker slow, or is it stuck? Chapter 8 turns this into a runbook, down to what to do once you've found the culprit.

Further reading

• PostgreSQL docs: The pg_locks view
• PostgreSQL docs: pg_stat_activity
• The same lesson on MySQL