How to Fix SQL Server Deadlocks: Detection and Prevention

A SQL Server deadlock means two sessions are locked in a circular wait and neither can move forward. SQL Server resolves it by picking one session as the deadlock victim, rolling back its transaction, and returning error 1205 to the calling application. If your users are seeing intermittent transaction failures, or your application logs show error 1205, deadlocks are the cause.

This guide walks through how deadlocks form, how to capture and read deadlock graphs, the most effective fixes ranked by impact, long-term prevention strategies, and how AI SQL Tuner Studio automates the entire analysis workflow.

What Is a SQL Server Deadlock?

A deadlock occurs when two or more sessions each hold a lock that the other session needs to proceed. Because neither session will release its lock until it acquires the one it is waiting for, both sessions are stuck indefinitely. SQL Server’s lock monitor detects this circular dependency every few seconds and resolves it by selecting one session as the deadlock victim, rolling back its transaction, and returning error 1205 to that session’s caller.

Deadlocks are different from ordinary blocking. In a blocking scenario, Session A is waiting for Session B to release a lock, and Session B will eventually do so. The wait ends on its own. In a deadlock, Session A is waiting for Session B, and Session B is simultaneously waiting for Session A. The wait will never end without intervention.

SQL Server chooses the deadlock victim based on the cost of rolling back each transaction. By default, the session with the least transaction log activity is chosen as the victim. You can influence this by setting SET DEADLOCK_PRIORITY LOW in sessions that are acceptable victims, or SET DEADLOCK_PRIORITY HIGH in sessions that should be protected.

How to Detect SQL Server Deadlocks

SQL Server records deadlock details as XML documents called deadlock graphs. You can capture these through Extended Events, which is the recommended approach in modern SQL Server versions. Trace Flag 1222, an older method, writes deadlock information to the SQL Server error log and remains useful for quick diagnostics on servers where you cannot create Extended Events sessions.

The system_health Extended Events Session

SQL Server runs a built-in Extended Events session called system_health at all times. This session captures deadlock graphs automatically and stores them in a ring buffer in memory, as well as in XEL files on disk. You can query recent deadlocks from the ring buffer using the following script:

SELECT
    xdr.value('@timestamp', 'datetime2') AS DeadlockTime,
    xdr.query('.') AS DeadlockGraph
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 = 'system_health'
      AND t.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xdr)
ORDER BY DeadlockTime DESC;

The system_health ring buffer holds a limited number of events and is cleared on SQL Server restart. For a production system with recurring deadlocks, create a dedicated session that writes to a file target for persistent storage.

Creating a Dedicated Deadlock Capture Session

The following Extended Events session captures all deadlock graphs to a file target on disk. Adjust the file path to a location with sufficient free space:

