PostgreSQL Blocking Lock Capture

Purpose

Identifies active lock blocking chains in PostgreSQL:

1. The blocking session (holding the lock)
2. The blocked sessions (waiting for the lock)
3. Lock type, relation, and wait duration

Supported Platforms and Versions

PostgreSQL 13 through 17. All queries use standard pg_locks and pg_stat_activity views.

Required Permissions

pg_monitor role (PostgreSQL 10+) — grants access to pg_stat_activity including other users’ sessions.

Without pg_monitor, a superuser role or explicit SELECT grant on pg_stat_activity is required.

Risk Level

Low. Read-only. No changes to locks, sessions, or data.

Script

-- ============================================================
-- PostgreSQL Blocking Lock Capture
-- Permissions: pg_monitor (or superuser)
-- Risk: Read-only
-- ============================================================

-- Full blocking chain view
SELECT
    blocker.pid                         AS blocker_pid,
    blocker.usename                     AS blocker_user,
    blocker.application_name            AS blocker_app,
    blocker.state                       AS blocker_state,
    LEFT(blocker.query, 200)            AS blocker_query_snippet,
    blocked.pid                         AS blocked_pid,
    blocked.usename                     AS blocked_user,
    blocked.application_name            AS blocked_app,
    blocked.wait_event_type,
    blocked.wait_event,
    blocked.state                       AS blocked_state,
    NOW() - blocked.query_start         AS blocked_duration,
    LEFT(blocked.query, 200)            AS blocked_query_snippet,
    kl.locktype,
    kl.relation::regclass               AS locked_relation,
    kl.mode                             AS lock_mode_held,
    wl.mode                             AS lock_mode_wanted
FROM pg_stat_activity                   AS blocked
JOIN pg_locks                           AS wl   ON wl.pid = blocked.pid AND NOT wl.granted
JOIN pg_locks                           AS kl   ON kl.locktype = wl.locktype
    AND kl.database IS NOT DISTINCT FROM wl.database
    AND kl.relation IS NOT DISTINCT FROM wl.relation
    AND kl.page    IS NOT DISTINCT FROM wl.page
    AND kl.tuple   IS NOT DISTINCT FROM wl.tuple
    AND kl.virtualxid IS NOT DISTINCT FROM wl.virtualxid
    AND kl.transactionid IS NOT DISTINCT FROM wl.transactionid
    AND kl.classid IS NOT DISTINCT FROM wl.classid
    AND kl.objid   IS NOT DISTINCT FROM wl.objid
    AND kl.objsubid IS NOT DISTINCT FROM wl.objsubid
    AND kl.pid != wl.pid
    AND kl.granted
JOIN pg_stat_activity                   AS blocker ON blocker.pid = kl.pid
WHERE NOT blocked.pid = ANY(
    SELECT pid FROM pg_stat_activity WHERE wait_event = 'Lock' AND pid = ANY(
        SELECT pid FROM pg_locks WHERE granted
    )
)
ORDER BY blocked_duration DESC;

Simplified version (current blockers only)

-- Which PIDs are blocking other PIDs right now?
SELECT
    pg_blocking_pids(pid)               AS blocker_pids,
    pid                                 AS blocked_pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    NOW() - query_start                 AS waiting_for,
    LEFT(query, 200)                    AS query_snippet
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY waiting_for DESC;

The simplified version uses the pg_blocking_pids() function (available since PostgreSQL 9.6) and is sufficient for identifying which sessions to investigate.

Interpreting Results

• blocker_state = idle in transaction: The blocker has started a transaction but is not actively running a query. This is the most common cause of lock contention — an application opened a transaction, acquired a lock, and is waiting for application-level logic before committing. Look for the application holding the transaction.
• locked_relation: The table or index being contested. Frequent contention on a single relation indicates an application-level concurrency pattern that may need redesign.
• Long blocked_duration: Sessions blocked for more than a few seconds in an OLTP system indicate a problem. Sessions blocked for minutes indicate a serious operational issue.

Safe Execution Guidance

Run in psql or any PostgreSQL client. Safe at any time. On production instances, run during the incident to capture the current state — waiting to run it after the incident means the evidence is gone.

Rollback Steps

Not applicable — read-only script.