/sqldeadlock-review

name: sqldeadlock-review
description: Analyze SQL Server deadlock XML (from system_health XE session, SSMS deadlock graph, or trace) to identify root cause and produce a prioritized remediation plan. Applies 17 known deadlock patterns (P1–P17). Use when a deadlock monitor captures a graph or users report intermittent deadlock errors (error 1205).
triggers:
  - /sqldeadlock-review
  - /deadlock
  - /deadlock-analyze

SQL Server Deadlock Analysis Skill

Purpose

Parse a SQL Server deadlock XML graph, identify the victim and winner processes, extract the queries and lock acquisition patterns involved, match against 17 known deadlock patterns (P1–P17), and produce a prioritized remediation plan.

Input

Accept any of:

• Raw `<deadlock>` XML (from system_health XE session or SSMS deadlock graph Save As XML)
• A file path to a `.xdl` or `.xml` deadlock graph file
• A description of the deadlock if XML is not available

How to Run

1. Parse the XML structure 2. Extract process list (victim, winner, their queries, lock waits) 3. Extract resource list (what locks are held and requested) 4. Match against pattern library 5. Generate remediation recommendations

---

XML Structure Reference

<deadlock>
  <victim-list>
    <victimProcess id="process1a2b" />
  </victim-list>
  <process-list>
    <process id="process1a2b" taskpriority="0" logused="0"
             waitresource="KEY: 5:72057594038910976 (abc123)"
             waittime="4023" ownerId="123456"
             transactionname="user_transaction"
             currentdb="5" spid="52" kpid="1234"
             status="suspended" isolationlevel="read committed">
      <executionStack>
        <frame procname="adhoc" line="3" stmtstart="100" stmtend="200"
               sqlhandle="0x...">
          UPDATE Orders SET Status = 1 WHERE Id = @id
        </frame>
      </executionStack>
      <inputbuf>UPDATE Orders SET Status = 1 WHERE Id = @id</inputbuf>
    </process>
    ...
  </process-list>
  <resource-list>
    <keylock hobtid="72057594038910976" dbid="5" objectname="dbo.Orders"
             indexname="PK_Orders" id="lock1" mode="X" associatedObjectId="...">
      <owner-list>
        <owner id="process2c3d" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process1a2b" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    ...
  </resource-list>
</deadlock>

---

Extraction Checklist

For each process:

• Process ID, SPID, victim status (yes/no)
• Query text (from `<inputbuf>` and `<executionStack>`)
• `waitresource` — what lock it is waiting for
• `transactionname` — the transaction context
• `isolationlevel` — READ COMMITTED, SNAPSHOT, SERIALIZABLE, etc.
• `logused` — how much log has been written (indicator of transaction size)

For each resource:

• Resource type: `keylock`, `pagelock`, `objectlock`, `ridlock`, `metadatalock`
• Object and index name
• Mode held by each owner (S, U, X, IS, IX, SIX)
• Mode requested by each waiter

---

Pattern Library (P1–P17)

P1 — Classic Forward/Reverse Access Order

• **Signature:** Process A holds X on resource R1, waits for resource R2. Process B holds X on R2, waits for R1.
• **Severity:** High
• **Cause:** Two transactions update the same pair of rows in opposite order.
• **Fix:** Enforce a consistent access order in application code (always update table A before table B, always process rows in ascending PK order).

P2 — Reader/Writer Deadlock (Shared vs Exclusive)

• **Signature:** Process A holds S lock (SELECT), waits for X. Process B holds X (UPDATE), waits for S to be released.
• **Severity:** High
• **Cause:** A long-running read transaction blocks a writer; another reader prevents the writer from completing, causing a cycle.
• **Fix:** Enable READ_COMMITTED_SNAPSHOT isolation (`ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON`). Readers take no shared locks under RCSI — the most common fix for reader/writer deadlocks without changing application code.

P3 — Update Lock Escalation Deadlock

• **Signature:** Multiple processes hold U locks on different rows, each waiting for U on the other's row.
• **Severity:** High
• **Cause:** `UPDATE` statements taking U locks in different orders on the same table.
• **Fix:** Add an index on the `WHERE` clause columns so each update targets exactly one row (reduces lock scope). Consider using `WITH (ROWLOCK)` hint. Consistent access order also applies.

P4 — Missing Index Causing Scan-Level Page or Table Lock Deadlock

• **Signature:** `objectlock` or `pagelock` resource type (not `keylock`) in the resource list.
• **Severity:** High
• **Cause:** Without a suitable index, SQL Server may scan pages and acquire page or table locks directly. Multiple transactions competing for the same page or table deadlock each other.
• **Fix:** Add a nonclustered index on the filter column so SQL Server can seek to specific rows and take row-level (`keylock`) locks instead of page or table locks. Use the `sqlindex-advisor` skill if an execution plan is available.

P5 — Bookmark Lookup Deadlock (Key Lookup)

• **Signature:** Two `keylock` resources: one on a nonclustered index, one on the clustered index (PK). Process A holds lock on NC index, waits for PK. Process B holds lock on PK, waits for NC index.
• **Severity:** Medium
• **Cause:** A query does a Key Lookup (NC index → PK), taking locks on both. Another query updates via the PK, taking locks in reverse order.
• **Fix:** Eliminate the Key Lookup by adding INCLUDE columns to the NC index so no bookmark lookup is needed. This removes the two-resource lock acquisition.

P6 — SERIALIZABLE Phantom Deadlock

• **Signature:** `isolationlevel = serializable` on one or more processes AND range locks (RangeX-X, RangeS-U) visible in the resource type.
• **Severity:** Medium
• **Cause:** SERIALIZABLE isolation holds range locks to prevent phantoms. Two transactions holding range locks on adjacent ranges block each other's inserts.
• **Fix:** Downg