SQL Server deadlocks: reading a deadlock graph step by step

In short

First secure the right graph, with its time and origin. On SQL Server it is usually already in the system_health event session. Then read the three sections: who became the victim (victim-list), what the participants were running (process-list), and who holds which lock and who is waiting for it (resource-list). Trace the cycle from there. Only then judge access order, transaction length, indexes and isolation, for example read committed snapshot isolation (RCSI). The victim is not the culprit: SQL Server picks by priority and, at equal priority, the execution that is cheapest to roll back.

What error 1205 tells you about the incident

The application receives this message:

Msg 1205, Level 13, State 51, Server …, Procedure dbo.CancelOrder, Line 10
Transaction (Process ID 57) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

It tells you three things:

• The whole transaction has been rolled back, not just the statement on line 10. Everything this transaction had changed before is undone. Five past twelve, as we said.
• The other side carries on. It gets no error. Its locks stay in place, it finishes its transaction, and nothing about the incident shows up in its log.
• The message does not name the cause. It shows which execution was chosen and, with procedure and line, which statement it was waiting on. Who else was part of the cycle and which locks were involved is only in the graph.

SQL Server looks for such cycles on its own. By default the lock monitor checks every five seconds, and more often when deadlocks are frequent, down to 100 milliseconds. When it finds a cycle, it picks a victim, rolls it back and writes the xml_deadlock_report event. A deadlock is therefore something different from long blocking: in a blocking chain, someone waits for someone else who will finish eventually. In a deadlock, nobody finishes without intervention. If something is hanging right now and you are looking for the cause, see the guide Find blocking and timeouts. If transactions keep aborting in day-to-day operations, our urgent help assesses and stabilizes the situation with you.

Finding the right deadlock graph

system_health is already recording

On SQL Server and Azure SQL Managed Instance, the system_health event session runs out of the box. Among other things, it captures every deadlock graph and writes it to files in the instance's log directory. You don't need to set anything up, and you should not change the session for an investigation either.

Two properties are worth knowing:

• Retention is limited. In every version we tested (Developer edition), system_health had 10 files of 100 MB each, overwritten in turn. Microsoft states this for Standard and Enterprise; according to the documentation, smaller editions keep considerably less. How far back that reaches depends on how many events the instance produces. A graph that is gone does not prove there was no deadlock.
• New events arrive with a delay. The session writes with a latency of up to 120 seconds. In the lab, a deadlock was not yet in the file 12 seconds after the error. If you look right after a 1205, wait two minutes.

Query 1 lists the 50 most recent deadlocks with time, database, number of participants, victim and the types of the resources involved. The last column contains the complete graph. In SQL Server Management Studio, a click on it opens the XML. Save it with the extension .xdl, and SSMS shows the graph visually the next time you open it.

/* PSG · Deadlock · Query 1   Which deadlocks does system_health still hold? The latest 50, with graph.   Read-only. Reads the event files of the system_health session, changes nothing.   Permission: VIEW SERVER STATE,   from SQL Server 2022 on, VIEW SERVER PERFORMANCE STATE is enough.   The files are limited in size and number: a missing old graph   does not prove there was no deadlock. New deadlocks appear   with a delay of up to 120 seconds. Times in UTC.   Deliberately without a time filter: in our tests, a filter directly on   timestamp_utc returned no rows at all on SQL Server 2017 to 2022.   Reading large system_health files can take a few seconds.   MAXDOP 1: the query runs serially and does not occupy additional cores. */SELECT TOP (50)    d.time_utc,    DB_NAME(d.graph.value('(/deadlock/process-list/process/@currentdb)[1]', 'int'))        AS database_name,    d.graph.value('count(/deadlock/process-list/process)', 'int') AS processes,    d.graph.value('(/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(64)')        AS victim_process,    d.graph.query('for $r in /deadlock/resource-list/* return local-name($r)')           .value('.', 'nvarchar(400)') AS resources,    d.graph AS deadlock_graphFROM (    SELECT f.timestamp_utc AS time_utc,           CAST(f.event_data AS xml)               .query('/event/data[@name="xml_report"]/value/deadlock') AS graph    FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS f    WHERE f.object_name = N'xml_deadlock_report') AS dORDER BY d.time_utc DESCOPTION (MAXDOP 1);

