Lab 8: Reproduce a deadlock, read the report, and make it stop happening

Objective

A deadlock is the one lock problem PostgreSQL resolves for you. It detects the cycle, picks a victim, aborts it, and the other transaction proceeds. Nothing hangs and nobody has to intervene.

That is exactly why deadlocks get mishandled. The application sees an error it can retry, the retry usually succeeds, and the underlying ordering bug is never fixed — until traffic doubles and the retry rate goes with it.

By the end of this lab you will have caused a deadlock deliberately, read the report from both ends, and proved that a change costing nothing at runtime makes it impossible. You will also produce a second deadlock containing no UPDATE at all, because the shape people are taught to look for is not the only shape there is.

Architecture

Two transactions, two rows, opposite orders. Then the same two transactions with the same order.

flowchart TD
    subgraph D["deadlock: opposite orders"]
      A1["A: UPDATE id=1"] --> A2["A: UPDATE id=2\nwaits for B"]
      B1["B: UPDATE id=2"] --> B2["B: UPDATE id=1\nwaits for A"]
      A2 -.cycle.-> B2
      B2 -.cycle.-> A2
    end
    subgraph F["no deadlock: same order"]
      C1["C: UPDATE id=1"] --> C2["C: UPDATE id=2"]
      E1["E: UPDATE id=1\nwaits for C, then proceeds"] --> E2["E: UPDATE id=2"]
    end

Requirements

A PostgreSQL 18 cluster with superuser access. The lab creates and drops a database called lab08.
Two sessions whose timing you control. The lab uses \! sleep inside a psql heredoc so each transaction pauses at a known point; two terminals typing by hand works too, and is arguably clearer.
log_lock_waits = on for Task 3. The lab turns it on and back off.

Scenario

An application logs a handful of deadlock detected errors every day. It retries and the retries succeed, so nobody has looked at it. You have been asked whether it matters.

To answer that you need to know what the deadlock actually is, which of the two competing transactions loses, and whether the fix is a configuration change or a code change.

Tasks

Task 1 — Cause a deadlock

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

docker exec -i -u postgres rbpg-lab01 psql -X -c "CREATE DATABASE lab08;"
docker exec -i -u postgres rbpg-lab01 psql -X -d lab08 <<'SQL'
CREATE TABLE accounts(id int PRIMARY KEY, holder text, balance numeric);
INSERT INTO accounts VALUES (1,'alice',1000),(2,'bob',1000);
SQL

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();"

# Session A: touch row 1, then row 2.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab08 -v ON_ERROR_STOP=0 <<'SQL'
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
\\\\! sleep 3
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
SQL\" > /tmp/l8a.out 2>&1"

sleep 1

# Session B: touch row 2, then row 1. The opposite order.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab08 -v ON_ERROR_STOP=0 <<'SQL'
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
\\\\! sleep 3
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;
SQL\" > /tmp/l8b.out 2>&1"

sleep 8
docker exec rbpg-lab01 cat /tmp/l8a.out /tmp/l8b.out | tee "$LAB/deadlock-client.txt"

Service impact possibleone transaction commits, the other is aborted by the server— Captured from the lab run of 2026-08-28. Session A's output first, then session B's.

$ cat the output of both sessions

BEGIN
UPDATE 1
UPDATE 1
COMMIT

BEGIN
UPDATE 1
ERROR:  deadlock detected
DETAIL:  Process 10011 waits for ShareLock on transaction 810; blocked by process 9996.
Process 9996 waits for ShareLock on transaction 811; blocked by process 10011.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "accounts"
ROLLBACK

Read the DETAIL. It states the cycle explicitly, in two lines:

Process 10011 waits for transaction 810, held by process 9996.
Process 9996 waits for transaction 811, held by process 10011.

Each is waiting for the other. Neither can make progress, and no amount of waiting will change that — which is what makes a deadlock different from the queue in Lab 7, where waiting does eventually work.

Note the wait type: ShareLock on transaction, the same mechanism Lab 7 established. A deadlock is not a special kind of lock. It is an ordinary pair of transaction waits that happen to form a cycle.

The final ROLLBACK is worth noticing too. The COMMIT in the script could not commit, because the transaction was already aborted; psql reported the rollback that actually happened.

Task 2 — Read the server’s copy

The client error ends with HINT: See server log for query details. That hint is not decoration — the server log genuinely contains something the client cannot have.

docker exec rbpg-lab01 grep -A8 "deadlock detected" \
  /var/log/postgresql/postgresql-18-main.log | tee "$LAB/deadlock-server.txt"

Read-only / Safethe server names both statements; the client only knew its own— Captured from the lab run of 2026-08-28.

