LockManager

Mechanism

Heavyweight lock bookkeeping is stored in shared hash tables partitioned by LockHashPartitionLock. Acquiring, granting, releasing, or inspecting many heavyweight locks can contend on the partition even when no SQL-level lock conflict exists.

Expected behavior

Small bursts accompany ordinary relation and transaction lock traffic.

When to investigate

A sustained share often follows lock fan-out: very large transactions, many partitions, DDL churn, or thousands of waiting lock requests.

Diagnostic actions

• Count pg_locks rows per PID and inspect the blocking tree.
• Identify statements touching many relations or partitions.
• Shorten transactions and reduce lock fan-out; raise max_locks_per_transaction only for genuine capacity errors.

Incident patterns

A deployment runs DDL across thousands of partitions while application sessions acquire relation locks, creating internal lock-table contention before a clear blocker is visible.

Diagnostic queries

Sessions waiting on LWLock/LockManager

SELECT pid, backend_type, usename, datname, application_name,
       state, now() - query_start AS query_age,
       now() - xact_start AS xact_age,
       wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocking_pids,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock'
  AND wait_event = 'LockManager'
ORDER BY query_age DESC NULLS LAST;

Current LWLock cohort

SELECT wait_event, count(*) AS waiting_sessions,
       count(*) FILTER (WHERE state = 'active') AS active_waiters,
       max(now() - query_start) AS oldest_query
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock'
GROUP BY wait_event
ORDER BY waiting_sessions DESC, wait_event;

Lock and relation context for these sessions

SELECT a.pid, l.locktype, l.mode, l.granted, l.fastpath,
       d.datname, n.nspname, c.relname,
       l.page, l.tuple, l.virtualxid, l.transactionid,
       l.classid, l.objid, l.objsubid
FROM pg_stat_activity AS a
LEFT JOIN pg_locks AS l ON l.pid = a.pid
LEFT JOIN pg_database AS d ON d.oid = l.database
LEFT JOIN pg_class AS c
  ON c.oid = l.relation
 AND l.database = (
       SELECT oid FROM pg_database WHERE datname = current_database()
     )
LEFT JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE a.wait_event_type = 'LWLock'
  AND a.wait_event = 'LockManager'
ORDER BY a.pid, l.granted, l.locktype, l.mode;