Lab 7: Build a lock queue and find the session at the root of it

Objective

Most PostgreSQL lock incidents are not really about locks. They are about a queue.

The simple case — two sessions want the same row, one waits — is easy to diagnose and rarely causes an outage. The case that takes a site down looks completely different: one harmless long-running SELECT, one ALTER TABLE that has to wait for it, and then every ordinary query arriving afterwards stuck behind the ALTER even though none of them conflicts with the SELECT at all.

By the end of this lab you will have built both, watched the second one grow in the server log, and written the query that tells you which of five blocked sessions is the one to act on.

Architecture

Two scenarios against one table. The second is the one worth remembering.

flowchart TD
    subgraph S1["Scenario 1: a simple row conflict"]
      A1["session A\nBEGIN; UPDATE id=1"] --> T1["accounts"]
      B1["session B\nUPDATE id=1\nwaits on transactionid"] --> T1
    end
    subgraph S2["Scenario 2: the queue"]
      R["long reader\nBEGIN; SELECT count(*)\nholds AccessShareLock"] --> T2["accounts"]
      AL["ALTER TABLE\nwants AccessExclusiveLock\nWAITS"] --> T2
      Q1["SELECT arriving later"] --> AL
      Q2["SELECT arriving later"] --> AL
      Q3["SELECT arriving later"] --> AL
    end

Requirements

A PostgreSQL 18 cluster with superuser access. The lab creates and drops a database called lab07.
Several concurrent sessions. The lab launches background psql processes with docker exec -d; separate terminals work as well.
The PIDs in every captured output are from the lab run of 2026-08-28. Yours will differ; substitute them.

Scenario

An application is timing out. pg_stat_activity shows five sessions waiting on locks. Somebody proposes terminating the oldest one, or all of them.

Both are guesses. One of the five is the cause and four are victims, and the difference is visible in one query.

Tasks

Task 1 — A simple row conflict

LAB="$HOME/rbpg-lab-07"
mkdir -p "$LAB"

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE lab07;"
docker exec -i -u postgres rbpg-lab01 psql -X -d lab07 <<'SQL'
CREATE TABLE accounts(id int PRIMARY KEY, holder text, balance numeric);
INSERT INTO accounts SELECT g, 'holder-'||g, g*100 FROM generate_series(1,10000) g;
SQL

# Session A holds a row lock and then goes idle in transaction.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab07 <<'SQL'
BEGIN;
UPDATE accounts SET balance = balance + 1 WHERE id = 1;
\\\\! sleep 600
SQL\""

sleep 3

# Session B tries to update the same row.
docker exec -d rbpg-lab01 bash -c \
  "su - postgres -c \"psql -X -d lab07 -c 'UPDATE accounts SET balance = balance + 5 WHERE id = 1;'\""

sleep 3

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT pid, state, wait_event_type, wait_event,
         now()-query_start AS waited, left(query,50) AS query
  FROM pg_stat_activity WHERE datname='lab07' ORDER BY pid;" | tee "$LAB/simple-block.txt"

Read-only / Safeone holder, one waiter— Captured from the lab run of 2026-08-28.

$ psql -X -d lab07 -c "SELECT pid, state, wait_event_type, wait_event, now()-query_start AS waited, left(query,50) AS query FROM pg_stat_activity WHERE datname='lab07' ORDER BY pid;"

 pid  |        state        | wait_event_type |  wait_event   |     waited      |                       query                        
------+---------------------+-----------------+---------------+-----------------+----------------------------------------------------
9606 | idle in transaction | Client          | ClientRead    | 00:00:06.065528 | UPDATE accounts SET balance = balance + 1 WHERE id
9620 | active              | Lock            | transactionid | 00:00:03.030377 | UPDATE accounts SET balance = balance + 5 WHERE id
9627 | active              |                 |               | 00:00:00        | SELECT pid, state, wait_event_type, wait_event, no
(3 rows)

wait_event_type = 'Lock' is the signal. Unlike Client:ClientRead, which every idle session shows, a Lock wait always means one session is waiting for another.

The wait event is transactionid, not tuple or row. Hold that thought until Task 3.

Task 2 — Ask the server who is blocking whom

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query,45) AS query
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0;" | tee -a "$LAB/simple-block.txt"