$ grep the cluster log for the deadlock report

2026-08-28 00:46:08.701 UTC [10011] postgres@lab08 ERROR:  deadlock detected
2026-08-28 00:46:08.701 UTC [10011] postgres@lab08 DETAIL:  Process 10011 waits for ShareLock on transaction 810; blocked by process 9996.
Process 9996 waits for ShareLock on transaction 811; blocked by process 10011.
Process 10011: UPDATE accounts SET balance = balance + 50 WHERE id = 1;
Process 9996: UPDATE accounts SET balance = balance + 100 WHERE id = 2;
2026-08-28 00:46:08.701 UTC [10011] postgres@lab08 HINT:  See server log for query details.
2026-08-28 00:46:08.701 UTC [10011] postgres@lab08 CONTEXT:  while updating tuple (0,1) in relation "accounts"
2026-08-28 00:46:08.701 UTC [10011] postgres@lab08 STATEMENT:  UPDATE accounts SET balance = balance + 50 WHERE id = 1;
2026-08-28 00:46:08.701 UTC [9996] postgres@lab08 LOG:  process 9996 acquired ShareLock on transaction 811 after 2035.038 ms

Two extra lines make all the difference:

Process 10011: UPDATE accounts SET balance = balance + 50 WHERE id = 1;
Process 9996:  UPDATE accounts SET balance = balance + 100 WHERE id = 2;

The application that received the error only ever knew its own statement. Diagnosing a deadlock from the application side alone means guessing at the other half of the cycle. From the server log both halves are named, and the ordering conflict is visible immediately: one statement is working on id = 1 and the other on id = 2.

The last line is the survivor’s side of the story: process 9996 acquired ShareLock on transaction 811 after 2035.038 ms. It waited two seconds, the victim was aborted, and it got its lock. That two-second delay is deadlock_timeout plus the detection pass.

Task 3 — Confirm the aftermath

docker exec -u postgres rbpg-lab01 psql -X -d lab08 -c \
  "SELECT id, holder, balance FROM accounts ORDER BY id;" | tee "$LAB/aftermath.txt"

Read-only / Safethe survivor's transfer applied in full, the victim's not at all— Captured from the lab run of 2026-08-28. Both balances started at 1000.

$ psql -X -d lab08 -c "SELECT id, holder, balance FROM accounts ORDER BY id;"

 id | holder | balance 
----+--------+---------
1 | alice  |     900
2 | bob    |    1100
(2 rows)

Session A moved 100 from row 1 to row 2 and committed: 900 and 1100. Session B’s transfer of 50 in the other direction left no trace at all — not even the first UPDATE, which had already succeeded before the deadlock was detected.

That is the property that makes deadlocks safe to retry. The victim is rolled back entirely, so there is no partial state to reason about. It is also the property that makes them expensive: everything the victim had done is thrown away, and on a long transaction that can be a great deal of work.

How the cycle is found, and who is chosen to die

There is no continuous deadlock monitor. Detection is triggered by waiting: when a backend has waited for a lock for deadlock_timeout (1 second by default), it runs a check before going back to sleep.

That check walks the graph of “who is waiting for whom” looking for a cycle back to itself. Most of the time there is none — the backend is in an ordinary queue — and it simply resumes waiting. The cost of the check is only paid by sessions that have already waited a full second.

When a cycle is found, the backend that ran the check is the one that aborts. That means the victim is essentially whichever transaction hit its deadlock_timeout first, not the newest, the smallest or the least important. You cannot influence the choice, and you should not build retry logic that assumes a particular transaction will be the survivor.

Lowering deadlock_timeout makes detection faster and makes every ordinary lock wait pay for a graph search. The default of one second is deliberately far above normal lock wait times.

Task 4 — Remove the deadlock with an ordering change

Nothing about the locking needs to change. Both transactions still take both row locks. They just take them in the same order.

docker exec -i -u postgres rbpg-lab01 psql -X -d lab08 -c "UPDATE accounts SET balance = 1000;"

# Session C: id 1 then id 2.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab08 -v ON_ERROR_STOP=0 <<'SQL'
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
\\\\! sleep 3
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
SQL\" > /tmp/l8c.out 2>&1"

sleep 1

# Session D: also id 1 then id 2, even though its transfer runs the other way.
docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab08 -v ON_ERROR_STOP=0 <<'SQL'
BEGIN;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
\\\\! sleep 3
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
COMMIT;
SQL\" > /tmp/l8d.out 2>&1"

sleep 12
docker exec rbpg-lab01 cat /tmp/l8c.out /tmp/l8d.out | tee "$LAB/ordered-fix.txt"
docker exec -u postgres rbpg-lab01 psql -X -d lab08 -c \
  "SELECT id, holder, balance FROM accounts ORDER BY id;" | tee -a "$LAB/ordered-fix.txt"

