Lock Contention Diagnosis
The application is intermittently slow. Several sessions are waiting on locks. Build the real chain, find the one session actually responsible, and terminate only that one.
A session waiting on a lock shows up clearly in pg_stat_activity — state 'active', wait_event_type 'Lock' — but that session is a victim, not the cause. The actual root of a blocking chain is often a completely different session: one sitting 'idle in transaction,' holding a lock from a statement it ran a while ago and never committed or rolled back, showing no wait_event at all because it is not waiting on anything — it is just sitting there. Terminating a visibly 'blocked' session accomplishes nothing; the application would simply retry and immediately block again on the same real root cause.\n\npg_blocking_pids(pid) returns, directly and authoritatively, exactly which other backend PIDs a given session is currently waiting on — no manual join across pg_locks required to get the direct answer, though reading pg_stat_activity's state and wait_event columns alongside it is what reveals which of those PIDs is itself blocked further up the chain, and which one is the genuine, non-waiting root. This lab builds a real 3-session, 2-hop chain, maps it directly, and proves that terminating only the real root — identified by its actual idle-in-transaction state, not by guessing a PID — genuinely unblocks everyone else on its own.
The Real Chain, Visible in pg_stat_activity
SELECT pid, state, wait_event_type, wait_event, left(query,50) AS query
FROM pg_stat_activity WHERE datname='beer_db' AND state != 'idle' ORDER BY pid;
pid | state | wait_event_type | wait_event | query
-----+---------------------+-----------------+---------------+------------------------------------------
138 | idle in transaction | Client | ClientRead | UPDATE sql_lock_a SET val=2 WHERE id=1;
142 | active | Lock | transactionid | UPDATE sql_lock_a SET val=3 WHERE id=1;
145 | active | Lock | transactionid | UPDATE sql_lock_b SET val=3 WHERE id=1;
138 is 'idle in transaction' — not waiting on anything at all, just sitting there, still holding whatever it locked. 142 and 145 both show a real 'Lock' wait — they are the visible victims, not the cause.
The Real Chain, Mapped Directly
SELECT pid, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity WHERE datname='beer_db' AND cardinality(pg_blocking_pids(pid)) > 0;
pid | blocked_by
-----+------------
142 | {138}
145 | {142}
142 is blocked by 138. 145 is blocked by 142. A real 2-hop chain: 145 → 142 → 138. 138 is the genuine root — it has no 'blocked_by' entry at all, meaning nothing is blocking it in turn.
Terminate Only the Root, by State — Not a Guessed PID
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle in transaction' AND datname = 'beer_db';
Identifying the root by its actual 'idle in transaction' state, rather than a specific PID number (which changes every time), is what makes this reliable — PIDs are different on every real run, but the state that marks the true root is not.
Both Victims, Genuinely Unblocked on Their Own
Session B's own transcript, uninterrupted:
BEGIN
UPDATE 1
UPDATE 1 <- this was the statement blocked on 138 a moment ago
COMMIT <- completed on its own, once 138 was gone
Session C's own transcript:
UPDATE 1 <- also completed on its own
Neither B nor C was ever touched directly — terminating only the real root was enough to unblock the entire chain.
pg_blocking_pids(pid)
A built-in function returning an array of every backend PID currently blocking the given PID from proceeding. It requires no manual join against pg_locks to compute — calling it once per row of pg_stat_activity is enough to directly map an entire blocking chain, including chains several sessions deep, without hand-correlating lock types and relations.
idle in transaction (state)
A session state meaning a transaction is open, but the client is not currently sending any query — no wait_event, no active work, just an open transaction sitting there, still holding whatever locks it has already acquired. This is structurally different from a session actively waiting on a lock (state 'active', wait_event_type 'Lock'), and is very often the true root of a blocking chain precisely because it shows no obvious symptom of its own.
🔍 Build the Real Chain and See Who Is Actually Doing What
Create a genuine 3-session, 2-hop blocking chain, then check each session's real state and wait_event.
psql -U postgres -d beer_db -c "CREATE TABLE sql_lock_a (id int PRIMARY KEY, val int);" -c "CREATE TABLE sql_lock_b (id int PRIMARY KEY, val int);" -c "INSERT INTO sql_lock_a VALUES (1,1);" -c "INSERT INTO sql_lock_b VALUES (1,1);"(psql -U postgres -d beer_db -c "BEGIN;" -c "UPDATE sql_lock_a SET val=2 WHERE id=1;" -c "\\! sleep 60" -c "COMMIT;" > /tmp/sess_A.txt 2>&1 &) ; sleep 0.5; (psql -U postgres -d beer_db -c "BEGIN;" -c "UPDATE sql_lock_b SET val=2 WHERE id=1;" -c "UPDATE sql_lock_a SET val=3 WHERE id=1;" -c "COMMIT;" > /tmp/sess_B.txt 2>&1 &) ; sleep 1; (psql -U postgres -d beer_db -c "UPDATE sql_lock_b SET val=3 WHERE id=1;" > /tmp/sess_C.txt 2>&1 &) ; sleep 1psql -U postgres -d beer_db -c "SELECT pid, state, wait_event_type, wait_event, left(query,50) AS query FROM pg_stat_activity WHERE datname='beer_db' AND state != 'idle' AND query NOT ILIKE 'autovacuum%' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY pid;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE sql_lock_a (id int PRIMARY KEY, val int);" -c "CREATE TABLE sql_lock_b (id int PRIMARY KEY, val int);" -c "INSERT INTO sql_lock_a VALUES (1,1);" -c "INSERT INTO sql_lock_b VALUES (1,1);" SET CREATE TABLE CREATE TABLE INSERT 0 1 INSERT 0 1 student@lab:~$ (psql -U postgres -d beer_db -c "BEGIN;" -c "UPDATE sql_lock_a SET val=2 WHERE id=1;" -c "\\! sleep 60" -c "COMMIT;" > /tmp/sess_A.txt 2>&1 &) ; sleep 0.5; (psql -U postgres -d beer_db -c "BEGIN;" -c "UPDATE sql_lock_b SET val=2 WHERE id=1;" -c "UPDATE sql_lock_a SET val=3 WHERE id=1;" -c "COMMIT;" > /tmp/sess_B.txt 2>&1 &) ; sleep 1; (psql -U postgres -d beer_db -c "UPDATE sql_lock_b SET val=3 WHERE id=1;" > /tmp/sess_C.txt 2>&1 &) ; sleep 1 student@lab:~$ psql -U postgres -d beer_db -c "SELECT pid, state, wait_event_type, wait_event, left(query,50) AS query FROM pg_stat_activity WHERE datname='beer_db' AND state != 'idle' AND query NOT ILIKE 'autovacuum%' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY pid;" SET pid | state | wait_event_type | wait_event | query -----+---------------------+-----------------+---------------+----------------------------------------- 138 | idle in transaction | Client | ClientRead | UPDATE sql_lock_a SET val=2 WHERE id=1; 142 | active | Lock | transactionid | UPDATE sql_lock_a SET val=3 WHERE id=1; 145 | active | Lock | transactionid | UPDATE sql_lock_b SET val=3 WHERE id=1; (3 rows)
🗺️ Map the Real Chain Directly
Use pg_blocking_pids() to see exactly who is blocking whom, without any manual join.
psql -U postgres -d beer_db -c "SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE datname='beer_db' AND cardinality(pg_blocking_pids(pid)) > 0;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE datname='beer_db' AND cardinality(pg_blocking_pids(pid)) > 0;" SET pid | blocked_by -----+------------ 142 | {138} 145 | {142} (2 rows)
🎯 Terminate Only the Real Root, by State
Terminate the session identified by its actual idle-in-transaction state — not a hardcoded, guessed PID.
psql -U postgres -d beer_db -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND datname = 'beer_db';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND datname = 'beer_db';" SET pg_terminate_backend ----------------------- t (1 row)
✅ Confirm Both Victims Genuinely Unblocked on Their Own
Check both other sessions' own transcripts and confirm they completed successfully without being touched.
sleep 2; cat /tmp/sess_A.txt; echo ===B===; cat /tmp/sess_B.txt; echo ===C===; cat /tmp/sess_C.txtstudent@lab:~$ sleep 2; cat /tmp/sess_A.txt; echo ===B===; cat /tmp/sess_B.txt; echo ===C===; cat /tmp/sess_C.txt SET BEGIN UPDATE 1 FATAL: terminating connection due to administrator command server closed the connection unexpectedly connection to server was lost ===B=== SET BEGIN UPDATE 1 UPDATE 1 COMMIT ===C=== SET UPDATE 1
Lab 3.6.2 complete. Lock contention, genuinely diagnosed and genuinely resolved:\n\n\n Real 2-hop chain built : ✅ 3 sessions, genuine contention\n Chain mapped directly : ✅ pg_blocking_pids(), no manual join\n Real root identified and terminated : ✅ by state, not a guessed PID\n Both victims unblocked on their own : ✅ confirmed, never touched directly\n
Enable JavaScript to run the live terminal and track your progress.