Autopsy Deadlocks and Blocking Chains from the Graph

Tier: Core · Workflow area: SQL Authoring & Query Tuning · ~35 minutes

Deadlocks intimidate because the evidence is dense — lock modes, resource lists, victim selection — not because the logic is deep. An LLM decodes the notation instantly and proposes the standard fixes (consistent ordering, index-shortened lock windows, isolation changes). Your job is the two things it cannot do: confirm the diagnosis against the real code paths, and prove the fix under concurrent load.

When to use

• Recurring deadlocks on the same pair of tablesa pattern worth an hour of root-cause work, and the graph plus app code tells the whole story.
• A blocking storm you need explained after the factthe lock-wait chain from the incident, decoded into who held what and why the line formed.
• Post-deploy deadlock noveltynew deadlocks after a release almost always trace to a new code path acquiring locks in a different order; the autopsy pinpoints which.

When NOT to use

• A single one-off deadlockdeadlocks are a normal concurrency outcome; the app should retry and move on. Autopsy patterns, not incidents.
• During the storm itselfmid-incident you kill the head blocker and restore service; autopsy comes after, from captured evidence.
• When retry logic is missing entirelyif the app crashes on deadlock instead of retrying, fix that first. No amount of lock-order hygiene reduces deadlocks to zero.

The recipe

1. 1.

   Capture the evidence — turn detailed logging on before you need it.

   Copy

   -- PostgreSQL: log every deadlock with full detail
   ALTER SYSTEM SET log_lock_waits = on;
   ALTER SYSTEM SET deadlock_timeout = '1s';
   SELECT pg_reload_conf();
   

   The Postgres log then yields the core artifact:

   Copy

   ERROR:  deadlock detected
   DETAIL:  Process 4211 waits for ShareLock on transaction 90412;
            blocked by process 4207.
            Process 4207 waits for ShareLock on transaction 90415;
            blocked by process 4211.
   Process 4211: UPDATE accounts SET balance = balance - 100 WHERE id = 2
   Process 4207: UPDATE accounts SET balance = balance + 100 WHERE id = 1
   

   On SQL Server, pull the deadlock graph from the always-on system_health Extended Events session (search for xml_deadlock_report). For live blocking chains on Postgres:

   Copy

   SELECT blocked.pid  AS blocked_pid,
          blocked.query AS blocked_query,
          blocking.pid AS blocking_pid,
          blocking.query AS blocking_query,
          blocking.state AS blocking_state
   FROM pg_stat_activity blocked
   JOIN pg_stat_activity blocking
     ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));
   

2. 2.

   Ask for the cycle, the cause, and ranked fixes — separately.

   Copy

   You are a database concurrency engineer. Below: a PostgreSQL deadlock
   log detail (or SQL Server xml_deadlock_report) plus the application
   code for both transactions involved.
   
   Answer in four ordered parts:
   1. THE CYCLE — who held what, who wanted what, in plain language,
      as a two-column timeline of the interleaving that deadlocks.
   2. VICTIM — why the engine chose this victim.
   3. ROOT CAUSE — the design property that makes the cycle possible
      (inconsistent acquisition order, lock escalation from a missing
      index, long transaction spans, SELECT ... FOR UPDATE gaps).
   4. FIXES, ranked safest-first, each with: the code/SQL change,
      what it trades away, and how to prove it worked.
   If the code provided cannot produce this graph, say so — do not
   invent an interleaving.
   

   The last line matters: models will happily narrate a plausible interleaving that your code cannot actually produce. If the explanation references a statement that is not in the code you pasted, reject and re-ask.

3. 3.

   Verify the diagnosis against the code paths. Trace both transactions in the app repo and confirm the acquisition order the AI described is real. The classic confirmed finding: transfer() updates debtor then creditor; reconcile() walks accounts in id order — opposite orders, guaranteed cycle under load.

4. 4.

   Build the repro before the fix. A deadlock you can produce on demand is one you can prove fixed:

   Copy

   # Terminal A                          # Terminal B
   psql -d labdb <<'EOF' &
   BEGIN;
   UPDATE accounts SET balance = balance - 100 WHERE id = 1;
   SELECT pg_sleep(3);
   UPDATE accounts SET balance = balance + 100 WHERE id = 2;
   COMMIT;
   EOF
   psql -d labdb <<'EOF'
   BEGIN;
   UPDATE accounts SET balance = balance - 50 WHERE id = 2;
   SELECT pg_sleep(1);
   UPDATE accounts SET balance = balance + 50 WHERE id = 1;
   COMMIT;
   EOF
   # Expect: "ERROR: deadlock detected" in one terminal
   

5. 5.

   Apply the ordering fix — the usual winner. Consistent acquisition order dissolves cycles without new locking machinery:

   Copy

   -- Both code paths now lock accounts in ascending id order first:
   BEGIN;
   SELECT id FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
   UPDATE accounts SET balance = balance - 100 WHERE id = 1;
   UPDATE accounts SET balance = balance + 100 WHERE id = 2;
   COMMIT;
   

   Where the AI proposed an index fix instead (blocking caused by lock waits on seq-scanned rows, or on SQL Server lock escalation from a scan), that flows through pb-03's verification loop.

6. 6.

   Prove it under looped concurrency. Run the repro pair in a loop — 200 iterations, both directions — and require zero deadlocks where the unfixed version produced them reliably. File the RCA with the graph, the confirmed cycle, the fix diff and the loop result: that is a complete deadlock autopsy.

Make it yours

• SQL Serverthe xml_deadlock_report from system_health is richer than Postgres logs (full resource list, isolation levels, victim reasoning); paste the XML whole, and add sys.dm_tran_locks snapshots for live chains. Watch for lock-escalation root causes — the fix is often an index or batching, not ordering.
• MySQL/InnoDBSHOW ENGINE INNODB STATUS prints only the latest deadlock; enable innodb_print_all_deadlocks = ON to get them all into the error log. Gap-lock deadlocks under REPEATABLE READ are a frequent species — the model should mention them, and the fix may be READ COMMITTED plus verified app semantics.
• Oracledeadlocks dump a trace file (ORA-00060) with the cycle graph; feed the trace section to the same prompt. ITL-slot and unindexed-foreign-key deadlocks are Oracle-specific species worth naming explicitly in the prompt so the model checks for them.

Quality check

• The explained cycle matches the captured graphevery lock and session in the narrative exists in the evidence. No invented interleavings.
• The fix was proven by repro, not by rereadingzero deadlocks across a looped concurrent run is the standard. "The code looks right now" is not.
• Retry logic still exists after the fixordering discipline reduces deadlocks; it cannot eliminate them. The app-level retry stays.
• Isolation-level changes got a semantics reviewdropping to READ COMMITTED to kill gap-lock deadlocks changes what concurrent transactions can observe. That is an application correctness decision, made with the app team, not a DBA tuning knob.