This is what the result looked like in the test run on SQL Server 2022, without the graph column. The last deadlocks come from the case “A table that gets in its own way”, which occurred at short intervals:

time_utc                     database_name  processes  victim_process    resources
2026-11-07 10:06:41.6870000  DeadlockLab    2          processe0fbeeca8  keylock keylock
2026-11-07 10:06:41.5780000  DeadlockLab    2          processe0fbeeca8  keylock keylock
2026-11-07 10:06:41.4690000  DeadlockLab    2          processe0fbeeca8  keylock keylock

Query 1 ran without errors on SQL Server 2017, 2019, 2022 and 2025.

A login without permission gets error 300 (VIEW SERVER STATE permission was denied …, from 2022 on VIEW SERVER PERFORMANCE STATE …). On SQL Server 2017 and 2019, VIEW SERVER STATE is enough; from 2022 on, the narrower VIEW SERVER PERFORMANCE STATE works too.

Careful with a time filter on SQL Server 2017 to 2022

The obvious step would be to restrict Query 1 to the last 24 hours, say with WHERE f.timestamp_utc > DATEADD(day, -1, SYSUTCDATETIME()). On SQL Server 2017 and 2022, exactly this filter returned no rows at all in the lab, even though the file contained the deadlock. The same happened with a fixed point in time and with a variable. SQL Server 2019 behaved the same way in a further test. On SQL Server 2025 the same filter worked.

If you rely on it, you may conclude from an empty list that there were no deadlocks. Query 1 therefore does without the filter and only sorts. If you need a time range, convert the timestamp first and filter afterwards. That returned the correct result on all four versions, and the example in Microsoft's documentation for sys.fn_xe_file_target_read_file converts the timestamp the same way:

FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fCROSS APPLY (SELECT CAST(f.timestamp_utc AS datetime2(7)) AS ts) AS xWHERE f.object_name = N'xml_deadlock_report'  AND x.ts > DATEADD(day, -1, SYSUTCDATETIME())

We tested this on Linux with SQL Server 2017 CU31, 2019 CU32, 2022 CU27 and 2025 CU9. We have not established the cause and did not test Windows installations.

What to record with the graph

A graph on its own is hard to place. Record the following with it:

• Time with time zone. The event files store UTC; application logs often use local time.
• Version and build of the instance, the database and, from SQL Server 2025 on, whether optimized locking is enabled.
• The symptom in the application: which function reported the error and how often.
• The unchanged XML. For your own investigation, keep it as it is. Before you pass it on, remove login, host and application names as well as parameter values in the input buffer that may contain customer data.

What is different on Azure

Azure SQL Managed Instance behaves like SQL Server and has system_health. Azure SQL Database has no such session. For deadlocks there, Microsoft describes a separate event session at database level. We did not reproduce that path for this article; Microsoft's guide for Azure SQL Database describes it.

An example: two transactions waiting in a circle

An order system has two code paths. One books an order and first reserves stock in the inventory, then records the booking on the order. The other cancels an order and goes the other way round: first the order, then the inventory. Each one is correct on its own. Two developers wrote them at different times.

CREATE PROCEDURE dbo.BookOrder @OrderID int, @ItemID int, @Qty intASBEGIN TRANSACTION;    UPDATE dbo.Inventory SET Reserved = Reserved + @Qty   -- inventory first    WHERE ItemID = @ItemID;    -- more work in the same transaction    UPDATE dbo.Orders    SET Booked = Booked + @Qty    WHERE OrderID = @OrderID;COMMIT TRANSACTION;GOCREATE PROCEDURE dbo.CancelOrder @OrderID int, @ItemID int, @Qty intASBEGIN TRANSACTION;    UPDATE dbo.Orders    SET Booked = Booked - @Qty       -- order first    WHERE OrderID = @OrderID;    -- more work in the same transaction    UPDATE dbo.Inventory SET Reserved = Reserved - @Qty    WHERE ItemID = @ItemID;COMMIT TRANSACTION;

