SQLOPTIMISE Logo
Troubleshooting13 min read

SQL Server Deadlocks: Complete Troubleshooting Guide with Real-World Solutions

Master deadlock detection, analysis, and prevention in SQL Server. Learn from expert insights and discover automated solutions that catch deadlocks before they impact users.

By SQLOPTIMISE Team•
SQL ServerDeadlocksTroubleshooting

SQL Server Deadlocks: Complete Troubleshooting Guide

Deadlocks are one of the most frustrating SQL Server issues for developers and DBAs alike. As Brent Ozar puts it, "Deadlocks are like traffic accidents - they're going to happen, but you can reduce them dramatically with the right approach." This guide covers everything you need to know about detecting, analyzing, and preventing deadlocks.

Understanding Deadlocks

What is a Deadlock?

A deadlock occurs when two or more processes permanently block each other by holding locks that the other process needs. As Paul Randal explains, "It's a circular chain of blocking where nobody can proceed."

Classic Example:

-- Session 1
BEGIN TRANSACTION
UPDATE Orders SET Status = 'Processing' WHERE OrderID = 1001
-- Holds lock on Orders row 1001
WAITFOR DELAY '00:00:05'
UPDATE Customers SET LastOrder = GETDATE() WHERE CustomerID = 500
-- Needs lock on Customers row 500
COMMIT

-- Session 2 (running simultaneously)
BEGIN TRANSACTION
UPDATE Customers SET Balance = Balance - 100 WHERE CustomerID = 500
-- Holds lock on Customers row 500
WAITFOR DELAY '00:00:05'
UPDATE Orders SET CustomerNote = 'Processed' WHERE OrderID = 1001
-- Needs lock on Orders row 1001
COMMIT

Result: Deadlock! Neither session can proceed. SQL Server's deadlock monitor detects this and kills one transaction as the "deadlock victim."

Deadlock Victim Selection

Jonathan Kehayias explains the selection criteria:

  1. DEADLOCK_PRIORITY (SET explicitly or default NORMAL)
  2. Transaction log usage (cheaper transaction to rollback)
  3. Random selection if priorities are equal
-- Make this session less likely to be chosen as victim
SET DEADLOCK_PRIORITY HIGH

-- Make this session more likely to be chosen (useful for background jobs)
SET DEADLOCK_PRIORITY LOW

Detecting Deadlocks

1. Error Messages in Application

Applications receive error 1205:

Transaction (Process ID XX) was deadlocked on {lock resources} with another process and has been chosen as the deadlock victim. Rerun the transaction.

2. System Health Extended Events Session

SQL Server 2008+ automatically captures deadlocks in the system_health session. Glenn Berry's query to retrieve them:

-- Get deadlock graphs from system_health
WITH DeadlockData AS (
    SELECT 
        CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    INNER JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health'
        AND st.target_name = 'ring_buffer'
)
SELECT 
    DeadlockGraph.query('.') AS DeadlockGraph,
    DeadlockGraph.value('(event/@timestamp)[1]', 'datetime2') AS DeadlockTime
FROM DeadlockData
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS Deadlock(DeadlockGraph)
ORDER BY DeadlockTime DESC

3. Custom Extended Events Session

For detailed deadlock monitoring, create a dedicated session as Erik Darling recommends:

CREATE EVENT SESSION [DeadlockCapture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.sql_text,
        sqlserver.username
    )
)
ADD TARGET package0.event_file(
    SET filename = N'C:\DeadlockTrace\DeadlockCapture.xel',
        max_file_size = 50,
        max_rollover_files = 10
)
WITH (
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = ON
)
GO

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

4. Trace Flag 1222

Paul Randal's approach for detailed deadlock information in the error log:

-- Enable deadlock trace flag
DBCC TRACEON(1222, -1)

-- View error log
EXEC xp_readerrorlog

Analyzing Deadlock Graphs

Reading the Deadlock Graph

