Skip to main content
View rawEdit

13. Lock contention

Identify blocking queries and lock wait patterns. Use this dashboard when sessions are stuck waiting for locks, or when Wait event analysis shows lock waits as a dominant wait type.

When to use​

  • Users report timeouts or stuck transactions
  • Wait events show lock waits dominating
  • You need to quickly identify which session is blocking others
  • Investigating deadlock occurrences

Key panels​

  • Lock waits over time — number of sessions waiting for locks over time
  • Blocking vs blocked sessions — which sessions hold locks that others are waiting for
  • Top blocking queries — queries that cause the most lock contention
  • Lock wait duration — how long sessions are waiting for locks
  • Lock modes — distribution of lock types (AccessShare, RowExclusive, AccessExclusive, etc.)

What good looks like​

  • Lock waits are rare and short-lived (under a few seconds)
  • No long-running blocking transactions
  • No persistent patterns of the same queries blocking each other

What to investigate​

SignalAction
One session blocking many othersIdentify the blocking query and its transaction; consider terminating if safe
DDL lock waits (AccessExclusive)A migration or ALTER TABLE is waiting; see if idle-in-transaction sessions need to be cleaned up
Repeated same-query contentionApplication-level issue — consider optimistic locking, advisory locks, or serializing at the app level
DeadlocksCheck pg_stat_database.deadlocks; review the queries involved and their locking order

Quick resolution​

To identify and resolve blocking immediately:

-- Find blocking chains
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocking.state AS blocking_state
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks gl ON gl.locktype = bl.locktype
  AND gl.database IS NOT DISTINCT FROM bl.database
  AND gl.relation IS NOT DISTINCT FROM bl.relation
  AND gl.page IS NOT DISTINCT FROM bl.page
  AND gl.tuple IS NOT DISTINCT FROM bl.tuple
  AND gl.pid != bl.pid
  AND gl.granted
JOIN pg_stat_activity blocking ON blocking.pid = gl.pid;

To cancel a blocking query (safe — the transaction can retry):

SELECT pg_cancel_backend(<blocking_pid>);

To terminate a blocking session (use with caution):

SELECT pg_terminate_backend(<blocking_pid>);
  • A007 — altered settings, including lock-related timeouts (CLI)
  • N001 — wait events grouped by type and query (monitoring stack)