If both run at the same time for the same order, the cycle forms:

A holds the inventory row and needs the order. B holds the order and needs the inventory row. Neither can continue on its own. In the lab we created the pause between the two statements with WAITFOR so that the case occurs every time. In an application, that spot holds computation, a call to another service or simply the time between two database calls. The longer a transaction stays open, the wider the window for the cycle.

Reading the three sections of the graph

This is the graph of this case on SQL Server 2022, shortened to the details that matter for reading it. Click a process ID: the graph highlights it in all three sections.

victim-list: which execution was chosen?

The victim list contains just one process ID, here processe07793848. Look for this ID in the process list: it is session 57, the one that wanted to cancel, so B. The process… IDs are internal addresses. They are valid only within this one graph and connect its three sections.

SQL Server follows two rules when choosing. Priority comes first; a session can set it with SET DEADLOCK_PRIORITY. At equal priority, SQL Server picks the execution that is cheaper to roll back. In the graph, the logused attribute shows how many bytes of log each transaction had written up to that point. In the lab, this held on all versions:

So with a tie, the victim is not predictable; Microsoft describes the choice in that case as random. And being the victim is not a verdict: in the third case B “wins” only because B would have lost more work. Both code paths are equally wrongly ordered. A low priority for unimportant background work can make sense. But it only decides who loses, not whether there is a loser.

process-list: what were the participants running?

Each process describes one participating execution at the moment of the deadlock:

• spid is the session ID, the same as in the error message (Process ID 57).
• isolationlevel and logused describe the transaction: which isolation level it ran under and how much log it had written up to then.
• waitresource and lockMode tell you what the execution was waiting for and in which mode it wanted to lock. waittime is the wait in milliseconds until the deadlock was resolved.
• executionStack shows the call chain: procedure and line of the waiting statement, below it the caller. On some versions the statement text in the frame is cut off, as here with @ItemI….
• inputbuf contains the batch the client sent last. For a procedure that is the call with its parameters; for an application with query parameters it is often only the parameterized statement.

Read the process list as a snapshot: it shows the statement that was waiting, not the one that took the lock. For B, line 10 waits for the inventory row. The lock on the order with which B closes the cycle comes from an earlier statement of the same transaction that does not appear in the graph. For that you need the code.

resource-list: who holds, and who waits?

The resource list is the cycle itself. Each resource names its type, here keylock (a row in an index), the object and the index. Below that, owner-list lists the processes that hold a lock and waiter-list those that wait, each with its mode.

One quirk deserves a second look. The inventory row and the order row have the same value in parentheses in waitresource, (8194443284a0). This hash is computed from the key, and both rows have key 1. Only the number in front of it, the hobtid of the index, tells them apart. So the hash alone does not identify a row, and the hobtid is an internal number that differs from instance to instance. Read object and index from objectname and indexname, therefore, not from waitresource.

Query 2 takes a graph from system_health and breaks it down into these two views: one row per participant, and for each lock its holders and waiters.

