Lock Contention

Overview

Lock contention occurs when one session holds a lock that another session needs before it can proceed. In Postgres, the most common form is a long-running query holding a row-level or table-level lock while DDL (ALTER TABLE, CREATE INDEX without CONCURRENTLY, VACUUM FULL) tries to acquire an AccessExclusiveLock. The DDL blocks, and all subsequent queries that need even a weaker lock queue behind it—creating a thundering-herd pile-up.

insightral uses pg_blocking_pids() to detect lock wait chains and reports the root blocker, the wait duration, and the lock mode. Common fixes are: run DDL with CONCURRENTLY, set lock_timeout on DDL statements, and avoid long transactions that accumulate row locks.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Sessions waiting on locks and their blockers
SELECT
  blocked.pid                           AS blocked_pid,
  blocked.usename                       AS blocked_user,
  blocked.application_name,
  left(blocked.query, 80)               AS blocked_query,
  now() - blocked.query_start           AS wait_duration,
  blocking.pid                          AS blocking_pid,
  blocking.usename                      AS blocking_user,
  left(blocking.query, 80)              AS blocking_query,
  locks.locktype,
  locks.relation::regclass              AS locked_relation,
  locks.mode
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
JOIN pg_locks locks
  ON locks.pid = blocking.pid
  AND NOT locks.granted
WHERE blocked.wait_event_type = 'Lock'
ORDER BY wait_duration DESC;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Terminate the blocking session (confirm with application team first)
SELECT pg_terminate_backend(<blocking_pid>);

-- For DDL lock contention, use lock_timeout to fail fast
SET lock_timeout = '2s';
ALTER TABLE <table_name> ADD COLUMN <col> <type>;  -- fails fast if locked

Worked example

Common false positives

• Expected locking during intentional schema migrations in a maintenance window — normal behaviour.
• Advisory locks taken by application-level distributed mutexes (pg_try_advisory_lock) appear as lock contention but are deliberate.