CREATE EVENT SESSION [DeadlockCapture] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report(
    ACTION(
        sqlserver.client_app_name,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.sql_text
    )
)
ADD TARGET package0.event_file(
    SET filename = N'C:\DeadlockLogs\Deadlocks.xel',
    max_file_size = (50),
    max_rollover_files = (10)
)
WITH (
    STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION [DeadlockCapture] ON SERVER STATE = START;

Set STARTUP_STATE = ON so the session resumes automatically after a SQL Server restart. Review captured files in SSMS by opening the XEL file via File > Open > File, which renders deadlock graphs visually.

Reading the Deadlock Graph

A deadlock graph shows each involved session as an oval and each contested resource as a rectangle. Arrows between ovals and rectangles represent lock requests and grants. The session marked as the deadlock victim has an X through it.

To diagnose a deadlock from its graph, work through these steps:

• Identify the two sessions and the SQL statement each was executing at the time of the deadlock.
• Note the object ID and index ID of each contested resource shown in the graph.
• Map those IDs to table and index names using sys.partitions and sys.indexes.
• Compare the lock types each session held versus what each was waiting for to classify the root cause.

Use this script to map object and index IDs from a deadlock graph to their names:

SELECT
    OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName,
    OBJECT_NAME(p.object_id)        AS TableName,
    i.name                          AS IndexName,
    p.object_id,
    p.index_id
FROM sys.partitions p
LEFT JOIN sys.indexes i
    ON p.object_id = i.object_id
    AND p.index_id = i.index_id
WHERE p.object_id = <object_id_from_graph>;

Root Cause Categories

Most SQL Server deadlocks fall into one of five categories. Identifying the correct category before attempting a fix prevents wasted effort on solutions that do not apply to the specific pattern.

• Access order mismatch. Two transactions update the same tables but in different sequences. Transaction A updates Orders then OrderLines; Transaction B updates OrderLines then Orders. When both run concurrently, each holds a lock the other needs. This is the most common deadlock pattern and the most straightforward to fix.
• Schema lock contention. A DDL operation (ALTER TABLE, CREATE INDEX, DROP INDEX) requires a Schema Modification (Sch-M) lock, which is incompatible with the Schema Stability (Sch-S) locks held by concurrent SELECT queries. Common in environments where index maintenance runs during active query workloads.
• Lock escalation. SQL Server escalates many row-level locks to a table-level lock when a session accumulates more than 5,000 row locks. If another session holds locks anywhere in that table, the escalation attempt creates a deadlock.
• Key-range locks. Under SERIALIZABLE isolation, range scans acquire key-range locks to prevent phantom reads. Two sessions scanning overlapping ranges can deadlock if their ranges intersect.
• Hotspot contention. Many concurrent sessions updating the same row or page, such as a sequence counter or audit table, create high lock contention that can escalate to deadlocks.

How to Fix SQL Server Deadlocks

The fixes below are ordered by impact and applicability. Most deadlock scenarios are resolved by one or two of these changes. Start with the fix that matches the root cause category you identified from the deadlock graph.

Enable Read Committed Snapshot Isolation (RCSI)

RCSI changes how SQL Server handles read operations. Instead of acquiring shared locks on rows, readers use a row version stored in tempdb, allowing reads to proceed without blocking writers and writers to proceed without blocking readers. This eliminates all deadlocks caused by reader-writer conflicts, including schema lock contention between SELECT queries and DDL operations.

RCSI is a database-level setting. Enable it during a maintenance window because the WITH ROLLBACK IMMEDIATE option terminates any active connections to the database:

-- Enable snapshot isolation support first
ALTER DATABASE [YourDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Enable RCSI (requires a brief exclusive database access)
ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
GO

-- Verify the setting
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'YourDatabase';

After enabling RCSI, monitor tempdb space usage. The version store in tempdb grows under write-heavy workloads. A 10 to 20 percent increase in tempdb utilization is typical for mixed OLTP workloads. RCSI does not fix write-write deadlocks caused by access order mismatches.

Enforce Consistent Table Access Order

When two stored procedures or code paths update the same set of tables, they must always access those tables in the same order. If Procedure A updates the parent table first and then the child table, Procedure B must follow the same sequence. Accessing them in reverse order creates the conditions for a deadlock whenever both procedures run concurrently.

The standard approach is to update parent tables before child tables, which aligns with foreign key relationships and is the most intuitive ordering to document and maintain. Add a comment in each stored procedure that explicitly states the access order and why, so future developers do not inadvertently reverse it.

-- Correct pattern: always update parent (Orders) before child (OrderLines)
BEGIN TRANSACTION;

    UPDATE Sales.Orders
    SET Comments = @Comments
    WHERE OrderID = @OrderID;

    UPDATE Sales.OrderLines
    SET Description = @Description
    WHERE OrderID = @OrderID;

COMMIT TRANSACTION;

Shorten Transaction Scope

The longer a transaction holds locks, the higher the probability that another session will need the same resource during that window. Two practices cause most unnecessary transaction length: performing work outside the critical update path inside a transaction boundary, and calling user interfaces or external services from within a transaction.

Move all preparatory work (lookups, calculations, validation) before the BEGIN TRANSACTION statement. Move all non-critical post-processing (logging, notifications) after the COMMIT. The transaction itself should contain only the data modifications that must be atomic.

-- Pattern to avoid: work inside the transaction extends lock duration
BEGIN TRAN;
    EXEC dbo.usp_ValidateOrder @OrderID;   -- locks held during validation
    UPDATE Sales.Orders SET Status = 'Approved' WHERE OrderID = @OrderID;
    WAITFOR DELAY '00:00:02';              -- locks held during arbitrary wait
COMMIT;

-- Correct pattern: transaction contains only the essential update
EXEC dbo.usp_ValidateOrder @OrderID;      -- validation before transaction
BEGIN TRAN;
    UPDATE Sales.Orders SET Status = 'Approved' WHERE OrderID = @OrderID;
COMMIT;

Add Targeted Indexes to Reduce Lock Footprint

A query that performs a table scan acquires many more row locks than a query that performs an index seek. Reducing the number of rows a transaction touches directly reduces its lock footprint and the probability of conflicting with another session. For deadlock scenarios involving range scans or large reads, add a nonclustered index that converts scans to seeks.

Review the deadlock graph for table scans on the contested tables, then create covering indexes for the queries involved. The index optimization guide at SQL Server Index Optimization covers the full process for identifying and creating the right indexes.

Implement Application-Side Retry Logic

Even after addressing the root cause, transient deadlocks can still occur during periods of high concurrency. Applications should catch error 1205 and retry the failed transaction with an exponential backoff. This prevents deadlocks from surfacing as failures to end users while permanent fixes are deployed and validated.

The retry pattern at the T-SQL level looks like this:

CREATE OR ALTER PROCEDURE dbo.usp_UpdateWithRetry
    @OrderID INT,
    @MaxRetries INT = 3
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Attempt INT = 0;

    WHILE @Attempt < @MaxRetries
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
                UPDATE Sales.Orders
                SET Status = 'Processed'
                WHERE OrderID = @OrderID;
            COMMIT TRANSACTION;
            RETURN;  -- success, exit the loop
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
            IF ERROR_NUMBER() = 1205  -- deadlock victim
            BEGIN
                SET @Attempt += 1;
                WAITFOR DELAY '00:00:00.1';  -- 100ms backoff
            END
            ELSE
                THROW;  -- re-raise non-deadlock errors
        END CATCH
    END
    THROW 50001, 'Max deadlock retries exceeded.', 1;
END

Monitoring and Alerting

Deadlock fixes that seem effective in testing sometimes mask underlying contention that recurs when load increases. Persistent monitoring gives you an early warning before deadlock frequency reaches the level where users notice failures.

Create a deadlock history table that the Extended Events session populates, then build a SQL Agent alert that fires when deadlocks exceed a threshold within a rolling time window:

-- Table to store deadlock history
CREATE TABLE dbo.DeadlockHistory (
    DeadlockID   INT IDENTITY(1,1) PRIMARY KEY,
    CaptureTime  DATETIME2        DEFAULT SYSDATETIME(),
    DatabaseName NVARCHAR(128),
    DeadlockGraph XML             NOT NULL
);

CREATE INDEX IX_DeadlockHistory_CaptureTime
    ON dbo.DeadlockHistory (CaptureTime DESC);

Query the history table regularly to identify the top offending queries and tables. A query appearing in multiple deadlock graphs over a short period is a reliable signal that a structural fix is needed rather than a retry-only approach.

Three metrics worth tracking on an ongoing basis:

• Deadlock frequency per hour, segmented by database. A sudden spike indicates a code deployment or schema change introduced a new contention pattern.
• Top offending queries by appearance count in deadlock graphs. These are the highest-priority candidates for code-level fixes.
• Deadlock victim rate by application. If a single application is disproportionately the victim, that application’s transaction design is a likely contributor.

AI-Powered Deadlock Analysis with AI SQL Tuner Studio

Working through deadlock graphs, mapping object IDs to table names, cross-referencing lock types, and then selecting the right fix from multiple options is a time-intensive process. AI SQL Tuner Studio automates this entire workflow and delivers a prioritized, actionable report in minutes.

How AI SQL Tuner Studio Analyzes Deadlocks

AI SQL Tuner Studio connects to your SQL Server instance using a least-privilege read-only account. It captures recent deadlock graphs from the system_health session and any active Extended Events sessions, maps all resource identifiers to schema objects, and passes the full analysis to an AI model. The AI classifies each deadlock by root cause, groups related deadlocks that share the same underlying pattern, and generates a prioritized recommendation list with T-SQL scripts for each fix.

See sample AI SQL Tuner Studio reports for examples of deadlock analysis output, including the recommendation format and implementation scripts.

Sample AI SQL Tuner Studio Deadlock Output

The following is an example of AI SQL Tuner Studio output from a real deadlock analysis session against the WideWorldImporters and TPC-H databases. The tool identified two distinct deadlock patterns and produced five prioritized recommendations.

Executive Summary

• CRITICAL: Two distinct deadlock patterns detected requiring immediate attention.
• CRITICAL: Read Committed Snapshot Isolation is disabled, causing schema lock contention between DDL operations and SELECT queries.
• CRITICAL: DDL operations (ALTER TABLE) executing concurrently with analytical queries causing Sch-M vs. Sch-S deadlocks.
• HIGH: Classic update-update deadlock between Orders and OrderLines tables with inverse access order in two stored procedures.

Top 3 Prioritized Actions

1. Enable RCSI on the tpch database. Expected to eliminate 80% of observed deadlocks (schema lock patterns) immediately.
2. Establish DDL maintenance windows to prevent concurrent DDL during active query workloads.
3. Refactor Website.UpdLinesandOrdersFromWeb and Website.UpdOrdersandLinesFromWeb to enforce consistent table access order (Orders first, then OrderLines).

Supported AI Models

AI SQL Tuner Studio integrates with leading AI models for deadlock analysis:

• All editions: OpenAI GPT-5.4 and Anthropic Claude Sonnet 4.6
• Corporate edition (in addition to the above): Anthropic Claude Opus 4.6 and Anthropic Claude Opus 4.7, providing deeper reasoning for complex multi-database deadlock patterns

If the primary model deployment hits a rate limit, AI SQL Tuner Studio automatically retries with a secondary deployment so your analysis completes without interruption.

Supported Environments

AI SQL Tuner Studio deadlock analysis works across SQL Server Developer Edition (free, no time limit), SQL Server Standard and Enterprise editions, Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric SQL Database.

The free edition covers SQL Server Developer Edition at no cost. Paid plans include a 14-day free trial and a 30-day money-back guarantee. Download the free edition or compare editions and pricing.

For a broader view of SQL Server performance tuning that includes index optimization and query analysis, see the SQL Server Performance Tuning Guide. For index-specific guidance, see SQL Server Index Optimization.

Frequently Asked Questions

Fix SQL Server Deadlocks Today

Most SQL Server deadlocks are caused by a small number of well-understood patterns: access order mismatches, RCSI being disabled, schema lock contention from DDL, or transactions that hold locks longer than necessary. Identifying which pattern applies to your specific deadlock requires reading the deadlock graph carefully and cross-referencing the involved sessions and resources.

AI SQL Tuner Studio automates that diagnostic work, using GPT-5.4 or Claude Sonnet 4.6 (or Claude Opus in the Corporate edition) to classify your deadlocks and produce a prioritized fix plan with implementation scripts. Download the free edition for SQL Server Developer Edition, or explore paid plans for production environments, all backed by a 30-day money-back guarantee.