Deadlocks: Questions & Answers

What Is a Deadlock?

Visual Example

Transaction A:              Transaction B:
1. LOCK Table1             1. LOCK Table2
2. Try to LOCK Table2 ←→ 2. Try to LOCK Table1
   (WAITS)                    (WAITS)

Result: CIRCULAR DEPENDENCY
SQL Server detects → Kills one (victim)
Victim rolls back → Other continues

Deadlock Myths & Facts

Root Causes

Diagnosing Deadlocks

Method 1: SQL Server Error Log

-- Look for deadlock entries in error log
-- But this shows minimal info (only victim SPID)

-- Enable trace flag 1222 (detailed deadlock graph)
DBCC TRACEON (1222, -1);  -- -1 = global

-- Now deadlocks are logged with full graph showing:
-- - Which transactions blocked which
-- - What statements they were running
-- - Lock modes held and waited-for

Method 2: Extended Events (SQL 2012+, Recommended)

-- Create session to capture deadlock graphs
CREATE EVENT SESSION deadlock_tracking ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(
  SET filename = 'C:\SQLLogs\deadlocks.xel'
)
WITH (STARTUP_STATE = ON);

-- Parse the deadlock graph (shows victim, blocking chain, SQL statements)
-- Use Extended Events viewer in SSMS: Management → Extended Events → Sessions

Method 3: Deadlock Graph Interpretation

-- A typical deadlock graph shows:
--
-- Process A (SPID 52)
--   Holding: Exclusive lock on Table1:Row 123
--   Waiting: Exclusive lock on Table2:Row 456
--   Running: UPDATE Table1 SET ... ; UPDATE Table2 SET ...
--
-- Process B (SPID 48)
--   Holding: Exclusive lock on Table2:Row 456
--   Waiting: Exclusive lock on Table1:Row 123
--   Running: UPDATE Table2 SET ... ; UPDATE Table1 SET ...
--
-- → Circular wait detected → SPID 52 killed as victim

-- Key insight: Both queries touch the same tables in different order!

Common Patterns & Solutions

Pattern 1: Order Mismatch Between Procedures

-- PROCEDURE A
CREATE PROCEDURE usp_UpdateCustomerAndOrder
  @CustomerID INT,
  @OrderID INT
AS
BEGIN
  UPDATE dbo.Customers SET LastModified = GETDATE() WHERE CustomerID = @CustomerID;
  UPDATE dbo.Orders SET LastModified = GETDATE() WHERE OrderID = @OrderID;
END;

-- PROCEDURE B
CREATE PROCEDURE usp_UpdateOrderAndCustomer
  @OrderID INT,
  @CustomerID INT
AS
BEGIN
  UPDATE dbo.Orders SET LastModified = GETDATE() WHERE OrderID = @OrderID;
  UPDATE dbo.Customers SET LastModified = GETDATE() WHERE CustomerID = @CustomerID;
END;

-- If both run concurrently → DEADLOCK
-- A locks Customers, waits for Orders
-- B locks Orders, waits for Customers

-- FIX: Standardize access order (always Customers first, then Orders)
ALTER PROCEDURE usp_UpdateOrderAndCustomer
  @OrderID INT,
  @CustomerID INT
AS
BEGIN
  UPDATE dbo.Customers SET LastModified = GETDATE() WHERE CustomerID = @CustomerID;
  UPDATE dbo.Orders SET LastModified = GETDATE() WHERE OrderID = @OrderID;
END;

Pattern 2: Foreign Key Cascade Locks

-- Schema:
-- Customers (PK: CustomerID) ← Orders.CustomerID (FK)

-- Insert order matters
-- Process A:
INSERT INTO Orders (OrderID, CustomerID) VALUES (1, 100);  -- Locks Customers:100

-- Process B (concurrent):
INSERT INTO Orders (OrderID, CustomerID) VALUES (2, 100);  -- Waits for Customers:100

-- Then both try to update Customers
-- → Potential deadlock with other processes

-- FIX: Use SERIALIZABLE or indexed views for FK integrity
-- OR: Accept deadlock as normal and retry in application layer

Pattern 3: Implicit Conversions in WHERE Clauses

-- Table definition:
CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  OrderDate DATETIME,
  Amount DECIMAL(10,2)
);

-- Query with implicit conversion
SELECT * FROM Orders WHERE OrderID = '123';  -- String '123' implicitly converts to INT

-- This causes SQL Server to:
-- 1. Lock more rows than expected (type conversion might affect index scans)
-- 2. Range locks instead of single row
-- 3. Higher deadlock probability

-- FIX: Use correct data types
SELECT * FROM Orders WHERE OrderID = 123;  -- INT literal, not string

Prevention Strategies

Retry Logic Example (C#)

int maxRetries = 3;
int retryCount = 0;
int backoffMs = 100;

while (retryCount < maxRetries)
{
  try
  {
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
      conn.Open();
      using (SqlCommand cmd = new SqlCommand("usp_UpdateCustomer", conn))
      {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();
      }
    }
    break;  // Success
  }
  catch (SqlException ex) when (ex.Number == 1205)  // Deadlock
  {
    retryCount++;
    if (retryCount >= maxRetries) throw;
    System.Threading.Thread.Sleep(backoffMs);
    backoffMs *= 2;  // Exponential backoff
  }
}

Monitoring & Tools

Manual Deadlock Monitoring Query

-- Count deadlocks by executing:
SELECT
  OBJECT_NAME(i.object_id) AS table_name,
  i.name AS index_name,
  s.user_updates,
  s.user_seeks,
  s.user_scans
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
  ON s.object_id = i.object_id
  AND s.index_id = i.index_id
WHERE database_id = DB_ID()
ORDER BY s.user_updates DESC;

-- This doesn't show deadlocks directly, but high update counts
-- on specific tables indicate deadlock hotspots

When to Accept Deadlocks

Common Mistakes

Deadlock Checklist

• ☐ Enable deadlock monitoring (Extended Events or trace flag 1222)
• ☐ Collect baseline deadlock frequency (deadlocks per hour/day)
• ☐ Analyze recent deadlock graphs to identify patterns
• ☐ Standardize access order in procedures touching multiple tables
• ☐ Add indexes to support WHERE clauses (reduce range locks)
• ☐ Implement retry logic in application code (with exponential backoff)
• ☐ Monitor deadlock trends using DeadlockCollector or similar tool
• ☐ Test failover behavior (deadlocks may manifest under load)
• ☐ Document known deadlock patterns and mitigation steps

The Bottom Line

Deadlocks are not bugs—they're a feature of multi-user database systems. The question isn't "how do I eliminate them," but "how do I minimize their impact?"

Consistent access order, minimal transaction duration, good indexing, and application-level retry logic are your arsenal. Pair these with tools like DeadlockCollector to identify systemic issues, and you'll have a robust, resilient database layer.

And remember: occasional deadlocks are normal. Constant deadlocks are a symptom. Find the root cause, not just the error.