PostgreSQL Lock Wait Analysis

Why this problem matters

Lock waits can increase query latency and cause connections to accumulate in chains. Optimizing the blocked statement may not help until the root blocking transaction is addressed.

How to diagnose it

Use pg_blocking_pids() to find direct blockers and inspect transaction age on both sides. Do not reduce the relationship to one row when multiple blockers or parallel workers exist.

The query maps direct blocker PIDs to the activity view. Results are instantaneous, and lock relationships may change while it runs.

SELECT a.pid AS blocked_pid, a.usename AS blocked_user,
       b.pid AS blocking_pid, b.usename AS blocking_user,
       a.wait_event_type, a.wait_event,
       clock_timestamp() - a.query_start AS blocked_for
FROM pg_stat_activity AS a
CROSS JOIN LATERAL unnest(pg_blocking_pids(a.pid)) AS p(blocking_pid)
JOIN pg_stat_activity AS b ON b.pid = p.blocking_pid
ORDER BY blocked_for DESC;

A safe solution approach

Shorten transactions, access objects in a consistent order, and keep user interaction outside open transactions. Consider session intervention only after verifying ownership and rollback impact.

Why continuous monitoring matters

moon keeps lock-wait duration and recurrence continuously visible with low overhead. Slack, PagerDuty, and webhook channels can surface growing blocking chains.