Last updated: October 17, 2026
A SQL Server deadlock happens when two or more sessions each hold a lock the other needs, so neither can move forward. SQL Server’s lock monitor breaks the tie by choosing one session as the deadlock victim, rolling back its transaction, and returning error 1205 to that session’s caller. If your users see intermittent transaction failures, or your logs show error 1205, deadlocks are the cause.
This guide covers how deadlocks form, how to capture and read deadlock graphs, the five root-cause categories, the most effective fixes ranked by impact, long-term prevention and monitoring, and how AI SQL Tuner Studio automates the whole diagnosis in minutes with its Fix Deadlocks goal.
Table of Contents
What Is a SQL Server Deadlock?
A deadlock occurs when two or more sessions each hold a lock that the other needs to proceed. Because neither session releases its lock until it acquires the one it is waiting for, both 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 blocking, Session A waits for Session B to release a lock. Session B eventually releases its lock and the wait ends on its own. In a deadlock, Session A waits for Session B while Session B simultaneously waits for Session A, so the wait never ends without intervention.
SQL Server chooses the victim based on the cost of rolling back each transaction; by default the session with the least transaction log activity is chosen. You can influence this with 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 them with Extended Events, which is the recommended approach on modern versions of SQL Server. Trace Flag 1222, an older method, writes deadlock information to the SQL Server error log and is still handy 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. It captures deadlock graphs automatically and stores them in an in-memory ring buffer and in XEL files on disk. You can query recent deadlocks with:
SELECT xe.event_data.value('(event/@timestamp)[1]', 'datetime2') AS deadlock_time_utc, xe.event_data.query('(event/data/value/deadlock)[1]') AS deadlock_graphFROM ( SELECT CAST(event_data AS xml) AS event_data FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) WHERE object_name = 'xml_deadlock_report') AS xeORDER BY deadlock_time_utc DESC;
The ring buffer holds a limited number of events and is cleared on restart. For a production system with recurring deadlocks, consider creating 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 SERVERADD 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);GOALTER EVENT SESSION [DeadlockCapture] ON SERVER STATE = START;
Set STARTUP_STATE = ON so the session resumes automatically after a restart. Review captured files in SSMS 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:
- 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.partitionsandsys.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_idFROM sys.partitions pLEFT JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_idWHERE p.object_id = <object_id_from_graph>;
The 5 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 in different sequences (A updates Orders then OrderLines; B updates OrderLines then Orders). Run concurrently, each holds a lock the other needs. This is the most common 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, incompatible with the Schema Stability (Sch-S) locks held by concurrent SELECT queries. This commonly occurs when index maintenance runs during active query workloads.
- Lock escalation. SQL Server escalates many row-level locks to a table-level lock once 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 when 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.
This list is not comprehensive. Deadlock #1 in the screenshot below illustrates an access order mismatch. But deadlocks #2-#5 are a parallel worker exchange deadlock, which happens when workers each grab U locks on different keys and then need to pass rows through exchange ports. They can end up waiting on each other’s buffers. The exchange port wait becomes part of the deadlock cycle.

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 reads. Instead of acquiring shared locks on rows, readers use a row version stored in tempdb, so reads proceed without blocking writers and writers proceed without blocking readers. This eliminates 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 WITH ROLLBACK IMMEDIATE terminates any active connections to the database:
-- Enable snapshot isolation support firstALTER DATABASE [YourDatabase] SET ALLOW_SNAPSHOT_ISOLATION ON;GO-- Enable RCSI (requires brief exclusive database access)ALTER DATABASE [YourDatabase] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;GO-- Verify the settingSELECT name, is_read_committed_snapshot_onFROM sys.databasesWHERE name = 'YourDatabase';
After enabling RCSI, monitor tempdb usage. The version store grows under write-heavy workloads, and a 10–20% increase in tempdb utilization is typical for mixed OLTP. RCSI does not fix write-write deadlocks caused by access order mismatches.
Enforce Consistent Table Access Order
When two 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 then the child table, Procedure B must follow the same sequence; reversing it creates the conditions for a deadlock whenever both 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 procedure stating 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 another session needs the same resource during that window. Two habits cause most unnecessary transaction length: doing work outside the critical update path inside the transaction boundary, and calling user interfaces or external services from within a transaction.
Move preparatory work (lookups, calculations, validation) before BEGIN TRANSACTION, and non-critical post-processing (logging, notifications) after COMMIT. The transaction itself should contain only the data modifications that must be atomic.
-- Pattern to avoid: work inside the transaction extends lock durationBEGIN 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 waitCOMMIT;-- Correct pattern: transaction contains only the essential updateEXEC dbo.usp_ValidateOrder @OrderID; -- validation before transactionBEGIN TRAN; UPDATE Sales.Orders SET Status = 'Approved' WHERE OrderID = @OrderID;COMMIT;
Add Targeted Indexes to Reduce Lock Footprint
A query that scans a table acquires many more row locks than a query that seeks an index. Reducing the rows a transaction touches directly reduces its lock footprint and the chance of conflicting with another session. For deadlocks 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 SQL Server index tuning guide 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 high concurrency. Applications should catch error 1205 and retry the failed transaction with an exponential backoff, so deadlocks don’t surface as failures to end users while permanent fixes are validated.
The retry pattern at the T-SQL level looks like this:
CREATE OR ALTER PROCEDURE dbo.usp_UpdateWithRetry @OrderID INT, @MaxRetries INT = 3ASBEGIN 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
Fixes that look effective in testing sometimes mask contention that recurs under load. Persistent monitoring gives an early warning before deadlock frequency reaches the level where users notice failures.
Create a deadlock history table that your Extended Events session populates, then build a SQL Agent alert that fires when deadlocks exceed a threshold within a rolling window:
-- Table to store deadlock historyCREATE 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 find 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:
- Deadlock frequency per hour, segmented by database. A sudden spike indicates a deployment or schema change introduced a new contention pattern.
- Top offending queries by appearance count in deadlock graphs — the highest-priority candidates for code-level fixes.
- Deadlock victim rate by application. If one application is disproportionately the victim, its transaction design is a likely contributor.
Fix Deadlocks Faster with AI SQL Tuner Studio
Working through deadlock graphs, mapping object IDs to table names, cross-referencing lock types, and choosing the right fix from several options is time-intensive. AI SQL Tuner Studio‘s Fix Deadlocks goal automates the whole workflow and returns a prioritized report with rewritten code in minutes.
How the Fix Deadlocks goal works
- Collects the deadlock graphs. Reads the most recent deadlocks (1–20, default 5) from the
system_healthevent files (sys.fn_xe_file_target_read_file) on SQL Server and Azure SQL Managed Instance. On Managed Instance it also reads the ring buffer for deadlocks not yet written to a file, and reports a deadlock found in both once. If no deadlocks are found, it writes an informational report without calling the AI. - Checks RCSI (
is_read_committed_snapshot_on) for the selected database. - Extracts the code involved. Reads the current definition of every stored procedure, function and trigger in each process’s execution stack from
sys.sql_modules, and the text of ad hoc batches from the plan cache. Limits: up to 30 objects, 60,000 characters each and 300,000 in total. - Names what ran it. Each process and each piece of code is labelled with its host, login and application. When a SQL Agent job step ran it, the job and step are named.
- Finds the root cause. The AI identifies the victim, each process and the contested keys, pages or objects, and cites the exact statements and line numbers that take the conflicting locks.
- Rewrites the code. For every module whose full text was retrieved, the Scripts section has a complete
CREATE OR ALTERdefinition, ready to review and deploy. A module involved in several deadlocks gets one definition that fixes all of them. For an ad hoc batch it gives the revised batch and says whether the SQL Agent job step or the application code must change. - Prioritizes the next steps, such as consistent access order, shorter transactions, targeted indexes, isolation-level changes and retry handling.
Which SQL Server platforms support deadlock analysis?
- On-premises SQL Server — Supported (reads deadlocks from the
system_healthExtended Events file target). - Azure SQL Managed Instance — Supported. Reads the
system_healthevent files, plus the ring buffer for deadlocks not yet written to a file. - Azure SQL Database — Not supported (no access to
system_healthdeadlock graphs). The tool returns recommended alternatives. - Microsoft Fabric SQL Database — Not supported (treated like Azure SQL Database for deadlock extraction). The tool returns recommended alternatives.
On an unsupported platform, AI SQL Tuner Studio returns specific guidance and alternatives such as Index Tuning, Code Review, or Azure Portal’s Query Performance Insight.
How to run it
- Select a connection in Connections, or click New.
- Under What should we tune today?, choose Fix Deadlocks.
- Set Deadlocks to analyze (1–20, default 5).
- The database is optional and defaults to
master. Pick the application’s database so the RCSI check covers it. - Choose the MODEL and REASONING level (default Medium).
- Click Run Analysis (Ctrl+R). The Review run plan dialog shows the connection, the AI call and each collector, with the DMVs it reads. Click Send to AI.
- The report streams into HTML Output. Use Save to keep the report, or Export SQL for its scripts.
Minimum permissions:
VIEW SERVER STATE: reads thesystem_healthevent files (plus the ring buffer on Managed Instance) and ad hoc batch text from the plan cache.- Read access to
sys.databasesfor the RCSI check (granted topublicby default). - Access to each database that holds deadlocked code, plus
VIEW DEFINITIONthere, to read procedure, function and trigger definitions. Without it, the tool falls back to the plan-cache text. - Optional:
SELECTonmsdb.dbo.sysjobsandsysjobsteps(for example viaSQLAgentReaderRole) to show SQL Agent job and step names. Without it, jobs are shown by their id.
Sample output
Example output from a real analysis against the WideWorldImporters database is shown below. The tool identified several deadlock patterns and produced five prioritized recommendations, including expected impact and confidence level for each one.