/* PSG · Deadlock · Query 2   Break a deadlock graph down into participants and resources.   Read-only, reads system_health like Query 1.   @nr = 1 is the most recent stored deadlock.   Result 1: one row per process with victim, session, isolation level,             what it waits for, module and input buffer.   Result 2: one row per lock and participant,             first who holds it, then who waits.   Permission as for Query 1.   MAXDOP 1: the query runs serially and does not occupy additional cores. */DECLARE @nr int = 1;   -- 1 = most recent, 2 = second most recent …DECLARE @graph xml; SELECT @graph = g.graphFROM (    SELECT ROW_NUMBER() OVER (ORDER BY f.timestamp_utc DESC) AS nr,           CAST(f.event_data AS xml)               .query('/event/data[@name="xml_report"]/value/deadlock') AS graph    FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS f    WHERE f.object_name = N'xml_deadlock_report') AS gWHERE g.nr = @nrOPTION (MAXDOP 1); -- Result 1: the participantsSELECT    x.process,    CASE WHEN @graph.exist(             '/deadlock/victim-list/victimProcess[@id = sql:column("x.process")]') = 1         THEN 'yes' ELSE '' END                                  AS victim,    p.n.value('@spid', 'int')                                    AS session_id,    p.n.value('@isolationlevel', 'varchar(40)')                  AS isolation,    p.n.value('@waitresource', 'varchar(200)')                   AS waiting_for,    p.n.value('@lockMode', 'varchar(10)')                        AS mode,    p.n.value('@waittime', 'int')                                AS wait_ms,    p.n.value('@logused', 'bigint')                              AS log_bytes,    p.n.value('@trancount', 'int')                               AS trancount,    p.n.value('(executionStack/frame/@procname)[1]', 'nvarchar(400)') AS module,    p.n.value('(executionStack/frame/@line)[1]', 'int')          AS line,    LTRIM(RTRIM(p.n.value('(inputbuf)[1]', 'nvarchar(4000)')))   AS input_buffer,    p.n.value('@clientapp', 'nvarchar(200)')                     AS application,    p.n.value('@hostname', 'nvarchar(200)')                      AS clientFROM @graph.nodes('/deadlock/process-list/process') AS p(n)CROSS APPLY (SELECT p.n.value('@id', 'varchar(64)') AS process) AS xOPTION (MAXDOP 1); -- Result 2: the resources with holders and waitersSELECT    r.n.value('@id', 'varchar(64)')                              AS lock_id,    r.n.value('local-name(.)', 'varchar(40)')                    AS resource,    COALESCE(r.n.value('@objectname', 'nvarchar(400)'),             r.n.value('(UnderlyingResource/*/@objectname)[1]', 'nvarchar(400)'))                                                                 AS object_name,    COALESCE(r.n.value('@indexname', 'nvarchar(200)'),             r.n.value('(UnderlyingResource/*/@indexname)[1]', 'nvarchar(200)'))                                                                 AS index_name,    b.role,    b.process,    b.modeFROM @graph.nodes('/deadlock/resource-list/*') AS r(n)CROSS APPLY (    SELECT N'holds', o.n.value('@id', 'varchar(64)'), o.n.value('@mode', 'varchar(10)')    FROM r.n.nodes('owner-list/owner') AS o(n)    UNION ALL    SELECT N'waits', w.n.value('@id', 'varchar(64)'), w.n.value('@mode', 'varchar(10)')    FROM r.n.nodes('waiter-list/waiter') AS w(n)) AS b(role, process, mode)ORDER BY lock_id, b.role   -- per lock: "holds" first, then "waits"OPTION (MAXDOP 1);

For the most recent deadlock in the test run, the case from the section “A table that gets in its own way”, Query 2 returned these two results on SQL Server 2022, without input buffer, application and client:

process           victim  session_id  waiting_for                              mode  log_bytes  module  line
processe0fbeeca8  yes     54          KEY: 5:72057594045923328 (40fd182c0dd9)  S     0          adhoc   9
processe0d8f5468          55          KEY: 5:72057594045988864 (1e3e71606df0)  X     244        adhoc   10

lock_id        resource  object_name              index_name         role   process           mode
locke0770e380  keylock   DeadlockLab.dbo.Account  PK_Account         holds  processe0d8f5468  X
locke0770e380  keylock   DeadlockLab.dbo.Account  PK_Account         waits  processe0fbeeca8  S
locke0770e400  keylock   DeadlockLab.dbo.Account  IX_Account_Status  holds  processe0fbeeca8  S
locke0770e400  keylock   DeadlockLab.dbo.Account  IX_Account_Status  waits  processe0d8f5468  X

For each lock, holder and waiter are listed one below the other. The cycle can be read straight off: the reader (session 54) holds IX_Account_Status and waits for PK_Account, the writer (session 55) the other way round. For ad hoc statements, module only says adhoc; the input buffer then shows which statement it was. Query 2 ran without errors on SQL Server 2017, 2019, 2022 and 2025, including with xactlock resources under optimized locking.

Drawing the cycle and forming a hypothesis

With the resource list you can draw the cycle. Write each lock as a box and each process as a circle. An arrow from box to process means “is held by”, an arrow from process to box “waits for”. In the example:

B waits for PK_Inventory, which A holds. A waits for PK_Orders, which B holds.

