Long-Running Lock Wait / Blocked Queries

Scenario

Application support tickets report widespread timeouts. pg_stat_activity reveals dozens of queries stuck in a Lock wait state for over 10 minutes. After investigation, a forgotten connection from a developer’s laptop is idle-in-transaction, holding an ACCESS EXCLUSIVE lock on a critical table. Every subsequent query against that table — including simple SELECTs — is queued behind the lock and cannot proceed.

How to Identify

Conditions:

• pg_stat_activity shows wait_event_type = 'Lock' for many sessions
• pg_locks contains rows with granted = false for the blocked queries
• Application logs show lock_timeout exceeded or connection timeout errors
• One or more sessions show state = 'idle in transaction' with a very old xact_start
• Connection count rising because application pool is exhausted by waiting sessions

Analysis Steps

-- 1. Find all sessions currently waiting for a lock
SELECT
    pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    now() - query_start   AS query_wait_duration,
    now() - xact_start    AS xact_duration,
    left(query, 100)      AS query
FROM   pg_stat_activity
WHERE  wait_event_type = 'Lock'
ORDER  BY query_wait_duration DESC;

-- 2. Find blocked sessions and their blockers
SELECT
    blocked.pid                        AS blocked_pid,
    blocked.usename                    AS blocked_user,
    left(blocked.query, 80)            AS blocked_query,
    now() - blocked.query_start        AS blocked_duration,
    blocking.pid                       AS blocking_pid,
    blocking.usename                   AS blocking_user,
    blocking.state                     AS blocking_state,
    now() - blocking.xact_start        AS blocking_xact_age,
    left(blocking.query, 80)           AS blocking_query
FROM   pg_stat_activity blocked
JOIN   pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE  cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- 3. Identify dangerous idle-in-transaction sessions
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    now() - xact_start   AS idle_in_txn_duration,
    left(query, 80)      AS last_query
FROM   pg_stat_activity
WHERE  state = 'idle in transaction'
ORDER  BY xact_start ASC;

-- 4. Inspect lock details for the blocker
SELECT
    l.pid,
    l.locktype,
    CASE WHEN l.relation IS NOT NULL
         THEN l.relation::regclass::text
         ELSE l.locktype END           AS object,
    l.mode,
    l.granted
FROM   pg_locks l
WHERE  l.pid = <blocking_pid>;   -- substitute the blocking PID from step 2

-- 5. Check current lock_timeout setting
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;

Pitfalls

• Terminating the wrong process can cascade — always identify the root blocker (the session with granted = true) before acting, not a session that is itself blocked.
• Setting statement_timeout too short globally will kill legitimate long-running queries (e.g., reports, data migrations). Use it per-session or per-role instead.
• Idle-in-transaction sessions are the most dangerous culprit — they hold locks indefinitely with no active query, so query-level timeouts do nothing to clear them.
• lock_timeout only prevents new waiters from queuing indefinitely; it does not release locks already held by existing sessions.
• Once a DDL statement acquires the lock queue position, all subsequent queries also pile up behind it, creating an avalanche even if the original DDL is brief.

Resolution Approach

Identify the root blocker — the idle in transaction session holding the lock — using pg_blocking_pids(). Terminate it with pg_terminate_backend(). Immediately after, set idle_in_transaction_session_timeout to prevent recurrence. Add lock_timeout to DDL scripts so they fail fast rather than blocking indefinitely.