Read-only / Safepg_blocking_pids answers directly— Captured from the lab run of 2026-08-28.

$ psql -X -d lab07 -c "SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query,45) AS query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;"

 pid  | blocked_by |                     query                     
------+------------+-----------------------------------------------
9620 | {9606}     | UPDATE accounts SET balance = balance + 5 WHE
(1 row)

pg_blocking_pids returns an array, because a session can be blocked by several others at once — a request for an exclusive lock has to wait for every current shared holder. cardinality(...) > 0 is the idiomatic way to filter for blocked sessions, and it is much easier to get right than the self-join over pg_locks that people often write instead.

Task 3 — Look at the locks themselves

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT pid, locktype, relation::regclass AS rel, transactionid, mode, granted
  FROM pg_locks WHERE pid IN (9606,9620) ORDER BY pid, locktype;" \
  | tee -a "$LAB/simple-block.txt"

Read-only / Safeten locks, and exactly one of them ungranted— Captured from the lab run of 2026-08-28. Substitute your own PIDs.

$ psql -X -d lab07 -c "SELECT pid, locktype, relation::regclass AS rel, transactionid, mode, granted FROM pg_locks WHERE pid IN (9606,9620) ORDER BY pid, locktype;"

 pid  |   locktype    |      rel      | transactionid |       mode       | granted 
------+---------------+---------------+---------------+------------------+---------
9606 | relation      | accounts_pkey |               | RowExclusiveLock | t
9606 | relation      | accounts      |               | RowExclusiveLock | t
9606 | transactionid |               |           799 | ExclusiveLock    | t
9606 | virtualxid    |               |               | ExclusiveLock    | t
9620 | relation      | accounts      |               | RowExclusiveLock | t
9620 | relation      | accounts_pkey |               | RowExclusiveLock | t
9620 | transactionid |               |           800 | ExclusiveLock    | t
9620 | transactionid |               |           799 | ShareLock        | f
9620 | tuple         | accounts      |               | ExclusiveLock    | t
9620 | virtualxid    |               |               | ExclusiveLock    | t
(10 rows)

Read the one row with granted = f:

9620 | transactionid | 799 | ShareLock | f

Session 9620 is waiting for a ShareLock on transaction 799 — which is the transaction id session 9606 holds an ExclusiveLock on. It is not waiting for a lock on a row, or on the table.

Notice also that both sessions hold RowExclusiveLock on accounts and both are granted. Table-level write locks do not conflict with each other; that is the entire point of row-level concurrency. The conflict is elsewhere.

Why waiting for a row means waiting for a transaction

There is no lock table entry for a locked row. With millions of rows that would be unaffordable, so PostgreSQL does not do it. Instead, the xmax field in the row version itself records the transaction that locked or updated it — the lock is stored in the row, on disk, costing nothing extra.

A session that finds a row with a live xmax therefore has nothing to wait on at the row level. What it does is take a ShareLock on the transaction id it found in xmax, and block. Every transaction holds an ExclusiveLock on its own id for its whole lifetime and releases it at commit or rollback, so the waiter is woken exactly when the other transaction ends.

This is why row conflicts always appear as transactionid waits, why pg_locks never grows in proportion to the number of rows you update, and why a session’s tuple lock — visible here, granted — is only the short-lived lock that serialises the queue for one specific row rather than the row lock itself.

Task 4 — Build the queue

This is the scenario that causes outages. Clear the first one and start again:

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE datname='lab07' AND pid <> pg_backend_pid();"

# 1. A long-running reader. Nothing about this is unusual or wrong.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab07 <<'SQL'
BEGIN;
SELECT count(*) FROM accounts;
\\\\! sleep 600
SQL\""
sleep 2

# 2. A migration that needs AccessExclusiveLock.
docker exec -d rbpg-lab01 bash -c \
  "su - postgres -c \"psql -X -d lab07 -c 'ALTER TABLE accounts ADD COLUMN note text;'\""
sleep 2

# 3. Three ordinary readers arriving afterwards.
for i in 1 2 3; do
  docker exec -d rbpg-lab01 bash -c \
    "su - postgres -c \"psql -X -d lab07 -c 'SELECT count(*) FROM accounts;'\""
