Long-Running Queries and Locks

Trace a live blocking chain with pg_blocking_pids(), then choose between pg_cancel_backend and pg_terminate_backend to resolve it

This lab's terminal, like a real student's, has exactly one interactive prompt — but the scenario needs two genuinely concurrent database sessions: one holding a lock, one stuck waiting behind it. The trick is the shell's own job control: appending & to a command backgrounds it immediately, freeing the prompt for the next command while the backgrounded psql process keeps its own connection open in the background, writing its output to a log file instead of the screen.\n\nYou will background a transaction that grabs an ACCESS EXCLUSIVE lock and then sleeps, background a second query that has no choice but to wait behind it, and then — from your one remaining live prompt — use pg_stat_activity and pg_blocking_pids() to prove exactly which session is blocking which, before resolving it with the right tool for the situation.

Backgrounding a Session From bash

psql -U postgres -d beer_db -c "BEGIN; LOCK TABLE monitoring.orders IN ACCESS EXCLUSIVE MODE; SELECT pg_sleep(45); COMMIT;" > /tmp/blocker.log 2>&1 &

The trailing & hands this command to the shell's job control and returns your prompt immediately — the psql process keeps its connection open, its lock held, and its output redirected to a file instead of interleaving with whatever you type next.

Finding Blocked and Blocking Sessions

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid();

A session waiting on a lock shows wait_event_type = 'Lock'. A session actively running a query — including one stuck inside pg_sleep() — shows state = 'active' with no lock wait at all.

Tracing the Chain Without Copy-Pasting a PID

SELECT pid, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids(pid) returns an array of every PID currently blocking the given session — directly, or transitively through a longer chain. No need to eyeball two separate query outputs and match PIDs by hand.

Cancel vs Terminate

-- Cancels only the CURRENT statement; the session and its transaction survive if there was no open transaction
SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE query LIKE '%pg_sleep%';

-- Ends the entire session, rolling back any open transaction and closing the connection
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction';

Both accept a subselect instead of a hand-typed PID — the same "don't force a copy-paste" habit used throughout this course.

idle in transaction

A session that has an open transaction (BEGIN was run) but is not currently executing any statement — it is simply waiting for the client to send the next command. It can still be holding locks acquired earlier in that same transaction, making it just as dangerous as an actively-running query, but pg_cancel_backend() has no running statement to interrupt on it.

pg_blocking_pids(pid)

Given a backend's PID, returns an array of every PID directly or transitively blocking it — the entire dependency chain in one function call, rather than manually cross-referencing pg_locks entries by relation and lock mode.

🔒 Start the Blocker

Background a transaction that locks the orders table in ACCESS EXCLUSIVE mode and then sleeps, holding the lock the whole time.

psql -U postgres -d beer_db -c "BEGIN; LOCK TABLE monitoring.orders IN ACCESS EXCLUSIVE MODE; SELECT pg_sleep(45); COMMIT;" > /tmp/blocker.log 2>&1 &

student@lab:~$ psql -U postgres -d beer_db -c "BEGIN; LOCK TABLE monitoring.orders IN ACCESS EXCLUSIVE MODE; SELECT pg_sleep(45); COMMIT;" > /tmp/blocker.log 2>&1 & [1] 140

⏳ Start the Session That Blocks Behind It

Background a second connection that needs the same table — it has no choice but to wait.

psql -U postgres -d beer_db -c "SELECT count(*) FROM monitoring.orders;" > /tmp/blocked.log 2>&1 &

student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM monitoring.orders;" > /tmp/blocked.log 2>&1 & [2] 146

🔍 Inspect Both Sessions

From your live prompt, check pg_stat_activity for both backgrounded sessions and see exactly what each one is waiting on.

psql -U postgres -d beer_db -c "SELECT pid, state, wait_event_type, wait_event, left(query,40) AS query FROM pg_stat_activity WHERE datname = 'beer_db' AND pid <> pg_backend_pid() ORDER BY pid;"

🔗 Trace the Blocking Chain

Use pg_blocking_pids() to identify exactly which session is blocking which, without matching PIDs by eye.

psql -U postgres -d beer_db -c "SELECT pid, pg_blocking_pids(pid) AS blocked_by, state FROM pg_stat_activity WHERE pid <> pg_backend_pid() 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, state FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND cardinality(pg_blocking_pids(pid)) > 0;" SET pid | blocked_by | state -----+------------+-------- 146 | {140} | active (1 row)

✂️ Cancel the Blocker

Cancel the blocking session's current statement using a subselect on its query text — never a hand-typed PID.

psql -U postgres -d beer_db -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND query LIKE 'BEGIN; LOCK TABLE monitoring.orders%pg_sleep%';"

pg_cancel_backend t (1 row)

📄 Confirm the Blocked Query Went Through

Check both log files: the previously-blocked session should have completed the instant the lock released.

cat /tmp/blocked.log

cat /tmp/blocker.log

student@lab:~$ cat /tmp/blocked.log SET count ------- 5000 (1 row) [2]+ Done psql -U postgres -d beer_db -c "SELECT count(*) FROM monitoring.orders;" 1>/tmp/blocked.log 2>&1 student@lab:~$ cat /tmp/blocker.log SET BEGIN LOCK TABLE ERROR: canceling statement due to user request

💤 Start a Genuinely Idle-in-Transaction Session

Background a session that opens a transaction, takes a lock, and then goes idle waiting for its next command — the case pg_cancel_backend cannot fix.

(echo "BEGIN;"; echo "LOCK TABLE monitoring.orders IN ACCESS EXCLUSIVE MODE;"; sleep 30) | psql -U postgres -d beer_db > /tmp/idle.log 2>&1 &

psql -U postgres -d beer_db -c "SELECT pid, state, left(query,40) AS query FROM pg_stat_activity WHERE datname = 'beer_db' AND pid <> pg_backend_pid();"

student@lab:~$ (echo "BEGIN;"; echo "LOCK TABLE monitoring.orders IN ACCESS EXCLUSIVE MODE;"; sleep 30) | psql -U postgres -d beer_db > /tmp/idle.log 2>&1 & [1] 140 student@lab:~$ psql -U postgres -d beer_db -c "SELECT pid, state, left(query,40) AS query FROM pg_stat_activity WHERE datname = 'beer_db' AND pid <> pg_backend_pid();" SET pid | state | query -----+----------------------+------------------------------------------ 140 | idle in transaction | LOCK TABLE monitoring.orders IN ACCESS E (1 row)

🛑 Terminate the Idle Session

pg_cancel_backend would do nothing here — end the whole session with pg_terminate_backend instead, then confirm it is gone.

psql -U postgres -d beer_db -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction';"

psql -U postgres -d beer_db -c "SELECT pid, state FROM pg_stat_activity WHERE datname = 'beer_db' AND pid <> pg_backend_pid();"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction';" SET pg_terminate_backend ----------------------- t (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT pid, state FROM pg_stat_activity WHERE datname = 'beer_db' AND pid <> pg_backend_pid();" SET pid | state -----+------- (0 rows)

Lab 2.4.2 complete. You can now trace and resolve a live blocking chain end to end:\n\n\n Simulated a second session : ✅ backgrounded with bash job control\n pg_stat_activity : ✅ read wait_event_type / wait_event\n pg_blocking_pids() : ✅ traced the chain in one query\n pg_cancel_backend : ✅ stopped an active statement\n pg_terminate_backend : ✅ ended an idle-in-transaction session\n

Enable JavaScript to run the live terminal and track your progress.