PostgreSQL Lock Contention

Investigation principle

Capture lock state and transaction age during the event when possible; screenshots after the fact often miss the chain.

Common wrong assumptions

• “The waiting query is slow.” It is waiting, not working. Tuning it changes nothing. Find the head of the queue.
• “The DDL is fast, so it is safe.” A catalog-only ALTER TABLE still needs an exclusive lock. While it waits, every new query on the table waits behind it. See the lock-queue lab: 14 seconds of zero reads.
• “Lock problems would show up as deadlock errors.” PostgreSQL detects deadlocks and cancels one side. Long blocking chains raise no error at all. They show up only as latency, unless log_lock_waits is on.
• “pg_locks will show the row.” A session waiting on a row lock usually shows as waiting on the other transaction’s transactionid, not on a row. pg_blocking_pids() resolves that for you.

First queries to run

Read-only. Run them during the problem window if you can; most of these views show current state, not history.

-- Blocking chain: who waits on whom
SELECT pid,
       pg_blocking_pids(pid)                     AS blocked_by,
       state,
       wait_event_type || ':' || wait_event      AS wait,
       now() - xact_start                        AS xact_age,
       left(query, 60)                           AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
   OR pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)
ORDER BY xact_start;

-- Long-running and idle transactions
SELECT pid, usename, application_name, state,
       now() - xact_start AS xact_age,
       left(query, 60)    AS last_query
FROM pg_stat_activity
WHERE xact_start < now() - interval '1 minute'
ORDER BY xact_start;

When a deeper investigation is justified

• Stalls happen during deploys or batch jobs, but nobody captured the chain while it was happening.
• Migrations run without lock_timeout, and adding one needs retry logic in the deploy tool.
• Long or idle transactions come from a code path or job that nobody has identified.

For the wider diagnostic sequence, read the PostgreSQL locking and concurrency guide.