PostgreSQL Blocking Queries: Find the Root Blocker with pg_locks

How PostgreSQL locking works when you are debugging a stall

Lock contention looks different from a resource problem. Queries stop finishing, connection counts climb, and the application times out, yet CPU is idle and disk I/O is flat. The sessions are not doing slow work. They are waiting for a lock another session holds. This workflow is for backend and platform engineers on call for shared databases, where one team's migration or forgotten transaction can stall everyone else's traffic.

PostgreSQL takes two kinds of locks. Table-level locks are acquired automatically by every statement, in a mode that depends on what the statement does. Row-level locks are taken by UPDATE, DELETE and SELECT ... FOR UPDATE on the specific rows they touch. A second writer to the same row waits on the first writer's transaction, not on the row itself.

Every lock is held until the transaction commits or rolls back, not until the statement finishes. A one-millisecond UPDATE inside a transaction that then sits idle while the application calls an external API keeps its locks for that whole time.

Requests also queue in order. A waiting ACCESS EXCLUSIVE request blocks the ordinary SELECTs that arrive after it, even though those reads would not conflict with the session that holds the current lock.

Finding waiting and blocking sessions with pg_stat_activity and pg_blocking_pids()

Start with pg_stat_activity, because it already knows which backends are waiting. A session stuck on a heavyweight lock shows wait_event_type = 'Lock', and wait_event names the lock type, such as relation, transactionid or tuple. Its state is still active: the query is running, just not progressing. Compare query_start with now() to see how long the statement has been stuck. Compare xact_start with now() to see how long the transaction has been open.

The blocker often looks harmless. A session in idle in transaction is doing no work, but it still holds every lock its transaction has acquired. It keeps them until it commits or rolls back. Typical causes are an application that opened a transaction and then waited on a network call, or a developer who left a BEGIN open in a console. The query column for that session shows only the last statement it ran. That statement may not be the one that took the conflicting lock. An old xact_start paired with a recent state_change is the pattern to look for.

To pair victims with blockers, call pg_blocking_pids(pid) for each waiting backend. It returns an array of the PIDs that block it. Join back to pg_stat_activity on blocker.pid = ANY(pg_blocking_pids(blocked.pid)). Select the PID, state, wait event, transaction age and query text for both sides.

When reading the output:

• A blocker can itself be waiting. As described above, queue order means a session can block others while it is still queued.
• One victim can list several blockers.
• The function takes a snapshot of lock state each time it runs. Run it on demand during an incident, not on a tight polling loop.

Copy-- Pair each waiting backend with the session(s) blocking it.
-- pg_blocking_pids() exists in PostgreSQL 9.6 and later.
SELECT
  blocked.pid                          AS blocked_pid,
  blocked.wait_event_type              AS blocked_wait_type,  -- 'Lock' for heavyweight lock waits
  blocked.wait_event                   AS blocked_wait_event, -- relation, transactionid, tuple, ...
  now() - blocked.query_start          AS blocked_for,        -- how long this statement has been stuck
  left(blocked.query, 60)              AS blocked_query,
  blocker.pid                          AS blocker_pid,
  blocker.state                        AS blocker_state,      -- 'idle in transaction' is a common culprit
  blocker.wait_event_type              AS blocker_wait_type,  -- non-null: the blocker is itself waiting
  now() - blocker.xact_start           AS blocker_xact_age,   -- how long it has held its locks
  now() - blocker.state_change         AS blocker_in_state,   -- recent change + old xact = stale open transaction
  left(blocker.query, 60)              AS blocker_last_query  -- last statement only, not necessarily the lock taker
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocker
  ON blocker.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock'
-- Oldest blocker transactions first: these are the likeliest root causes.
ORDER BY blocker.xact_start NULLS LAST, blocked.pid;

Joins pg_stat_activity to itself on pg_blocking_pids() to show each lock-waiting query next to the session blocking it, with timing columns for both sides and the oldest blocker transactions listed first.

Reading pg_locks directly: granted, locktype and mode

pg_locks has one row per lock that a session holds or is waiting for. Each row carries a pid, so you can join it back to pg_stat_activity. The most useful column is granted. When it is true, the session holds the lock. When it is false, the session is queued behind someone else. Each waiting row has at least one granted row on the same object that conflicts with it.

locktype tells you what kind of object is locked:

• relation: a table or index. The relation column holds its OID, which you join to pg_class.oid to get a table name. The OID only resolves in the database named by the database column, so run the join while connected to that database.
• transactionid: a transaction's ID. Every transaction holds an exclusive lock on its own ID until it ends.
• tuple: a single row version. This lock is held briefly so that waiters queue in order.

mode is the lock strength, for example AccessShareLock for a plain SELECT, RowExclusiveLock for DML, or AccessExclusiveLock for most ALTER TABLE forms. Compare the mode of a waiting row with the mode of the granted row to see why they conflict.

Row-level waits usually show up as a wait on a transactionid. PostgreSQL does not track row locks in the shared lock table. Instead, it records the locking transaction in the row header. A session that needs that row requests a ShareLock on the holder's transaction ID, which is only granted once that transaction commits or aborts.

