Section 2 of 3 · 7 minutes
Trace the blocking chain
Your goal
What you will be able to do
Connect each waiting session to the PID that blocks it and identify the blocker owner.
Why this matters at work
The practical reason
The session with the longest wait is often a victim, not the cause of the queue.
Learn
The idea in plain English
`pg_blocking_pids(pid)` returns the PIDs that directly block a session. Start from waiting sessions and follow those PIDs to their role, application, transaction age, and state.
A queue can contain several levels. Removing one victim does not resolve the root blocker. Contact the owner before cancellation when time allows, and understand what rollback will do.
Remember these points
- Waiting sessions and blocking sessions have different roles in the incident.
- Trace to the root blocker before acting.
- Record ownership and rollback impact.
See it in SQL
List blocked sessions and blocker PIDs
Show the direct dependency that explains each lock wait.
SELECT
pid AS blocked_pid,
pg_blocking_pids(pid) AS blocking_pids,
wait_event_type,
wait_event,
application_name
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY query_start;Each array contains direct blocker PIDs. Use those PIDs in a second lookup of `pg_stat_activity` to find their owners and transaction ages.
A non-empty blocker array is stronger evidence than assuming every long-running query is blocking others.
One row per blocked session with its direct blocker PIDs and wait details.
Useful words
Important terms
- Root blocker
- The session at the start of a blocking chain that is not itself waiting on another session.
Quick check · Not graded
Check your understanding
Which session should you investigate first in a lock queue?
Your progress is saved to your signed-in account.