Locking & concurrencyintermediateFree in full

Detect and resolve lock contention

When queries hang on a lock, pg_blocking_pids and pg_locks tell you exactly who is waiting on whom. One query gives you the blocker's PID; ending its transaction clears the wait.

Problem

What you're actually looking at

The symptom as it shows up on a real server.

A session waiting on a row or table lock simply hangs, no error, no progress. To resolve it you need to see the wait graph: which PID is blocked, which PID holds the conflicting lock, and what each is doing.

A Meridian api_request update sits waiting while a settlement_batch job holds the row it needs. The application looks frozen; the fix starts with seeing the block clearly.

Simple terms

Two sessions want the same row. The first one grabbed it inside a transaction that is still open, so the second one has to wait its turn, and it waits silently, with no error, which is why the app just looks frozen. PostgreSQL can tell you exactly who is stuck behind whom: pg_blocking_pids returns the process ID of the session holding the lock. Once you can see the blocker, ending its transaction lets the waiting query through. This is not a deadlock, nobody is blocking each other in a circle, so PostgreSQL will not step in on its own.

Before you start

• pg_stat_activity / pg_locks visibility and pg_blocking_pids.
• Authority to cancel or terminate the blocking backend.

How to identify it

›pg_stat_activity shows a session with wait_event_type = 'Lock' that never completes.
›pg_blocking_pids(pid) returns a non-empty array, the PIDs holding the lock it waits for.
›The blocked query is a normal UPDATE/DELETE, not a deadlock (no 40P01).
›The blocker is often idle in transaction, not actively working.

Pitfalls to avoid

✕Do not terminate a backend blindly, identify the blocker and whether it is doing real work first.
✕Do not confuse lock contention (one waits, one holds) with a deadlock (both wait; PostgreSQL raises 40P01 automatically).
✕Do not use pg_terminate_backend when pg_cancel_backend (cancel the query) is enough.
✕Do not ignore the root cause, a blocker that is idle in transaction points to a missing commit/timeout.

Trace it

Run these against the affected server. To build a copy of this scenario instead, see “Reproduce it in a lab” below.

See who is blocking whom

The wait graph shows pid 2445 (api_request) blocked on a Lock by pid 2440 (settlement_batch), the blocker holds the row the update needs.

SQL

SELECT waiter.pid            AS waiting_pid,
       waiter.application_name          AS waiting_app,
       waiter.wait_event_type,
       blocker.pid                      AS blocking_pid,
       blocker.application_name         AS blocking_app,
       left(waiter.query, 46)           AS waiting_query
FROM pg_stat_activity waiter
JOIN LATERAL unnest(pg_blocking_pids(waiter.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocker ON blocker.pid = b.pid
WHERE cardinality(pg_blocking_pids(waiter.pid)) > 0
ORDER BY waiting_pid;

Lab-captured output

 waiting_pid | waiting_app | wait_event_type | blocking_pid |   blocking_app   |                 waiting_query                  
-------------+-------------+-----------------+--------------+------------------+------------------------------------------------
        2445 | api_request | Lock            |         2440 | settlement_batch | UPDATE courier_accounts SET balance = balance 
(1 row)

Inspect the exact locks held

pg_locks on courier_accounts shows both sessions holding RowExclusiveLock on the relation, and api_request also holding a granted tuple ExclusiveLock. That granted tuple row is the queue ticket, not proof the waiter is running. The wait graph in the previous step is what names settlement_batch as the blocker; pair the two views rather than reading granted flags alone.

SQL

SELECT a.application_name, l.locktype, l.mode, l.granted
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'courier_accounts'::regclass OR l.locktype = 'tuple'
ORDER BY a.application_name, l.granted DESC;

Lab-captured output

 application_name | locktype |       mode       | granted 
------------------+----------+------------------+---------
 api_request      | tuple    | ExclusiveLock    | t
 api_request      | relation | RowExclusiveLock | t
 settlement_batch | relation | RowExclusiveLock | t
(3 rows)

Resolution approach

1.Identify the blocking PID with pg_blocking_pids and confirm what it is doing.
2.If it is idle in transaction or safe to interrupt, cancel or terminate it (pg_cancel_backend / pg_terminate_backend).
3.Fix the application path that left the transaction open, or add idle_in_transaction_session_timeout.
4.Confirm the previously-blocked session now has zero blockers.

Stop it recurring

Confirm the wait cleared

After the blocker's transaction ends, the api_request session reports cardinality(pg_blocking_pids) = 0, it is no longer waiting on anyone.

SQL

SELECT application_name, state, wait_event_type,
       cardinality(pg_blocking_pids(pid)) AS blockers
FROM pg_stat_activity
WHERE application_name IN ('settlement_batch','api_request')
ORDER BY application_name;

Lab-captured output

 application_name |        state        | wait_event_type | blockers 
------------------+---------------------+-----------------+----------
 api_request      | idle in transaction | Client          |        0
(1 row)

Reproduce it in a lab

Builds the scenario above on a throwaway database so you can practise the fix. Skip this if you are working a live incident.

01
Lab setup (run this first)
Creates `courier_accounts` with a single seed row, which the two sessions in the SQL steps below then contend over.
SQL
```
DROP TABLE IF EXISTS courier_accounts;
CREATE TABLE courier_accounts (id int PRIMARY KEY, balance numeric);
INSERT INTO courier_accounts VALUES (1, 1000);
```

Verify you're done

SELECT pid, wait_event_type, pg_blocking_pids(pid) FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Related errors55P03 40P01 57014

Related errors

SQLSTATEs this runbook resolves

The error pages that send an on-call engineer here.

Other Locking & concurrency runbooks

Neighbouring incidents that share the same diagnostic surface.

All Locking & concurrency runbooks →

Connected

How this connects to the rest of the library

A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.

Open in the interactive map →