done
sleep 3

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT pid, state, wait_event_type, wait_event,
         now()-query_start AS waited, left(query,42) AS query
  FROM pg_stat_activity
  WHERE datname='lab07' AND pid <> pg_backend_pid()
  ORDER BY query_start;" | tee "$LAB/lock-queue.txt"

Service impact possiblefour sessions waiting, and only one of them should be— Captured from the lab run of 2026-08-28.

$ psql -X -d lab07 -c "SELECT pid, state, wait_event_type, wait_event, now()-query_start AS waited, left(query,42) AS query FROM pg_stat_activity WHERE datname='lab07' AND pid <> pg_backend_pid() ORDER BY query_start;"

 pid  |        state        | wait_event_type | wait_event |     waited      |                   query                    
------+---------------------+-----------------+------------+-----------------+--------------------------------------------
9670 | idle in transaction | Client          | ClientRead | 00:00:07.154455 | SELECT count(*) FROM accounts;
9684 | active              | Lock            | relation   | 00:00:05.121589 | ALTER TABLE accounts ADD COLUMN note text;
9702 | active              | Lock            | relation   | 00:00:03.081987 | SELECT count(*) FROM accounts;
9714 | active              | Lock            | relation   | 00:00:03.055427 | SELECT count(*) FROM accounts;
9720 | active              | Lock            | relation   | 00:00:03.029209 | SELECT count(*) FROM accounts;
(5 rows)

Three plain SELECT count(*) statements are blocked. Two SELECT statements never conflict with each other. Nothing here should be blocking a reader, and yet the table is effectively unreadable.

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query,40) AS query
  FROM pg_stat_activity
  WHERE datname='lab07' AND cardinality(pg_blocking_pids(pid))>0
  ORDER BY pid;" | tee -a "$LAB/lock-queue.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT pid, mode, granted FROM pg_locks
  WHERE relation = 'accounts'::regclass AND locktype='relation'
  ORDER BY granted DESC, pid;" | tee -a "$LAB/lock-queue.txt"

Read-only / Safethe chain, and the queue on the relation— Captured from the lab run of 2026-08-28. One granted lock, four waiting.

$ pg_blocking_pids over the blocked sessions, then the relation locks on accounts

 pid  | blocked_by |                  query                   
------+------------+------------------------------------------
9684 | {9670}     | ALTER TABLE accounts ADD COLUMN note tex
9702 | {9684}     | SELECT count(*) FROM accounts;
9714 | {9684}     | SELECT count(*) FROM accounts;
9720 | {9684}     | SELECT count(*) FROM accounts;
(4 rows)

pid  |        mode         | granted 
------+---------------------+---------
9670 | AccessShareLock     | t
9684 | AccessExclusiveLock | f
9702 | AccessShareLock     | f
9714 | AccessShareLock     | f
9720 | AccessShareLock     | f
(5 rows)

There is the whole mechanism in five rows. AccessShareLock — what a plain SELECT takes — does not conflict with AccessShareLock. Session 9702 could have been granted its lock immediately alongside 9670.

It was not, because the lock manager does not let a request jump the queue ahead of an incompatible request already waiting. Session 9684 asked for AccessExclusiveLock first, so everything arriving after it queues behind it, whether or not it conflicts with the current holder.

This is how one slow query takes out a table

Without the queueing rule, AccessExclusiveLock requests on a busy table could be starved indefinitely — a steady trickle of overlapping readers would mean there is never an instant with no readers, and the ALTER would wait forever. The rule prevents that.

The consequence is that a single ALTER TABLE, CREATE INDEX (without CONCURRENTLY), VACUUM FULL, REINDEX or TRUNCATE that has to wait behind one long transaction converts into a full stop on that table for every query that arrives afterwards. The table is not locked by the long reader. It is locked by the queue in front of it.

Migrations run during business hours fail this way constantly, and the symptom — “the whole application stopped” — looks nothing like the cause, which is one report that has been running for four minutes.

Task 5 — Find the session at the root of the chain