The picture gives you the smallest explanation the graph supports: two transactions change the same two rows in opposite order and hold the first lock while waiting for the second. Write down what the graph doesn't show as open questions: which earlier statement took the lock that is held? How long was the transaction open? With which parameters did it run? Does the case occur regularly or only with a particular data constellation? The answers are in the code, in the application logs and, for frequency and plans, in Query Store.

Often the code doesn't belong to you but to the vendor of the application. Then the graph, the hypothesis and the open questions together are the evidence the vendor can work with. “These two procedures change inventory and orders in opposite order” is more useful than “we have deadlocks”. In our continuous performance engineering service PSG MX, this is exactly part of the work: back findings with evidence and agree changes with your team and the vendor.

A table that gets in its own way

A deadlock doesn't need two tables. A single table can also get itself into a cycle through its own indexes. The case is surprising because the code contains nothing but a harmless query and a simple update.

The table dbo.Account has a clustered primary key on AccountID and an additional index on Status. A reader searches via this index and fetches the balance, which is stored only in the clustered index, with a key lookup. A writer changes the same accounts via the primary key and sets a new status as it does so. That means after the row in the clustered index it also has to change the entry in IX_Account_Status.

Both access the same two indexes, but in reverse order:

• The reader holds an S lock on the entry in IX_Account_Status and wants to read the row in PK_Account.
• The writer holds an X lock on the row in PK_Account and wants to change the entry in IX_Account_Status.

This is how it looks in the graph, shortened to the resource list, SQL Server 2025 without optimized locking:

<keylock objectname="DeadlockLab.dbo.Account" indexname="PK_Account" mode="X">
  <owner-list><owner id="process…088" mode="X"/></owner-list>   <!-- writer -->
  <waiter-list><waiter id="process…478" mode="S"/></waiter-list>  <!-- reader -->
</keylock>
<keylock objectname="DeadlockLab.dbo.Account" indexname="IX_Account_Status" mode="S">
  <owner-list><owner id="process…478" mode="S"/></owner-list>   <!-- reader -->
  <waiter-list><waiter id="process…088" mode="X"/></waiter-list>  <!-- writer -->
</keylock>

One object, two indexes, one cycle. In the interactive workbench below you can replay the sequence and switch on the two remedies:

In the lab we ran reader and writer in a loop for 15 seconds each:

CREATE INDEX IX_Account_Status ON dbo.Account (Status); -- ReaderSELECT @s = SUM(Balance) FROM dbo.Account WHERE Status = 7;   -- Balance via key lookup -- WriterUPDATE dbo.AccountSET Status = CASE WHEN Status = 7 THEN 8 ELSE 7 END, Balance = Balance + 1WHERE AccountID IN (7, 1007, 2007, 3007);   -- PK_Account first, then IX_Account_Status

CREATE INDEX IX_Account_Status ON dbo.Account (Status)    INCLUDE (Balance)                                     -- no more lookup    WITH (DROP_EXISTING = ON); -- Reader and writer unchanged

ALTER DATABASE DeadlockLab SET READ_COMMITTED_SNAPSHOT ON;   -- reader reads versions -- Index without INCLUDE, reader and writer unchanged

What the numbers show:

• The cycle forms on every version, from SQL Server 2017 to 2025, with 31 to 82 deadlocks in 15 seconds. The victim was the reader in every run. It had written no log, so rolling it back cost nothing.
• The covering index removes it, because the reader no longer touches the clustered index. As a side effect, the reader managed 17 to 42 times as many iterations in seven of eight runs, because it was no longer blocked either. In one run on SQL Server 2019 it was only three times as many; we have not established why.
• RCSI removes it as well, because under read committed snapshot the reader takes no locks and reads the last committed version instead.
• Optimized locking alone does not help here. On SQL Server 2025 with optimized locking but without RCSI there were 80 deadlocks in 15 seconds. The reader still takes locks under read committed.

Which route fits is a trade-off. A covering index resolves this cycle for this query, but makes the index larger, and it has to be maintained whenever the included columns change. It is also visible to every query on the table. How to check that is described in the article on index tuning. RCSI changes the read semantics of the whole database and needs space for row versions in TempDB. It is a decision for the application, not for one query.

