Lab 03 · Deadlock autopsy with AI

Kavitha's payments team gets deadlock detected twice a week from a settlement job, and the incident channel's standing answer is "we retried, it cleared." Deadlock log output is dense, rarely read carefully, and an LLM will explain the cycle in thirty seconds — which is exactly why this lab exists: the AI's explanation will be fluent, one of its three standard fix proposals is a correctness trap, and only a repro-then-verify loop separates the fix that works from the fix that trades blocking for wrong data.

Time & tier

~75 minutes hands-on · Core tier (maps to module M1 · Verification-First AI Tuning & Diagnostics).

Setup

Reuse the Lab 01 container, fresh database:

docker exec -it tuning-lab psql -U postgres -c "create database autopsy"
docker exec -it tuning-lab psql -U postgres -d autopsy

create table accounts (
  account_id bigint primary key,
  holder     text not null,
  balance    numeric(14,2) not null
);
insert into accounts
select g, 'Holder ' || g, 100000.00 from generate_series(1, 100) g;

create table transfer_log (
  log_id      bigint generated always as identity primary key,
  from_acct   bigint not null references accounts(account_id),
  to_acct     bigint not null references accounts(account_id),
  amount      numeric(14,2) not null,
  logged_at   timestamptz not null default now()
);
-- deliberate omission: no index on transfer_log(from_acct) or (to_acct)
alter system set log_lock_waits = on;
alter system set deadlock_timeout = '500ms';
select pg_reload_conf();

The settlement job's two directions, saved as session_a.sql and session_b.sql — each transfers between the same pair of accounts, locking in opposite order:

-- session_a.sql : settle 7 -> 42
begin;
update accounts set balance = balance - 500 where account_id = 7;
select pg_sleep(1.5);
update accounts set balance = balance + 500 where account_id = 42;
insert into transfer_log (from_acct, to_acct, amount) values (7, 42, 500);
commit;

-- session_b.sql : settle 42 -> 7  (opposite lock order)
begin;
update accounts set balance = balance - 200 where account_id = 42;
select pg_sleep(1.5);
update accounts set balance = balance + 200 where account_id = 7;
insert into transfer_log (from_acct, to_acct, amount) values (42, 7, 200);
commit;

And the loop harness, loop_test.sh:

#!/usr/bin/env bash
PSQL="docker exec -i tuning-lab psql -U postgres -d autopsy -q"
FAILS=0
for i in $(seq 1 20); do
  $PSQL -f /dev/stdin < session_a.sql > /dev/null 2>&1 &
  $PSQL -f /dev/stdin < session_b.sql > /dev/null 2>&1 || FAILS=$((FAILS+1))
  wait
done
echo "deadlock-aborted runs: $FAILS / 20"

Guided steps

1. 1.

   Trigger the deadlock once, by hand. Open two psql sessions. In session 1 run session_a.sql's statements up to the sleep; in session 2 run session_b.sql's first update; then let both proceed. Within ~500ms one session dies with ERROR: deadlock detected. Note which session survived.

2. 2.

   Capture the evidence from the log. Pull the full deadlock detail:

   Copy

   docker logs tuning-lab 2>&1 | grep -A 12 "deadlock detected" | tail -20
   

   Expected: two Process ... waits for ShareLock on transaction ...; blocked by process ... lines forming the cycle, the two victim/survivor statements, and the DETAIL naming the tuples. Save verbatim as deadlock-log.txt — this is the artifact the AI works from, not your memory of it.

3. 3.

   Run the AI explanation pass. Feed the assistant the log detail, both session scripts, and the DDL. Ask for: (a) the lock cycle in plain language — who held what, who waited for what; (b) why Postgres chose that victim; (c) three fixes ranked by preference, each with what it could make worse. Save as ai-explanation.md. Expected: it correctly narrates the ABBA cycle on accounts 7 and 42. Its fix list will typically include lock ordering, retry logic, and something in the family of "reduce isolation level" or "use select ... for update skip locked" — keep all three verbatim.

4. 4.

   Adjudicate the fix list before implementing anything. Write one verdict line per proposed fix. The trap to catch: any suggestion that lowering the isolation level fixes deadlocks. This deadlock happens at plain READ COMMITTED — it is a lock ordering problem, not an isolation problem, and isolation changes alter application semantics without breaking the cycle. Likewise skip locked silently skips settling a row — a correctness change dressed as a concurrency fix. Retry logic is legitimate but treats the symptom. The ordering fix is the cure.

5. 5.

   Implement the ordering fix. Rewrite both scripts to lock accounts in a canonical order (lowest account_id first) regardless of transfer direction — the classic pattern:

   Copy

   -- session_b_fixed.sql : settle 42 -> 7, but LOCK in id order
   begin;
   select account_id from accounts
    where account_id in (7, 42) order by account_id for update;
   update accounts set balance = balance - 200 where account_id = 42;
   select pg_sleep(1.5);
   update accounts set balance = balance + 200 where account_id = 7;
   insert into transfer_log (from_acct, to_acct, amount) values (42, 7, 200);
   commit;
   

   Apply the same select ... order by account_id for update preamble to session_a.sql. The sleep stays — the fix must survive the same interleaving that caused the crash, not avoid it.

6. 6.

   Prove it under looped load. Run bash loop_test.sh against the original scripts first — expected: several aborted runs out of 20 (the interleaving is timing-dependent; any non-zero count confirms the repro). Then run it against the fixed scripts — expected: deadlock-aborted runs: 0 / 20, every time. Sessions now block briefly and proceed serially on contended pairs; blocking is the correct behavior, deadlock was the bug.

7. 7.

   Check the second-order finding. The DDL deliberately omits indexes on transfer_log.from_acct / to_acct — both are foreign keys. Ask the AI what risk unindexed FK columns add in high-concurrency delete/update scenarios on accounts, then verify its claim against the docs before writing it down. It is a real sharp edge (FK checks and cascades take locks and seq-scan the child table) but it is not what caused this deadlock — the RCA must keep the two findings separate.

8. 8.

   Write the RCA. deadlock-rca.md, four sections: Cycle (the log excerpt annotated line-by-line in your own words); Root cause (opposite lock acquisition order, with the two scripts cited); Fix and proof (the ordering rewrite, plus both loop-test outputs — before with failures, after with 0/20); Rejected fixes (isolation change and skip-locked, each with the one-line reason). Append the FK-index note as a separate follow-up item.

Verify

• •
  deadlock-log.txt contains the genuine two-process cycle from your own container, not a paraphrase.
• •
  The adjudication kills the isolation-level fix and the skip-locked fix with correctness-based reasons, and classifies retry as symptom relief.
• •
  Loop test output appears twice in the RCA: non-zero failures on the original scripts, 0 / 20 on the fixed ones.
• •
  The FK-index observation is present but explicitly marked as unrelated to this deadlock.

Self-grading rubric (pass = 7/10): manual repro and log capture (0–2); AI explanation obtained and checked against the log (0–1); fix adjudication catches the isolation trap with the right reason (0–3); ordering fix implemented and proven 0/20 under loop (0–3); RCA separates the FK finding from the root cause (0–1).

Stuck?

• No deadlock fires in step 1the interleaving needs both first updates to land before either second update; run the statements manually with the sleeps, or lengthen pg_sleep to 3 seconds.
• docker logs shows nothingdeadlock detail goes to the server log at default settings, but confirm log_lock_waits = on took effect with show log_lock_waits; and that you reloaded the config.
• The loop test shows 0 failures on the original scriptstiming luck; raise the iteration count to 50 or add a 100ms stagger before launching session B so the windows overlap reliably.