Every read of pg_locks briefly takes the lock manager's internal locks to build a snapshot of the current state. This is fine for diagnosing an incident. If you poll it every second from a monitoring agent, those reads add contention to the system you are trying to observe.

Copy-- Pair each waiting lock request with the granted locks on the same object.
-- Run while connected to the database you are diagnosing so relation OIDs resolve.
SELECT
  w.pid                    AS waiting_pid,
  w.locktype,
  w.mode                   AS waiting_mode,
  h.pid                    AS holding_pid,
  h.mode                   AS held_mode,
  c.relname                AS locked_table,     -- NULL for transactionid/virtualxid waits
  w.transactionid          AS waited_on_xid,    -- set for row-level waits
  a.query                  AS holder_query
FROM pg_locks w
JOIN pg_locks h
  ON  h.granted
  AND h.pid <> w.pid
  -- Same lock target: compare every identifying column, treating NULLs as equal
  AND h.locktype      = w.locktype
  AND h.database      IS NOT DISTINCT FROM w.database
  AND h.relation      IS NOT DISTINCT FROM w.relation
  AND h.page          IS NOT DISTINCT FROM w.page
  AND h.tuple         IS NOT DISTINCT FROM w.tuple
  AND h.virtualxid    IS NOT DISTINCT FROM w.virtualxid
  AND h.transactionid IS NOT DISTINCT FROM w.transactionid
  AND h.classid       IS NOT DISTINCT FROM w.classid
  AND h.objid         IS NOT DISTINCT FROM w.objid
  AND h.objsubid      IS NOT DISTINCT FROM w.objsubid
-- Only resolve OIDs that belong to the current database
LEFT JOIN pg_class c
  ON  c.oid = w.relation
  AND w.database = (SELECT oid FROM pg_database WHERE datname = current_database())
LEFT JOIN pg_stat_activity a
  ON a.pid = h.pid
WHERE NOT w.granted
-- Some granted rows may hold a compatible mode; compare waiting_mode with held_mode.
ORDER BY w.pid, h.pid;

A one-off diagnostic query that pairs each ungranted pg_locks row with the granted locks on the same object, names the table through pg_class, and shows the transaction ID that row-level waits are queued on.

Walking the lock queue to the root blocker

When the blocked sessions are all simple SELECTs, it is tempting to blame whatever looks most suspicious in pg_stat_activity. Often the session that appears to be the blocker is itself waiting, and the real cause is a session that is doing nothing at all.

PostgreSQL grants table locks in queue order. An ALTER TABLE that needs ACCESS EXCLUSIVE cannot be granted while any other transaction holds even ACCESS SHARE on the table, so it waits. New readers then arrive. Their ACCESS SHARE request does not conflict with any lock that is currently granted, but it does conflict with the ACCESS EXCLUSIVE request already waiting in the queue. To keep the DDL from starving, those readers queue behind it. The ALTER TABLE holds nothing yet, but it still stalls every query that touches the table.

Copypid 101  idle in transaction   holds ACCESS SHARE (granted)
            ▲
pid 202  ALTER TABLE ...       wants ACCESS EXCLUSIVE (waiting on 101)
            ▲
pid 303  SELECT ...            wants ACCESS SHARE (waiting on 202)
pid 304  SELECT ...            wants ACCESS SHARE (waiting on 202)
pid 305  SELECT ...            wants ACCESS SHARE (waiting on 202)

pg_blocking_pids() reports these queue-order blocks as well as blocks from held locks. That lets you follow the chain mechanically. A recursive query starts from every waiting session, joins each one to the PIDs returned by pg_blocking_pids(), and repeats the step for those PIDs. The root blocker is a PID that blocks others but has an empty pg_blocking_pids() result itself. In this example, that is the idle transaction in pid 101. Have the query record the path it has already visited. A deadlock briefly forms a cycle before the deadlock detector breaks it, and without the visited path the recursion would loop through that cycle.

Copy-- Walk pg_blocking_pids() from every waiting session up to the head of its chain.
WITH RECURSIVE chain AS (
    -- Seed: each waiting session paired with each PID directly blocking it
    -- (held-lock conflicts and lock-queue-order conflicts alike).
    SELECT a.pid            AS waiter,
           b.pid            AS blocker,
           ARRAY[a.pid, b.pid] AS path
    FROM pg_stat_activity a
    CROSS JOIN LATERAL unnest(pg_blocking_pids(a.pid)) AS b(pid)

    UNION ALL

    -- Step: follow the current blocker to whatever is blocking it.
    SELECT c.waiter,
           nb.pid,
           c.path || nb.pid
    FROM chain c
    CROSS JOIN LATERAL unnest(pg_blocking_pids(c.blocker)) AS nb(pid)
    -- Stop if this PID is already on the path (transient deadlock cycle).
    WHERE nb.pid <> ALL (c.path)
)
SELECT r.blocker                    AS root_pid,
       count(DISTINCT r.waiter)     AS sessions_stuck_behind_it,
       s.state,
       now() - s.xact_start         AS xact_age,
       left(s.query, 60)            AS last_query