Four sessions are blocked. Terminating any of the three SELECT statements achieves nothing. Terminating the ALTER releases the three readers but abandons the migration. The session to act on is the one that is blocking somebody and is not itself blocked:

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT a.pid, a.state, now()-a.xact_start AS xact_age, left(a.query,40) AS query,
         (SELECT count(*) FROM pg_stat_activity b
          WHERE a.pid = ANY(pg_blocking_pids(b.pid))) AS blocking_count
  FROM pg_stat_activity a
  WHERE cardinality(pg_blocking_pids(a.pid)) = 0
    AND EXISTS (SELECT 1 FROM pg_stat_activity b
                WHERE a.pid = ANY(pg_blocking_pids(b.pid)))
  ORDER BY xact_age DESC;" | tee "$LAB/root-of-chain.txt"

Read-only / Safeone row: the session everything traces back to— Captured from the lab run of 2026-08-28.

$ the root-of-chain query over pg_stat_activity

 pid  |        state        |    xact_age     |             query              | blocking_count 
------+---------------------+-----------------+--------------------------------+----------------
9670 | idle in transaction |    00:00:22.392787 | SELECT count(*) FROM accounts; |              1
(1 row)

The two conditions are the whole idea: blocked by nobody, and blocking somebody. That combination is the head of every chain, and on a real incident it usually returns one row out of dozens of blocked sessions.

Note what it found. Not the ALTER TABLE that four sessions are directly waiting on, but the idle in transaction session holding an AccessShareLock it stopped needing twenty seconds ago — exactly the state Lab 6 taught you to look for.

docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_terminate_backend(9670);"
sleep 3
docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT count(*) AS still_blocked FROM pg_stat_activity
  WHERE datname='lab07' AND cardinality(pg_blocking_pids(pid))>0;"

Destructiveone termination, the whole chain drains— Captured from the lab run of 2026-08-28. The ALTER and all three readers completed.

$ pg_terminate_backend against the root session, then re-check for blocked sessions

 pg_terminate_backend 
----------------------
t
(1 row)

still_blocked 
---------------
           0
(1 row)

ALTER TABLE
count 
-------
10000
(1 row)

One session terminated. The ALTER TABLE acquired its lock and completed, and all three readers ran.

Task 6 — Prevent it: lock_timeout

The migration should never have been able to form that queue. Bound how long it will wait:

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "ALTER TABLE accounts DROP COLUMN note;"

docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab07 <<'SQL'
BEGIN;
SELECT count(*) FROM accounts;
\\\\! sleep 600
SQL\""
sleep 2

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c \
  "SET lock_timeout = '2s'; ALTER TABLE accounts ADD COLUMN note text;" \
  | tee "$LAB/prevention.txt"

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT count(*) AS waiting FROM pg_stat_activity
  WHERE datname='lab07' AND wait_event_type='Lock';"
docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "SELECT count(*) FROM accounts;"

Configuration changethe migration gives up and nothing queues behind it— Captured from the lab run of 2026-08-28. The table stays fully readable throughout.

$ psql -X -d lab07 -c "SET lock_timeout = '2s'; ALTER TABLE accounts ADD COLUMN note text;" then check for waiters

SET
ERROR:  canceling statement due to lock timeout

waiting 
---------
     0
(1 row)

count 
-------
10000
(1 row)

The migration failed. That is the correct outcome and a much better one: a failed migration you retry in a moment costs nothing, and the table never became unreadable.

Set lock_timeout on every migration, without exception

Any statement that takes AccessExclusiveLock should run with lock_timeout set — a few seconds is usually right. The pattern is:

SET lock_timeout = '3s';
ALTER TABLE accounts ADD COLUMN note text;

and retry on failure, ideally with a short backoff. Most retries succeed within a minute or two, because the long transactions that block them are transient.

The two failure modes this removes are the queue you just built, and the subtler one where the migration eventually acquires its lock after eight minutes of accumulated queue and then completes in twelve milliseconds, leaving no evidence that anything went wrong.

Note that lock_timeout bounds waiting for a lock only. A statement that holds a lock for a long time once it has one is a different problem, bounded by statement_timeout.

Task 7 — Record it: log_lock_waits

Prevention needs the queue to be visible in the first place. Check the default on your own server rather than assuming it:

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT name, setting, boot_val, reset_val, source
  FROM pg_settings WHERE name IN ('log_lock_waits','deadlock_timeout') ORDER BY name;"