Read-only / Safethe identical workload, both transactions committing— Captured from the lab run of 2026-08-28. Session D waited for C rather than deadlocking with it.

$ cat both session outputs, then read the table

BEGIN
UPDATE 1
UPDATE 1
COMMIT

BEGIN
UPDATE 1
UPDATE 1
COMMIT

id | holder | balance 
----+--------+---------
1 | alice  |     950
2 | bob    |    1050
(2 rows)

Both committed. Session D blocked on row 1 for three seconds waiting for session C, then proceeded — an ordinary lock wait of exactly the kind Lab 7 covered, which resolves itself.

The transfers still went in opposite directions. What changed is only which row each transaction touched first.

The rule, and how to apply it in real code

If two transactions may lock the same set of rows, they must lock them in the same order. A total order on the rows — ascending primary key is the usual choice — makes a cycle impossible, because a cycle requires one transaction to be holding a higher key while waiting for a lower one.

In practice that means:

-- Lock everything the transaction will touch, in a defined order, first.
SELECT id FROM accounts WHERE id IN (:from, :to) ORDER BY id FOR UPDATE;
-- then do the work in any order you like

The ORDER BY inside FOR UPDATE is the whole fix. Without it, the order comes from the plan, which can change with the data.

Retry logic is still worth having — deadlocks can arise from foreign key checks and index maintenance you did not write — but it is a safety net, not a fix. A deadlock rate that rises with traffic is an ordering bug, and retries make it worse by adding load at exactly the wrong moment.

Task 5 — A deadlock with no UPDATE in it

The pattern people learn to look for is two UPDATE statements in opposite orders. Here is a deadlock with neither.

docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab08 -v ON_ERROR_STOP=0 <<'SQL'
BEGIN;
INSERT INTO accounts VALUES (100,'carol',10);
\\\\! sleep 3
INSERT INTO accounts VALUES (101,'dave',10);
COMMIT;
SQL\" > /tmp/l8e.out 2>&1"

sleep 1

docker exec -d rbpg-lab01 bash -c "su - postgres -c \"psql -X -d lab08 -v ON_ERROR_STOP=0 <<'SQL'
BEGIN;
INSERT INTO accounts VALUES (101,'dave',10);
\\\\! sleep 3
INSERT INTO accounts VALUES (100,'carol',10);
COMMIT;
SQL\" > /tmp/l8f.out 2>&1"

sleep 9
docker exec rbpg-lab01 cat /tmp/l8f.out
docker exec rbpg-lab01 grep -A6 "deadlock detected" \
  /var/log/postgresql/postgresql-18-main.log | tail -8

Service impact possibletwo inserts deadlocking on a unique index— Captured from the lab run of 2026-08-28. Note the CONTEXT line names the index, not the table.

$ the victim session's output, then the server log

BEGIN
INSERT 0 1
ERROR:  deadlock detected
DETAIL:  Process 10138 waits for ShareLock on transaction 815; blocked by process 10123.
Process 10123 waits for ShareLock on transaction 816; blocked by process 10138.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (0,15) in relation "accounts_pkey"
ROLLBACK

Process 10138: INSERT INTO accounts VALUES (100,'carol',10);
Process 10123: INSERT INTO accounts VALUES (101,'dave',10);

No UPDATE, no SELECT ... FOR UPDATE, no explicit LOCK. Two INSERT statements.

The CONTEXT explains it: while inserting index tuple (0,15) in relation "accounts_pkey". An insert into a table with a unique index must check whether the key already exists. When it finds an uncommitted row with that key, it cannot decide — the other transaction may commit (making this a duplicate) or roll back (making it fine) — so it waits on that transaction, exactly like a row update.

Two transactions inserting the same two keys in opposite orders therefore form the same cycle. This is the ordinary shape of an upsert deadlock in a service that processes a batch of records concurrently, and it is invisible to anyone looking for UPDATE statements.

The fix is the same, and so is the mistake

Sort the batch before inserting it. If two workers process overlapping sets of keys in ascending key order, no cycle is possible.

INSERT ... ON CONFLICT DO NOTHING does not remove this. The conflict check still has to wait on the uncommitted duplicate to find out whether there is a conflict at all, so the wait — and therefore the cycle — is unchanged. It changes what happens after the wait resolves, not whether the wait happens.

Do not take that on trust; re-run Task 5 with ON CONFLICT DO NOTHING appended to both inserts and count the deadlocks in the log before and after. On the lab run of 2026-08-28 the count went from 2 to 3, and the victim reported:

ERROR:  deadlock detected
DETAIL:  Process 10205 waits for ShareLock on transaction 818; blocked by process 10190.
Process 10190 waits for ShareLock on transaction 819; blocked by process 10205.
CONTEXT:  while inserting index tuple (0,16) in relation "accounts"

Validation

test -s "$LAB/deadlock-client.txt" && echo "OK deadlock-client"
test -s "$LAB/deadlock-server.txt" && echo "OK deadlock-server"
test -s "$LAB/aftermath.txt"       && echo "OK aftermath"
test -s "$LAB/ordered-fix.txt"     && echo "OK ordered-fix"

grep -q "deadlock detected"   "$LAB/deadlock-client.txt" && echo "OK deadlock reproduced"
grep -q "Process .*: UPDATE"  "$LAB/deadlock-server.txt" && echo "OK both statements logged"
grep -q "900"                 "$LAB/aftermath.txt"       && echo "OK survivor committed"
grep -c "COMMIT"              "$LAB/ordered-fix.txt"      # expect 2

Questions to answer without looking anything up:

Which of the two transactions is aborted, and can you influence the choice?
What does the server log contain that the client error does not, and why does that matter?
The victim’s first UPDATE succeeded before the deadlock was detected. What happened to it?
What triggers deadlock detection, and what does lowering deadlock_timeout cost?
Two INSERT statements deadlocked. What were they waiting on, and would ON CONFLICT DO NOTHING have prevented it?

Expected Outcome

You have produced two structurally identical deadlocks from quite different SQL, read the report from both ends, and eliminated one of them by changing only the order in which rows are touched.

The three things to carry away:

A deadlock is an ordinary pair of transactionid waits that forms a cycle. It needs no special lock type and no special explanation.
The victim is rolled back completely, which makes retries safe and makes long transactions expensive to lose.
The fix is a consistent lock order — usually ORDER BY on a primary key inside FOR UPDATE, or sorting a batch before writing it. Retries are a safety net, not a fix.

Troubleshooting

The deadlock does not occur. The two transactions must interleave. If the first completes both updates before the second starts, they simply queue. Run each UPDATE as a separate step with the other session’s step in between, exactly as the task sequences them.

Only one session reports an error, and you expected two. That is correct. PostgreSQL chooses one victim, rolls it back, and lets the other proceed. The survivor sees nothing unusual.

ERROR: deadlock detected takes a second to appear. It is detected, not prevented: a backend that has waited deadlock_timeout — one second by default — runs the cycle check. Lowering that value makes detection faster and the check more frequent, which is a trade rather than an improvement.

The DETAIL in the client error is shorter than the log’s. The server log carries the full report with both processes, both statements and the HINT about lock ordering. Read the log copy; it is the useful one.

The ON CONFLICT DO NOTHING case in Task 5 does not deadlock. Both sessions must insert the same two keys in opposite orders, and neither may commit before the other has inserted its first row. The waits here are on transactionid, exactly as in the UPDATE case, which is the point of the task.

The ordering fix in Task 4 does not eliminate the deadlock. Sorting must apply to the rows each transaction locks, in the same direction, in every code path. One unsorted path is enough to keep the cycle available.

Cleanup

docker exec -u postgres rbpg-lab01 psql -X -c "
  SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE datname='lab08' 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 lab08;"

Production notes

Deadlocks are an application defect with a database symptom. The database detects and resolves them correctly; nothing you configure on the server fixes the cause.
The fix is a consistent lock order. SELECT ... FROM t WHERE id = ANY($1) ORDER BY id FOR UPDATE before the writes, or sorting a batch by key before applying it, removes the cycle by construction.
Retries are a safety net, not a fix. The victim is rolled back entirely, so a retry must restart from BEGIN and must be safe to repeat — and a long transaction is expensive to lose.
Alert on the deadlocks counter in pg_stat_database as a rate, not a level. A steady trickle is a lock-ordering defect somewhere; a step change points at a deployment.
Keep the server’s deadlock report. It names both statements, and it is usually the fastest route to the two code paths involved.

What You Learned

A deadlock is an ordinary cycle of transactionid waits. It needs no special lock type and no exotic explanation.
Detection, not prevention. A backend waiting longer than deadlock_timeout runs the cycle check.
One victim, rolled back completely, and the other transaction proceeds as if nothing happened.
The server log carries the full report — both processes, both statements and the lock-ordering hint — and the client sees a shorter version.
INSERT ... ON CONFLICT DO NOTHING can deadlock, with no UPDATE anywhere, because an insert waits on the inserting transaction of a conflicting key. This was tested rather than assumed.
Consistent ordering eliminates the cycle; retries only survive it.

Objectives

Prerequisites