FROM chain r
JOIN pg_stat_activity s ON s.pid = r.blocker
-- A root blocker is not waiting on anyone itself.
WHERE cardinality(pg_blocking_pids(r.blocker)) = 0
GROUP BY r.blocker, s.state, s.xact_start, s.query
ORDER BY sessions_stuck_behind_it DESC;

-- For the diagram above, expect one row: root_pid 101, state
-- 'idle in transaction', with 4 sessions (202, 303, 304, 305) behind it.

A recursive query over pg_blocking_pids() that follows each waiting session up its chain, guards against cycles with a visited-PID path, and reports the session at the head of the chain along with how many sessions are stuck behind it.

Resolving an incident safely: pg_cancel_backend() vs pg_terminate_backend()

Before signalling anything, look up the root blocker's pid in pg_stat_activity. Check query (for an idle session this is the last statement, not the one that took the lock), application_name and client_addr to identify the owner, state, and now() - xact_start for transaction age. A migration that has been running for 40 minutes calls for a different decision than a worker left idle in transaction by a missing commit.

Try SELECT pg_cancel_backend(101); first when the blocker is actively executing. If the pid is still in your blocker query a few seconds later, or it is idle in transaction, escalate with SELECT pg_terminate_backend(101);. Both functions return true only if the signal was sent. That does not mean the locks are gone, so re-run the blocker query to confirm.

Sometimes the better choice is to kill nothing:

• Rollback discards completed work. A long batch update or index build has to start over from the beginning.
• Applications must handle the error. A dropped connection may trigger retries that recreate the same contention.
• Ending the wrong pid can make things worse. Terminating a waiter instead of the root blocker leaves the queue intact. Pids can also be reused after a session ends, so re-check the pid immediately before you act.

Copy-- 1. Inspect the root blocker (pid 101) before signalling it.
--    For idle sessions, query is the LAST statement, not the one that took the lock.
SELECT pid, usename, application_name, client_addr, state,
       backend_start,                      -- note this to guard against pid reuse
       now() - xact_start   AS xact_age,
       now() - state_change AS in_current_state_for,
       left(query, 200)     AS last_query
FROM pg_stat_activity
WHERE pid = 101;

-- 2. If it is actively executing, try the gentler option first.
--    Locks stay held until the client issues ROLLBACK.
SELECT pg_cancel_backend(101);

-- 3. A true result only means the signal was sent. Check whether 101 still blocks anyone.
SELECT pid AS waiting_pid, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE 101 = ANY (pg_blocking_pids(pid));

-- 4. Escalate only if it is still blocking or is idle in transaction.
--    Matching backend_start from step 1 ensures this is the same session, not a reused pid.
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 101
  AND backend_start = '2026-10-22 09:14:03.512+00';  -- placeholder: value from step 1

-- 5. Re-run the check from step 3. It should now return no rows.
SELECT pid AS waiting_pid, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE 101 = ANY (pg_blocking_pids(pid));

Inspect a root blocker in pg_stat_activity, try pg_cancel_backend first, confirm with pg_blocking_pids, then escalate to pg_terminate_backend, using backend_start to guard against pid reuse.

Preventing pile-ups with lock_timeout, statement_timeout and idle_in_transaction_session_timeout

The cheapest incident is the one that never forms a queue. A DDL statement such as ALTER TABLE needs an ACCESS EXCLUSIVE lock. While it waits behind a long-running reader, every later query on that table queues behind the DDL, so ordinary SELECTs stall too. Setting lock_timeout makes the DDL give up instead of holding its place in line.

For migrations, set it inside the transaction with SET LOCAL lock_timeout, which reverts at commit or rollback and cannot leak into a pooled connection. A plain SET lasts for the whole session.

statement_timeout and idle_in_transaction_session_timeout work better as defaults. Use ALTER ROLE ... SET or ALTER DATABASE ... SET so they apply to new sessions without changes to application code. Scope them to the roles your application uses, not to everything.

Retries need to wait between attempts and back off. A tight retry loop around a short lock_timeout keeps cutting into the lock queue and adds to the contention it was meant to avoid.

Deadlocks, log_lock_waits and the alternatives to firefighting

A deadlock is not a long wait. It is a cycle: session A holds a lock B needs, while B holds one A needs, so neither can ever proceed. When a session has waited for deadlock_timeout (one second by default), PostgreSQL checks for such a cycle. If it finds one, it aborts one transaction with deadlock detected. The server log lists each process, the lock it waited for, the process blocking it and the statements involved. Read those lines for the order in which each transaction touched rows or tables. The fix is almost always consistent ordering, such as updating rows sorted by primary key, so a cycle cannot form.

Enable log_lock_waits to get after-the-fact evidence. Any wait longer than deadlock_timeout is then logged with its blocker, which is useful when nobody was watching during the incident. Lowering deadlock_timeout gives faster detection and more log lines. The cost is that the detection check runs more often, which adds overhead under heavy contention. Raising it reduces that overhead but lets real deadlocks hold locks longer.

Illustrative estimate: assume two incidents a month at two engineer-hours each. That is roughly 48 hours a year. The cheap configuration changes reduce that time, but only the application fixes remove it.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql blocking queries, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.