Read-only / Safeoff by default in 18.6, including the compiled-in default— Captured from the lab run of 2026-08-28. boot_val is the value compiled into the server.

$ psql -X -c "SELECT name, setting, boot_val, reset_val, source FROM pg_settings WHERE name IN ('log_lock_waits','deadlock_timeout') ORDER BY name;"

       name       | setting | unit 
------------------+---------+------
deadlock_timeout | 1000    | ms
log_lock_waits   | off     | 
(2 rows)

    name      | setting | boot_val | reset_val | source  
----------------+---------+----------+-----------+---------
log_lock_waits | off     | off      | off       | default

Turn it on and rebuild the queue:

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM SET log_lock_waits = on;"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"

docker exec -d rbpg-lab01 bash -c \
  "su - postgres -c \"psql -X -d lab07 -c 'ALTER TABLE accounts ADD COLUMN note text;'\""
sleep 4
for i in 1 2; do
  docker exec -d rbpg-lab01 bash -c \
    "su - postgres -c \"psql -X -d lab07 -c 'SELECT count(*) FROM accounts;'\""
done
sleep 3

docker exec rbpg-lab01 grep -E "still waiting|Wait queue" \
  /var/log/postgresql/postgresql-18-main.log | tail -6 | tee -a "$LAB/prevention.txt"

Read-only / Safethe queue growing in the log, with nobody watching— Captured from the lab run of 2026-08-28. Note the wait queue lengthening between the two DETAIL lines.

$ grep the cluster log for lock wait messages

2026-08-28 00:43:21.252 UTC [9880] postgres@lab07 LOG:  process 9880 still waiting for AccessExclusiveLock on relation 16415 of database 16414 after 1000.141 ms
2026-08-28 00:43:21.252 UTC [9880] postgres@lab07 DETAIL:  Process holding the lock: 9796. Wait queue: 9880.
2026-08-28 00:43:21.252 UTC [9880] postgres@lab07 STATEMENT:  ALTER TABLE accounts ADD COLUMN note text;
2026-08-28 00:43:36.617 UTC [9904] postgres@lab07 DETAIL:  Process holding the lock: 9796. Wait queue: 9880, 9904, 9910.
2026-08-28 00:43:36.644 UTC [9910] postgres@lab07 DETAIL:  Process holding the lock: 9796. Wait queue: 9880, 9904, 9910.

That is the whole incident, reconstructable after the fact: the lock mode requested, the process holding it, the wait queue in order, the statement, and the timestamps.

Two practical notes. The threshold for logging is deadlock_timeout (1 second by default), not a separate setting — a wait is logged once it has lasted that long. And the relation is reported by OID, so resolve it:

docker exec -u postgres rbpg-lab01 psql -X -d lab07 -c "
  SELECT 16415::regclass AS relation,
         (SELECT datname FROM pg_database WHERE oid=16414) AS database;"

Read-only / Safeturning the OIDs in the log back into names— Captured from the lab run of 2026-08-28. The cast must run in the database that owns the relation.

$ psql -X -d lab07 -c "SELECT 16415::regclass AS relation, (SELECT datname FROM pg_database WHERE oid=16414) AS database;"

 relation | database 
----------+----------
accounts | lab07
(1 row)

The ::regclass cast resolves an OID only in the database that contains it, so run it against the database the log line names — which is why the log gives you the database OID too.

Validation

test -s "$LAB/simple-block.txt"  && echo "OK simple-block"
test -s "$LAB/lock-queue.txt"    && echo "OK lock-queue"
test -s "$LAB/root-of-chain.txt" && echo "OK root-of-chain"
test -s "$LAB/prevention.txt"    && echo "OK prevention"

grep -q "transactionid" "$LAB/simple-block.txt"  && echo "OK transactionid wait captured"
grep -q "AccessExclusiveLock" "$LAB/lock-queue.txt" && echo "OK queue captured"
grep -q "lock timeout" "$LAB/prevention.txt"     && echo "OK lock_timeout demonstrated"
grep -q "Wait queue"   "$LAB/prevention.txt"     && echo "OK log_lock_waits demonstrated"

Questions to answer without looking anything up:

