Diagnosing Lock:relation vs Lock:tuple vs Lock:page waits

Scenario

The wait_event under wait_event_type = 'Lock' specifies which object is being waited on — the correct diagnosis and fix depends entirely on whether it is a relation, tuple, page, or transaction lock. Diagnose it -- Current…

Investigation Path

The wait_event under wait_event_type = 'Lock' specifies which object is being waited on — the correct diagnosis and fix depends entirely on whether it is a relation, tuple, page, or transaction lock.

Diagnose it

-- Current heavyweight lock waiters with detail:
SELECT a.pid,
       a.usename,
       a.wait_event,          -- 'relation', 'tuple', 'page', 'transactionid', etc.
       a.state,
       l.locktype,
       l.relation::regclass   AS locked_object,
       l.mode,
       l.granted,
       l.waitstart
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE a.wait_event_type = 'Lock'
ORDER BY l.waitstart ASC NULLS LAST;

Why it happens

Lock granularities in PostgreSQL:

• relation — table-level lock. Common causes: DDL (ALTER TABLE,
  VACUUM FULL, TRUNCATE), or an explicit LOCK TABLE. The
  blocker is usually visible in pg_stat_activity as an active or idle-in-transaction
  session holding an incompatible mode on the same relation in pg_locks.
• tuple — row-version level lock. Two transactions are trying to update or
  delete the same specific row version simultaneously. The second transaction waits until the
  first commits or rolls back.
• page — rare; occurs during hash index operations or sequence page locks.
  Usually transient.
• transactionid — waiting for another transaction to commit or roll back.
  Normal and expected; becomes a problem only if that transaction is very long.
• virtualxid — very short-lived; waiting for a virtual transaction ID
  to be confirmed.

How to fix it

-- Find what the blocker is holding:
SELECT pid, locktype, relation::regclass,
       mode, granted
FROM pg_locks
WHERE pid = <blocking_pid>
ORDER BY granted DESC;

For tuple locks with many waiters on the same row: this is a hot-row contention
pattern — redesign the application to avoid many concurrent writes to the same row, or use
SELECT ... FOR UPDATE SKIP LOCKED for queue-consumer patterns.

Prevent it next time

For relation locks from DDL: run DDL during low-traffic windows and use
lock_timeout so the DDL fails fast rather than queuing and blocking further
behind it. For tuple locks: reduce row-level write contention through
partitioning, batching, or architectural changes that distribute writes across more rows.

Related & next steps

• pg_locks — locktype, mode, waitstart columns
• pg_stat_activity — wait_event for Lock class
• Wait event reference — Lock class events classified
• lock_timeout GUC
• deadlock_timeout GUC

Related Knowledge

Related Interview Questions

• Practice this competency in Interview Prep

Career Impact

This scenario builds production judgment and operational confidence under pressure.

Open Career Dashboard →