Top priorities
- Standardize resource order in every caller and stored procedure. This directly removes the circular wait in all five graphs and is the highest-impact fix.
- Remove deliberate waits inside transactions. The input contains
WAITFOR DELAYvalues of 2, 3, 5, and 10 seconds. These are not the root cause, but they materially enlarge the interval during which locks are held. - Deploy the revised DeadlockDemo procedures and change the ad hoc callers.
usp_PayOrder,usp_Refund.Order, and the direct transaction in CODE-7 must use the same Accounts-then-Orders order. - Change both WideWorldImporters test batches to Countries-then-StateProvinces. This removes the #5 cycle; the rewrite also removes the 10-second lock-holding delay.
- Do not prioritize RCSI or new indexes for these graphs. The conflicts are writer-versus-writer key locks, and the shown predicates already target primary-key values. No index change is supported by the supplied evidence.
See a complete sample deadlock report or browse all sample reports.
Supported AI models
All editions include GPT-5.6 Luna, the default model. Team and Enterprise add GPT-6 Sol; run it at Medium or High reasoning effort, not Low. Reasoning effort defaults to Medium. The Free edition can choose Low or Medium, and High requires Pro, Team or Enterprise. Pro, Team and Enterprise can also bring their own Azure AI Foundry or Anthropic-compatible endpoint.
Editions and platforms
AI SQL Tuner Studio runs on Windows against SQL Server (on-prem and IaaS), Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric SQL Database. The Free edition includes up to 10 runs per month; paid editions bought on aisqltuner.com include a 14-day trial and a 30-day money-back guarantee. Download the free edition or compare editions and pricing. (Note: the Fix Deadlocks goal specifically requires on-premises SQL Server or Azure SQL Managed Instance.)
Frequently Asked Questions
What causes SQL Server deadlocks?
SQL Server deadlocks occur when two or more sessions hold locks that each other needs, and neither can proceed. The most common causes are transactions accessing the same tables in different orders, long-running transactions that hold locks while doing unrelated work, range scans under SERIALIZABLE isolation, and DDL operations running concurrently with active SELECT queries. SQL Server selects a deadlock victim, rolls back its transaction, and returns error 1205.
What is the difference between a deadlock and blocking in SQL Server?
Blocking occurs when one session holds a lock another session is waiting for; the waiting session proceeds once the lock is released. A deadlock is a circular blocking situation where Session A waits for Session B and Session B waits for Session A simultaneously — neither can proceed on its own. SQL Server detects the cycle and terminates one session to break it. Blocking resolves itself; deadlocks require SQL Server intervention.
How do I read a SQL Server deadlock graph?
A deadlock graph shows each session as an oval and each contested resource as a rectangle, with arrows for lock requests and grants; the victim session has an X through it. To analyze it: identify the two sessions and their SQL statements, note the object and index IDs of the contested resource, map those IDs to table and index names using sys.partitions and sys.indexes, then classify the root cause from the lock types involved (shared vs. exclusive, Sch-S vs. Sch-M).
Does enabling Read Committed Snapshot Isolation fix all SQL Server deadlocks?
No. RCSI eliminates deadlocks caused by readers conflicting with writers by using row versioning instead of shared locks for reads, which resolves schema lock contention between SELECT queries and DDL operations. It does not fix deadlocks caused by two writers accessing the same rows in different orders, or by application-level transaction design issues. RCSI also adds version-store overhead in tempdb, so test its impact before enabling in production.
Which SQL Server platforms support deadlock analysis in AI SQL Tuner Studio?
The Fix Deadlocks goal is supported on on-premises SQL Server (reads from the system_health Extended Events file target) and Azure SQL Managed Instance (reads from the system_health ring buffer). It is not supported on Azure SQL Database or Microsoft Fabric SQL Database, because those platforms do not expose the system_health deadlock graphs; the tool returns recommended alternatives instead.
What permissions are required to run Fix Deadlocks?
Fix Deadlocks requires VIEW SERVER STATE to read sys.fn_xe_file_target_read_file (on-premises) or sys.dm_xe_session_targets and sys.dm_xe_sessions (Azure SQL MI ring buffer). Read access to sys.databases for the RCSI status check is granted to public by default.
What happens if no deadlocks are found?
When no deadlocks are found in the system_health session, the tool generates an informational HTML report directly without calling the AI, saving time and cost. The report confirms no deadlock events were recorded and suggests running Index Tuning, Code Review, or Server Health for further analysis.
The check covers the whole server, not just the selected database. system_health keeps only a rolling history, so this means no deadlocks were recorded recently, not that none have ever occurred.
How many deadlocks can AI SQL Tuner Studio analyze at once?
Fix Deadlocks can analyze between 1 and 20 recent deadlock graphs in a single run; the default is 5. Configure this with the “Deadlocks to analyze” option before clicking Run.
Can AI automate SQL Server deadlock analysis?
Yes. AI SQL Tuner Studio reads recent deadlock graphs and the code each session was running, identifies the statements and lines that take the conflicting locks, names the application or SQL Agent job that ran them, and returns rewritten procedures as complete CREATE OR ALTER scripts. Fix Deadlocks is read-only: nothing is changed on your server, and you review and deploy the scripts yourself.
Related Resources
- SQL Server Deadlocks Guide (Microsoft Docs)
- Extended Events system_health Session (Microsoft Docs)
- SQL Server Index Tuning Guide
- SQL Server Performance Tuning Guide
- AI SQL Tuner Studio User Guide
Fix SQL Server Deadlocks Today
Most SQL Server deadlocks come from a small set of well-understood patterns: access order mismatches, RCSI disabled, schema lock contention from DDL, or transactions holding locks longer than necessary. Identifying which pattern applies means reading the deadlock graph and cross-referencing the involved sessions and resources. This is work that AI SQL Tuner Studio automates, classifying your deadlocks and producing a prioritized fix plan with implementation scripts in minutes. Download the free edition for SQL Server Developer Edition, or explore paid plans, all backed by a 30-day money-back guarantee on purchases from aisqltuner.com.