Kendra Little breaks down the key components:

  1. Victim (marked with an X)
  2. Processes (each transaction involved)
  3. Resources (what they're fighting over)
  4. Execution Stack (what queries were running)

Example Analysis:

<deadlock>
  <victim-list>
    <victimProcess id="process123" />
  </victim-list>
  <process-list>
    <process id="process123" taskpriority="0" logused="832" 
             waitresource="KEY: 6:72057594038779904 (1001)" 
             transactionname="UPDATE">
      <executionStack>
        <frame procname="dbo.ProcessOrder" line="15" 
               stmttext="UPDATE Orders SET..." />
      </executionStack>
    </process>
    <process id="process456" taskpriority="0" logused="416"
             waitresource="KEY: 6:72057594038845440 (500)"
             transactionname="UPDATE">
      <executionStack>
        <frame procname="dbo.UpdateCustomer" line="8"
               stmttext="UPDATE Customers SET..." />
      </executionStack>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594038779904" dbid="6" 
             objectname="dbo.Orders" indexname="PK_Orders" 
             mode="X" associatedObjectId="72057594038779904">
      <owner-list>
        <owner id="process456" mode="X"/>
      </owner-list>
      <waiter-list>
        <waiter id="process123" mode="X"/>
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Key Insights:

  • Process123 (victim) was updating Orders
  • Process456 was updating Customers
  • Both needed locks the other held
  • The deadlock occurred on primary key locks

Common Deadlock Patterns

1. Missing Indexes Causing Lock Escalation

Grant Fritchey identifies this as a top cause:

-- Query without index does table scan, holding many locks
SELECT CustomerID, OrderDate 
FROM Orders 
WHERE Status = 'Pending'  -- No index on Status column

-- Create covering index to reduce lock footprint
CREATE INDEX IX_Orders_Status_Covering 
ON Orders(Status) 
INCLUDE (CustomerID, OrderDate)

2. Different Access Order

Aaron Bertrand's classic deadlock scenario:

-- Proc A
BEGIN TRANSACTION
UPDATE TableA WHERE ID = @ID
UPDATE TableB WHERE ID = @ID
COMMIT

-- Proc B (accessing same tables in different order)
BEGIN TRANSACTION
UPDATE TableB WHERE ID = @ID
UPDATE TableA WHERE ID = @ID
COMMIT

Solution: Access tables in consistent order across all procedures.

3. Long-Running Transactions

Brent Ozar emphasizes: "The longer a transaction runs, the more likely it is to deadlock."

-- Bad: Long transaction holding locks
BEGIN TRANSACTION
    SELECT * FROM LargeTable  -- Expensive query
    -- User interaction time...
    UPDATE SmallTable SET ...
COMMIT

-- Good: Minimize transaction scope
SELECT * FROM LargeTable  -- Outside transaction
-- Process results...
BEGIN TRANSACTION
    UPDATE SmallTable SET ...
COMMIT

4. Bookmark Lookups (Key Lookups)

Kendra Little explains: "Lookups require additional locks, increasing deadlock potential."

-- Query causing lookups
SELECT OrderID, CustomerID, OrderDate, TotalAmount, ShippingAddress
FROM Orders
WHERE CustomerID = @CustomerID

-- Index only covers CustomerID
CREATE INDEX IX_Orders_CustomerID ON Orders(CustomerID)

-- Convert to covering index to eliminate lookups
CREATE INDEX IX_Orders_CustomerID_Covering 
ON Orders(CustomerID) 
INCLUDE (OrderDate, TotalAmount, ShippingAddress)

Prevention Strategies

1. Keep Transactions Short

Paul Randal's golden rule: "Get in, do your work, get out."

-- Separate long reads from modifications
DECLARE @ProcessData TABLE (OrderID INT, Amount DECIMAL(18,2))

-- Read outside transaction
INSERT INTO @ProcessData
SELECT OrderID, TotalAmount 
FROM Orders 
WHERE Status = 'Pending'

-- Short transaction for updates only
BEGIN TRANSACTION
    UPDATE Orders 
    SET Status = 'Processed', ProcessedDate = GETDATE()
    WHERE OrderID IN (SELECT OrderID FROM @ProcessData)
COMMIT

2. Use Appropriate Isolation Levels

Jonathan Kehayias recommends READ COMMITTED SNAPSHOT for many workloads:

-- Enable at database level
ALTER DATABASE YourDB 
SET READ_COMMITTED_SNAPSHOT ON

Benefits:

  • Readers don't block writers
  • Writers don't block readers
  • Reduces deadlock potential significantly

Trade-off: Increased TempDB usage for row versioning.

3. Proper Indexing Strategy

Glenn Berry's indexing checklist to reduce deadlocks:

  • Cover frequent queries to reduce lock duration
  • Include covering columns to eliminate lookups
  • Avoid redundant indexes that increase lock complexity
  • Monitor index usage with DMVs
-- Find missing indexes that might reduce deadlocks
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * 
    (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' + 
    REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') + 
    CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END +
    REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns,''),', ','_'),'[',''),']','') +
    '] ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns,'') +
    CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END +
    ISNULL(mid.inequality_columns, '') + ')' +
    ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * 
      (migs.user_seeks + migs.user_scans) > 100000
ORDER BY improvement_measure DESC

4. Retry Logic in Application

Erik Darling's approach: "Some deadlocks are unavoidable - handle them gracefully."

// C# example with exponential backoff
int maxRetries = 3;
int retryCount = 0;
int delayMs = 100;

while (retryCount < maxRetries)
{
    try
    {
        // Execute query
        ExecuteQuery();
        break;  // Success
    }
    catch (SqlException ex) when (ex.Number == 1205)  // Deadlock
    {
        retryCount++;
        if (retryCount >= maxRetries) throw;
        
        Thread.Sleep(delayMs);
        delayMs *= 2;  // Exponential backoff
    }
}

5. Use NOLOCK Carefully

Warning from Kendra Little: "NOLOCK can cause incorrect results. Know the trade-offs!"

-- Use only for reports where dirty reads are acceptable
SELECT CustomerName, OrderCount
FROM Customers WITH (NOLOCK)
LEFT JOIN Orders WITH (NOLOCK) ON Customers.CustomerID = Orders.CustomerID