When more than two are involved

A cycle can have any number of links. In the lab we chained three and four sessions: each changes its own inventory row, waits, and then wants the row of the next one. The last one wants the row of the first.

On every version this produced a single graph with three or four processes and as many resources. SQL Server picked exactly one victim. That broke the cycle, and the remaining sessions ran through one after the other.

With such graphs the process list alone doesn't get you far, because all participants run the same procedure and wait on the same object. In the lab, all four resources were on PK_Inventory and differed only in their hash. You find the cycle via the resource list: start at the victim, find the lock it waits for, then that lock's holder, then the lock that one waits for, and so on until you are back at the victim. Result 2 of Query 2 gives you the holder and the waiter of each lock in consecutive rows for this.

A cycle with many links usually means the same as one with two: several code paths change the same kind of rows in different order. It just looks more threatening.

Which fix fits this case

Every cycle has several possible fixes. Which one fits depends on how it forms.

The same access order in all code paths

For the cycle in the example, this is the direct fix: cancelling, too, changes the inventory row first and then the order. We ran both code paths 300 times each at the same time, with 10 milliseconds of work between the two statements, on SQL Server 2022 and 2025 in five configurations, once each:

The number of deadlocks with reverse order varied widely from run to run. It is no good for comparing the configurations, but it supports the finding: with the same order, not a single cycle formed. The code paths merely waited for each other, and that cost less time than the deadlocks.

In practice this means: define a fixed order of tables for writing transactions, for example from header to line record or alphabetically, and change several rows of the same table sorted by key.

Keeping transactions short

The shorter a transaction holds its locks, the smaller the window in which another code path can get in between. If user interaction, service calls or expensive computations don't strictly have to be inside the transaction, move them before or after it.

Checking the access path: when a scan closes the cycle

Some cycles are caused not by the order in the code but by the plan. In the lab, session A changes invoice 10 and session B invoice 20. Then A changes all invoices of customer 111 and B all invoices of customer 222. There is no row that both want to change. Still, a deadlock occurred on every version.

The reason: there was no index on CustomerNo. Each session had to scan the whole table and briefly lock every row to check the customer. In doing so, A ran into B's row 20 and B into A's row 10. In the graph this appears as a wait in mode U on someone else's row that was never meant to be touched.

A suitable index resolves this cycle on every version, because each session only touches its own rows. RCSI alone doesn't help here, because the search for the rows to change takes locks under RCSI too. Only on SQL Server 2025 with optimized locking and RCSI does SQL Server evaluate the condition without locking the other row (lock after qualification). According to Microsoft this does not apply to all plans and statements, for example not with locking hints such as UPDLOCK; we only tested this simple UPDATE.

How to derive a suitable index for such a query is described in the article on index tuning. A graph with locks on rows that have nothing to do with each other in business terms is a good reason to look at the plans of the statements involved.

In our analyses, the access profile in PSG QX shows which queries scan a table instead of seeking into it, weighted by CPU time and based on the execution history already in Query Store. QX does not see the deadlock itself, but it does see the access paths, like the scan here, that make such a cycle possible in the first place.

Isolation and hints only with a view of the specific cycle

RCSI, optimized locking and locking hints are not general switches against deadlocks. In the lab, RCSI helped with reading across two indexes, but not with the cycle from reverse order and not with the scan. Optimized locking alone removed none of the three cases. So only change isolation or hints if you know which lock in the graph you are avoiding with it, and if the application tolerates the changed read semantics. If your team wants to make these trade-offs with confidence: concurrency is covered alongside plans and statistics in our performance troubleshooting training for DBAs and technical support.

Why error handling is part of it too

Even after a good fix, some risk remains. That is why Microsoft recommends in its deadlocks guide handling 1205 in the application and retrying the transaction after a short, randomly chosen pause. What matters is what gets retried. We compared the variants under load, 300 bookings and 300 cancellations each in reverse order, on SQL Server 2022 and 2025 in five configurations:

The third variant is the typical mistake. The application gets 1205 on the second UPDATE and sends that UPDATE again. But SQL Server rolled back the first UPDATE together with the whole transaction. Afterwards the booking is on the order but not in the inventory. Five past twelve means: everything since BEGIN TRANSACTION is gone.

For a retry, therefore:

• Retry the whole transaction, from BEGIN TRANSACTION on, with freshly read data.
• Few attempts with a short, randomly varied pause, so that the participants don't run into each other again in the same rhythm.
• Mind side effects outside the database: an email already sent or a call to another system is not rolled back.
• Log retries. A retry hides the error, not the cause. If the number of retries rises, that is a signal.

Recognizing deadlocks with optimized locking

From SQL Server 2025 on, a database can use optimized locking. It requires accelerated database recovery (ADR). A changed row then no longer stays locked until the end of the transaction. Instead, the transaction holds a lock on its own transaction ID, and anyone who wants to change the row waits for that.

In Azure SQL Database, optimized locking is always on according to Microsoft, so there you see the new form without doing anything. It does not prevent the cycle from reverse order. In the lab it formed just the same with optimized locking, with and without RCSI. The graph looks different, though:

Three differences from the classic graph:

• The resource is called xactlock instead of keylock. It means the transaction that changed the row. Which row that is appears below it in UnderlyingResource.
• The waiter requests S, not X. It waits for the other transaction to end.
• The waitresource starts with XACT: and then names the key.

Mixed graphs occur as well. When reading across two indexes, under optimized locking without RCSI an xactlock on PK_Account sat next to a classic keylock on IX_Account_Status. Query 2 reads both forms: for xactlock it takes object and index from UnderlyingResource. Incidentally, under optimized locking the 1205 message in the lab had state 72 instead of 51; the wording stayed the same.

Frequently asked questions

Is the deadlock victim the cause? No. The victim is the execution SQL Server chose to break the cycle, at equal priority the one that is cheaper to roll back. For the cause, look at all participants, their locks and the transactions behind them.

Can I reconstruct a deadlock from Query Store? Not the cycle. Query Store provides queries, plans and aggregated runtime and wait data. In an earlier PSG lab, a deadlock victim appeared there as an execution of type Exception, and lock waits are visible as a category. Who blocked whom is only in the deadlock graph. For the plans of the statements involved, though, Query Store is a good complement.

Should the application simply resend the statement after 1205? Not the statement, the whole transaction. If the application only repeats the last statement, everything the transaction had changed before is missing. In the lab, inventory and orders no longer matched afterwards.

Does NOLOCK help against deadlocks? NOLOCK changes what a reader sees: it can read uncommitted and duplicate rows or miss rows. It doesn't help against cycles between writers. If a reader is involved, as with reading across two indexes, a covering index or RCSI are the clean routes.

Do RCSI or optimized locking prevent deadlocks? Some, not all. In the lab, RCSI removed the cycle between reader and writer, and optimized locking with RCSI additionally the scan case. None of the switches prevented the cycle from reverse access order.

Why can't I find a known deadlock any more? Because system_health keeps only a limited amount of events and overwrites older ones, because a new deadlock only appears in the file after up to two minutes, or, on SQL Server 2017 to 2022, because a time filter directly on timestamp_utc silently drops the rows.

When the graph doesn't explain the transaction yet

The graph shows the moment the cycle was closed. How the transactions got there, which statements took the locks that were held and why the plan touched someone else's row is in the code and in the plans. For a joint investigation, it helps to have:

• the unchanged graph as .xdl or XML, with time, database and version,
• the procedures or statements involved,
• the actual execution plans of the waiting statements,
• the frequency: once, daily at the same time, or constantly.

With this information we start a joint analysis right at the cycle instead of collecting data first.

Sources

• Microsoft: Deadlocks guide
• Microsoft: Use the system_health session
• Microsoft: sys.fn_xe_file_target_read_file
• Microsoft: SET DEADLOCK_PRIORITY
• Microsoft: Optimized locking
• Microsoft: Analyze and prevent deadlocks in Azure SQL Database

Read on: Find blocking and timeouts · Lock escalation from a SELECT · SQL Server index tuning · SQL Server performance tuning