Two sessions update the same row. What does the waiting session’s pg_locks row say it is waiting for, and why is it not a row?
Three SELECT statements are blocked on a table. Neither the other readers nor a writer is holding a conflicting lock. What is?
Five sessions are blocked. Which one do you terminate, and what are the two conditions that identify it?
Your ALTER TABLE completed in 12 ms after waiting 8 minutes. What happened to the application during those 8 minutes, and would you know?
Which setting controls how long a lock wait must last before log_lock_waits records it?

Expected Outcome

You have produced both a simple row conflict and the queue that turns a long read into an outage, and you have the two queries that matter.

-- Who is blocked, and by whom?
SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, left(query,60)
FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- Which one session is the root of it all?
SELECT a.pid, a.state, now()-a.xact_start AS xact_age, left(a.query,60)
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) = 0
  AND EXISTS (SELECT 1 FROM pg_stat_activity b WHERE a.pid = ANY(pg_blocking_pids(b.pid)))
ORDER BY xact_age DESC;

And two settings to put in place before you need them: lock_timeout on anything that takes AccessExclusiveLock, and log_lock_waits = on so the next queue leaves a record.

Troubleshooting

Neither session appears to block. They are touching different rows, or one of them committed. Row locks conflict only on the same row — confirm both statements target the same primary key, and that neither transaction has ended.

pg_blocking_pids returns an empty array for a session you can see waiting. It reports lock waits only. A session waiting on I/O, on a client, or on a synchronous standby is waiting on something else — wait_event_type and wait_event name what.

pg_locks shows two rows for one session on the same relation. A transaction commonly holds both a relation-level lock and a transactionid or tuple lock. The granted column is the one to read first: an ungranted row is a wait, a granted row is a hold.

The queue in Task 4 does not form. The ACCESS EXCLUSIVE statement must arrive after the long reader and before the later readers. Run them in that order, with a short pause between, and check the ordering in pg_locks by granted.

lock_timeout does not fire. It bounds the wait for a lock, not the execution of a statement. If your statement acquired its lock and is merely slow, statement_timeout is the relevant setting.

Nothing appears in the log after setting log_lock_waits. Its boot value is off in PostgreSQL 18.6, so it must be set explicitly, and the wait must exceed deadlock_timeout — one second by default — before a line is written. Check SHOW log_lock_waits; returns on and that the reload took effect.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE datname='lab07' AND pid <> pg_backend_pid();"

docker exec -u postgres rbpg-lab01 psql -X -c "ALTER SYSTEM RESET log_lock_waits;"
docker exec -u postgres rbpg-lab01 psql -X -c "SELECT pg_reload_conf();"
docker exec -u postgres rbpg-lab01 psql -X -c "DROP DATABASE IF EXISTS lab07;"

docker exec -u postgres rbpg-lab01 psql -X -c "SHOW log_lock_waits;"

log_lock_waits should be back to off — though on a real server, leaving it on is the better choice.

Production notes

Set log_lock_waits = on in every production cluster. Its boot value is off, so an untouched cluster records nothing, and a lock queue leaves no evidence once it has cleared. One log line per wait longer than deadlock_timeout is a trivial cost.
Set lock_timeout before any statement that takes AccessExclusiveLock — every ALTER TABLE, most DROP, VACUUM FULL, REINDEX without CONCURRENTLY. Without it, a brief strong lock that cannot be granted immediately becomes an unbounded queue behind a single long reader.
Act on the head of the queue, not on the queue. The root query in Expected Outcome finds the one session that is blocking and is not itself blocked, and terminating that one clears everything behind it.
Capture pg_locks and the blocking tree before you clear the queue. Neither exists afterwards, and the incident review will ask what was waiting.

What You Learned

A lock queue is not a lock conflict. One long reader plus one strong lock request stops every subsequent reader, none of which conflicts with the reader at all.
pg_blocking_pids gives you the edge; the recursive query gives you the root. Terminating a session in the middle of the chain clears nothing.
pg_locks.granted separates holds from waits, and a session legitimately appears more than once.
lock_timeout bounds waiting for a lock, which is a different thing from statement_timeout bounding execution.
log_lock_waits is off by default in 18.6 — verified from pg_settings.boot_val — so the evidence you want during an incident has to be enabled before it.
The queue clears the instant the head does. That is what makes finding the root worth the extra query.

Objectives

Prerequisites