Better alternative: READ COMMITTED SNAPSHOT (no dirty reads, no blocking).

Automated Deadlock Monitoring

Manual deadlock analysis doesn't scale for busy production environments. Automated monitoring is essential for quick detection and resolution.

aDBA's Deadlock Detection and Analysis

aDBA provides comprehensive deadlock monitoring:

Automated Capture:

  • Continuous monitoring via Extended Events
  • Automatic deadlock graph collection
  • Historical deadlock database

Intelligent Analysis:

  • Identifies deadlock patterns over time
  • Correlates deadlocks with code deployments
  • Suggests specific index improvements
  • Highlights affected procedures/queries

Proactive Alerts:

  • Immediate notification of deadlocks
  • Threshold-based alerting (e.g., >5 deadlocks/hour)
  • Escalation for repeated patterns
  • Integration with incident management systems

Trend Reporting:

-- aDBA automatically tracks:
-- - Deadlock frequency over time
-- - Most common deadlock patterns
-- - Victim selection patterns
-- - Impact on application performance
-- - Correlation with system changes

Integration with First Responder Kit

aDBA leverages Brent Ozar's sp_BlitzLock for detailed deadlock analysis:

  • Automatic execution after deadlock detection
  • Historical comparison of deadlock patterns
  • Identification of "repeat offenders"
  • Prioritized remediation recommendations

Learn more about aDBA's automated deadlock monitoring →

Advanced Techniques

Snapshot Isolation

For certain workloads, Paul Randal recommends SNAPSHOT isolation:

-- Enable snapshot isolation
ALTER DATABASE YourDB 
SET ALLOW_SNAPSHOT_ISOLATION ON

-- Use in transactions
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
    -- Your queries here
COMMIT

Benefits: Complete isolation without blocking
Trade-off: Higher TempDB usage, potential for update conflicts

Query Hints

Grant Fritchey shows when hints can help:

-- Force row-level locking
SELECT * FROM Orders WITH (ROWLOCK)
WHERE Status = 'Pending'

-- Or force table-level locking to reduce lock escalation overhead
UPDATE Orders WITH (TABLOCK)
SET ProcessedDate = GETDATE()
WHERE Status = 'Pending'

Use sparingly and test thoroughly!

Real-World Deadlock Resolution

Case Study: E-commerce Order Processing

Problem: Frequent deadlocks during checkout process

Analysis (using techniques from Glenn Berry and Brent Ozar):

  • Deadlock between order insertion and inventory update
  • Missing indexes on Inventory table
  • Long transactions including external API calls

Solution:

  1. Created covering index on Inventory(ProductID) INCLUDE (Quantity)
  2. Moved API calls outside transaction
  3. Implemented READ COMMITTED SNAPSHOT
  4. Added retry logic with exponential backoff

Result: Deadlocks reduced from 50+/hour to < 1/day

When to Get Expert Help

Deadlocks can be complex to diagnose and resolve. Consider expert assistance when:

  • Deadlocks persist despite applying standard fixes
  • Deadlock patterns are unclear or intermittent
  • Application changes required but code access is limited
  • Business impact is severe
  • Need automated monitoring and alerting

SQLOPTIMISE specializes in deadlock resolution:

  • Rapid deadlock graph analysis
  • Root cause identification
  • Code and index recommendations
  • Automated monitoring implementation
  • Follow-up support for verification

Schedule a free deadlock analysis →

Best Practices Summary

  1. Monitor Continuously: Don't wait for user complaints
  2. Analyze Patterns: Most deadlocks follow predictable patterns
  3. Fix Root Causes: Don't just add NOLOCK everywhere
  4. Test Changes: Verify fixes in realistic test environments
  5. Implement Retry Logic: Some deadlocks are inevitable
  6. Keep Transactions Short: Minimize lock duration
  7. Use Proper Isolation: READ COMMITTED SNAPSHOT for most OLTP
  8. Index Strategically: Cover frequent queries to reduce lock times

Conclusion

Deadlocks are inevitable in busy databases, but proper monitoring, analysis, and prevention can minimize their impact. By following expert guidance from Brent Ozar, Paul Randal, Kendra Little, and others, and implementing automated monitoring through solutions like aDBA, you can maintain smooth database operations even under heavy concurrent load.

Remember Brent Ozar's advice: "The best deadlock is the one you prevent before it happens."

Essential Resources

  • Brent Ozar: "How to Read a Deadlock Graph"
  • Paul Randal: "Deadlock Troubleshooting and Prevention"
  • Kendra Little: "Working with Deadlocks in SQL Server"
  • Jonathan Kehayias: "Using Extended Events for Deadlock Analysis"
  • Gail Shaw: "Dealing with Deadlocks in SQL Server"

Struggling with persistent deadlocks? Contact SQLOPTIMISE for expert deadlock analysis and resolution. We'll identify root causes and implement lasting solutions.

Need Expert SQL Help?

Our SQL optimization experts are ready to help you implement these strategies and optimize your database performance.

Schedule Free Consultation
Back to Blog