Error 1205 means SQL Server ended one transaction to break a deadlock cycle. Deadlock graphs show the participating processes and resources needed to explain that decision.
Understand the Cycle Rather Than Blaming the Victim
A deadlock occurs when participating work cannot proceed because each side depends on a resource held within the cycle. The engine selects a victim, rolls back that transaction, and reports error 1205. Ordinary blocking can resolve when its owner finishes; a deadlock requires the cycle to be broken.
The victim is not automatically the transaction responsible for poor design. Selection follows the applicable priority and rollback-cost rules. Review every participant's access order and transaction scope. I start with the cycle's resources rather than assuming the process that received the error needs the only correction.
Preserve the occurrence time and application context. A client retry can hide the immediate symptom while the same cycle continues recurring. Conversely, an isolated incident can have a very specific execution pattern. Keep deadlock graphs, frequency, and business impact together before choosing a broad database change.
Check That System Health Captured the Event
The default system_health Extended Events session captures xml_deadlock_report on SQL Server. Verify that the session is running and inspect its targets. Finite target retention means the older graph can be overwritten; absence from the current target does not prove that no deadlock occurred.
SELECT s.name,t.target_name,t.target_data
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t ON t.event_session_address=s.address
WHERE s.name=N'system_health';The output can contain private application and statement details. Keep it within the approved diagnostic scope. Required permissions depend on the SQL Server version. A stopped session, restricted metadata, or lost target files needs an explicit coverage note rather than an empty-result conclusion.
Capture important incidents promptly and retain the relevant event outside the rolling target through the approved process. A default session is convenient evidence collection, not a permanent incident archive. Its retained history should not be expected to survive every restart, cleanup, or storage event.
Read Deadlock Graphs From the Ring Buffer
The ring-buffer target can provide recent captured events. Parse only the deadlock event and preserve the timestamp and XML graph. Its in-memory size and XML materialization limits make it a supplemental source when an event-file target is available.
SELECT e.n.value('@timestamp','datetime2(7)') AS EventUTC,
e.n.query('(data[@name="xml_report"]/value/deadlock)[1]') AS DeadlockGraph
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t ON t.event_session_address=s.address
CROSS APPLY (SELECT TRY_CONVERT(xml,t.target_data) AS TargetXML) AS x
CROSS APPLY x.TargetXML.nodes('/RingBufferTarget/event[@name="xml_deadlock_report"]') AS e(n)
WHERE s.name=N'system_health' AND t.target_name=N'ring_buffer'
ORDER BY EventUTC DESC;Inspect the event timestamp alongside the client error time with an explicit time-zone conversion where required. Keep the raw graph so the process and resource relationships can be re-examined. A cropped screenshot of one node cannot provide the complete cycle needed for analysis.
On a busy instance the ring-buffer XML can come back truncated, so a recent deadlock can be missing here while the event file still holds it. If the relevant event is missing, inspect the event-file target and the available retention window. Do not manufacture a graph from the current blocking state and label it as the earlier incident. Live wait evidence and a captured deadlock describe different states.
Pull Deadlock Graphs From the Event Files
Find the current system_health event-file path, then read the matching rollover files in that directory. The example assumes the default Windows local-file target. A different target location requires an approved path adjustment.
DECLARE @CurrentFile nvarchar(4000),@Pattern nvarchar(4000);
SELECT @CurrentFile=x.TargetXML.value(
'(EventFileTarget/File/@name)[1]','nvarchar(4000)')
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t ON t.event_session_address=s.address
CROSS APPLY (SELECT TRY_CONVERT(xml,t.target_data) AS TargetXML) AS x
WHERE s.name=N'system_health' AND t.target_name=N'event_file';
IF @CurrentFile IS NULL OR CHARINDEX(N'\',REVERSE(@CurrentFile))=0
THROW 50000,'Verify the system_health event-file target path.',1;
SET @Pattern=LEFT(@CurrentFile,LEN(@CurrentFile)-CHARINDEX(N'\',REVERSE(@CurrentFile))+1)
+N'system_health*.xel';
SELECT x.EventXML.value('(/event/@timestamp)[1]','datetime2(7)') AS EventUTC,
x.EventXML.query('(/event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadlockGraph
INTO #DeadlockEvents
FROM sys.fn_xe_file_target_read_file(@Pattern,NULL,NULL,NULL) AS f
CROSS APPLY (SELECT TRY_CONVERT(xml,f.event_data) AS EventXML) AS x
WHERE f.object_name=N'xml_deadlock_report';
SELECT EventUTC,DeadlockGraph FROM #DeadlockEvents ORDER BY EventUTC DESC;Scope large target reads to the relevant retained period in the approved diagnostic process. The illustrated query reads available matching files, not an unlimited history. Save a selected deadlock XML document as an .xdl file through SSMS and open it there for the graphical view. Preserve the event timestamp with that saved graph.
Split Deadlock Graphs Into Victims, Processes, and Resources
Choose the relevant captured graph, then inspect its components. The following example selects the newest retained graph for demonstration. An incident investigation should select the exact matching occurrence rather than assume the newest event is the user's error.
DECLARE @Graph xml=(SELECT TOP (1) DeadlockGraph FROM #DeadlockEvents ORDER BY EventUTC DESC);
SELECT v.n.value('@id','varchar(100)') AS VictimProcessID
FROM @Graph.nodes('/deadlock/victim-list/victimProcess') AS v(n);
SELECT p.n.value('@id','varchar(100)') AS ProcessID,
p.n.value('@spid','int') AS SessionID,
p.n.value('@waitresource','nvarchar(500)') AS WaitResource,
p.n.value('@isolationlevel','nvarchar(100)') AS IsolationLevel,
p.n.value('(inputbuf/text())[1]','nvarchar(4000)') AS InputBatch
FROM @Graph.nodes('/deadlock/process-list/process') AS p(n);
SELECT r.n.query('.') AS ResourceDetail
FROM @Graph.nodes('/deadlock/resource-list/*') AS r(n);Match each process ID to the owners and waiters under the resource nodes. Review execution-stack frames, index or object identifiers, lock modes, and transaction context. The input buffer can describe a broader submitted batch; it is not a substitute for the exact frame involved in the wait. Check the recorded statements and their actual access paths together.
Which resource does each transaction already own before requesting the next one? Answer that for both sides. The graph is a relationship record, and reading one statement in isolation misses its purpose. The victim process did not volunteer for the role, and its application log cannot explain the entire meeting.
Reproduce an Opposite-Order Cycle in Two Sessions
Create the lab table in an existing disposable database. Then use two independent sessions. Ensure both start without an unrelated open transaction and roll back any remaining lab work afterward.
CREATE TABLE dbo.DeadlockLab(ItemID int PRIMARY KEY,Amount int NOT NULL);
INSERT dbo.DeadlockLab VALUES(1,10),(2,20);Run this batch in session A, then immediately run the following batch in B during A's delay. Each transaction updates the two rows in opposite order, creating the intended cycle when the operations overlap.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.DeadlockLab SET Amount=Amount+1 WHERE ItemID=1;
WAITFOR DELAY '00:00:10';
UPDATE dbo.DeadlockLab SET Amount=Amount+1 WHERE ItemID=2;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
THROW;
END CATCH;SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.DeadlockLab SET Amount=Amount+1 WHERE ItemID=2;
WAITFOR DELAY '00:00:10';
UPDATE dbo.DeadlockLab SET Amount=Amount+1 WHERE ItemID=1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE()<>0 ROLLBACK TRANSACTION;
THROW;
END CATCH;The overlap determines whether the cycle occurs; do not claim a captured result before running it. Inspect the resulting event and verify both sessions have finished. Remove the table only after the lab transactions are closed.
Fix the Observed Pattern and Verify Recovery
Consistent access order, shorter transactions, and useful indexes can reduce particular deadlock patterns. Row-versioned reading can address some reader-writer cycles, but it does not remove every writer conflict. Choose a correction that matches the recorded resources and execution behavior.
Repeat the representative concurrent workload after the correction and inspect newly captured events. A single successful execution without overlap cannot demonstrate that a concurrency cycle was removed. Keep transaction boundaries and access order consistent during the comparison. If the graph identifies a different resource pattern afterward, treat that as new evidence rather than stretching the first diagnosis to cover it.
I pair a targeted fix with a bounded application retry for error 1205 where the operation is safe to repeat. Retry the complete transaction, preserve idempotency, and avoid duplicating external actions. Deadlock graphs support that review when the full cycle, application contract, and post-change evidence remain together.
Related reading on this blog: Setting Deadlock Priority to Control the Transaction that is Rolled Back and Your First Extended Events Session.
A deadlock victim is not the whole diagnosis, it is one participant in a recorded cycle that needs a targeted correction.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
