Deadlock Between Two Stored Procedures — Diagnosed Using the Extended Events Deadlock Graph

14:23 on a Tuesday. Application error logs started showing deadlock victim exceptions from two stored procedures that had coexisted without incident for three years. By 14:45 the deadlock rate was one per minute. Orders were being rolled back. Customer service calls started.

The Alert

Sentry caught the first deadlock at 14:23:11 — Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. By 14:30 the error had occurred 14 times. Both procedures involved: usp_PlaceOrder and usp_UpdateInventory. They had run concurrently for three years. What changed at 14:23?

First Hypothesis: New Index Changed Access Order

A new covering index had been added to the Inventory table that morning. The hypothesis: the index changed the order in which usp_UpdateInventory accessed resources, creating a lock order conflict with usp_PlaceOrder.

We checked the index creation time. 09:14. But the deadlocks only started at 14:23. A five-hour gap made the index an unlikely trigger. Ruled out.

The Discovery: Extended Events

Without a deadlock graph, diagnosis is guesswork. Extended Events captures the full graph including victim, waiters, and lock types.

CREATE EVENT SESSION [DeadlockCapture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer(SET max_memory = 4096)
WITH (STARTUP_STATE = ON);

ALTER EVENT SESSION [DeadlockCapture] ON SERVER STATE = START;

-- After next deadlock, retrieve the graph:
SELECT xdr.value('@timestamp', 'datetime2') AS deadlock_time,
       xdr.query('.') AS deadlock_graph
FROM (
  SELECT CAST(target_data AS XML) AS TargetData
  FROM   sys.dm_xe_session_targets t
  JOIN   sys.dm_xe_sessions        s ON s.address = t.event_session_address
  WHERE  s.name = 'DeadlockCapture'
) x
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') r(xdr);

The graph revealed the exact lock chain. usp_PlaceOrder held an update lock on Orders and was waiting for a shared lock on Inventory. usp_UpdateInventory held a shared lock on Inventory and was waiting for an exclusive lock on Orders. Classic circular wait.

The graph also showed the lock mode on Inventory was LCK_M_S — a shared lock from a SELECT inside usp_UpdateInventory that had been added to the procedure that morning at 11:47. Not the index. A SELECT that had been added to the procedure to log current stock levels before updating them.

Incident Timeline

Root Cause

The SELECT added to usp_UpdateInventory at 11:47 read from the Orders table to log context. This created a lock order conflict: usp_PlaceOrder acquired locks in the order Orders → Inventory. usp_UpdateInventory now acquired locks in the order Inventory → Orders. Under concurrent execution this produces a circular wait. SQL Server detects the cycle and kills one transaction as the deadlock victim.

The Fix

-- The offending SELECT inside usp_UpdateInventory was removed.
-- If the context log is needed, pass the values as parameters
-- rather than reading them inside the transaction.

ALTER PROCEDURE dbo.usp_UpdateInventory
  @ProductId   INT,
  @Quantity    INT,
  @OrderId     INT   -- passed in, not read from Orders
AS
  UPDATE dbo.Inventory
  SET    StockLevel = StockLevel - @Quantity
  WHERE  ProductId = @ProductId;
  
  -- Log with the passed OrderId — no cross-table lock
  INSERT dbo.InventoryLog (ProductId, Quantity, OrderId, LoggedAt)
  VALUES (@ProductId, @Quantity, @OrderId, GETUTCDATE());

Prevention

Extended Events deadlock capture is now always running in production. Any new stored procedure that touches multiple tables in a transaction goes through a review step that maps the lock acquisition order against existing procedures. Lock order consistency — always acquiring locks in the same sequence across all procedures — prevents circular waits.