Deadlocks and row versioning (snapshot isolation)

Blocking ends when the blocker commits. A deadlock never ends by itself: two sessions each hold a lock the other one needs, and each waits for the other. SQL Server has to break the circle by cancelling one of them.

Making a deadlock

Two sessions both take one hammer and one drill out of stock, but in a different order. Each starts with its first product:

1BEGIN TRANSACTION;2UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 1;   -- hammer

1BEGIN TRANSACTION;2UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 2;   -- drill

Now each asks for the row the other has. Session 1 waits for Session 2’s drill. That alone is ordinary blocking:

1UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 2;   -- waits

Then Session 2 asks for Session 1’s hammer. Now each is waiting for the other:

1UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 1;

Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Within about five seconds SQL Server’s lock monitor spots the cycle. It picks a victim, rolls back the victim’s whole transaction and sends it error 1205. That frees the victim’s locks, so the other session carries on: Session 1’s UPDATE finished and its transaction is still open. Session 2 has no transaction left:

1SELECT @@TRANCOUNT AS OpenTransactions;2COMMIT;

OpenTransactions
----------------
               1

1SELECT @@TRANCOUNT AS OpenTransactions;

OpenTransactions
----------------
               0

SQL Server normally picks as victim the transaction that is cheapest to roll back (the one that has written the least to the log). You can change that with SET DEADLOCK_PRIORITY LOW (or NORMAL, HIGH, or a number from -10 to 10): the session with the lower priority is chosen first. Give it to background jobs that can easily run again, so the till never loses a sale.

Retry the victim

The error tells you what to do: “Rerun the transaction.” A victim has been rolled back completely, so running it again is safe. Catch error 1205 and try a few more times. Here Session 2 runs the same two updates in a retry loop with low priority, while Session 1 creates the conflict again:

1SET DEADLOCK_PRIORITY LOW;2SET XACT_ABORT ON;3DECLARE @Attempt int = 1;4 5WHILE 1 = 16BEGIN7    BEGIN TRY8        BEGIN TRANSACTION;9        UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 2;10        UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 1;11        COMMIT;12        PRINT CONCAT('Attempt ', @Attempt, ' committed');13        BREAK;14    END TRY15    BEGIN CATCH16        IF @@TRANCOUNT > 0 ROLLBACK;17        IF ERROR_NUMBER() = 1205 AND @Attempt < 318        BEGIN19            PRINT CONCAT('Attempt ', @Attempt, ' was a deadlock victim; trying again');20            SET @Attempt += 1;21            WAITFOR DELAY '00:00:00.100';22        END23        ELSE THROW;24    END CATCH;25END;

Attempt 1 was a deadlock victim; trying again
Attempt 2 committed

Both sessions’ work was done in the end: the hammer went from 40 to 38 and the drill from 12 to 10. In an application, the retry usually lives in the data-access code (C#, for example), which catches error 1205 and runs the whole transaction again.

Finding out what deadlocked

SQL Server records every deadlock in the built-in system_health Extended Events session, including a full report of both processes, the statements they ran and the locks involved. This reads the newest report:

1WITH Reports AS (2    SELECT CAST(event_data AS xml) AS Report3    FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)4    WHERE object_name = 'xml_deadlock_report'5)6SELECT TOP (1)7       Report.value('(event/@timestamp)[1]', 'datetime2(0)') AS HappenedAt,8       Report.value('(event/data/value/deadlock/victim-list/victimProcess/@id)[1]', 'varchar(20)') AS Victim,9       Report.value('count(event/data/value/deadlock/process-list/process)', 'int') AS Processes10FROM Reports11ORDER BY HappenedAt DESC;

HappenedAt           Victim              Processes
-------------------  ------------------  ---------
2026-11-09 20:08:04  process27a82403088          2

The time is in UTC. Select the whole Report column instead and SSMS shows the XML; save it as a file ending in .xdl and SSMS draws the deadlock graph: the processes as ovals (the victim crossed out), the locked rows as boxes, and arrows for “holds” and “waits for”.

Preventing deadlocks

• Touch rows and tables in the same order everywhere. If both sessions above had updated the hammer first, Session 2 would simply have waited for Session 1 to commit and then run; no cycle is possible.
• Keep transactions short. Fewer locks are held for less time. Never wait for user input inside a transaction.
• Do not read and then update under REPEATABLE READ or SERIALIZABLE. Two sessions holding S locks on the same row, both wanting X, deadlock every time. Read with UPDLOCK, or do it in one UPDATE.
• Index the columns you search on. An UPDATE that has to scan a whole table takes and checks far more locks than one that seeks to its rows.
• Use row versioning for readers (below): readers stop taking S locks, so reader–writer deadlocks disappear.

The REPEATABLE READ trap from the last lesson is worth seeing. Two sessions run this at the same time:

1SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;2BEGIN TRANSACTION;3DECLARE @Stock int = (SELECT Stock FROM dbo.Products WHERE ProductId = 2);4WAITFOR DELAY '00:00:02';5UPDATE dbo.Products SET Stock = @Stock - 1 WHERE ProductId = 2;6COMMIT;

Msg 1205, Level 13, State 51, Line 5
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Only one row, and still a deadlock: each session holds an S lock on the drill and wants an X lock, which the other’s S lock blocks. One session got error 1205 at line 5, the UPDATE.

Row versioning: readers that do not block

Locking makes readers and writers wait for each other. Row versioning takes another approach: when a row is changed, SQL Server keeps a copy of the previous committed version in tempdb (the version store). A reader that would otherwise have to wait reads that copy instead.

It has two forms. The first, READ COMMITTED SNAPSHOT (RCSI), changes how the default READ COMMITTED level works for every session in the database. You switch it on once:

1ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;2 3SELECT is_read_committed_snapshot_on4FROM sys.databases5WHERE database_id = DB_ID();

Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

is_read_committed_snapshot_on
-----------------------------
1

Now repeat the blocking example from the last lesson. Session 1 changes the drill’s price and leaves the transaction open:

1BEGIN TRANSACTION;2UPDATE dbo.Products SET Price = 95.00 WHERE ProductId = 2;

1SELECT Name, Price FROM dbo.Products WHERE ProductId = 2;

Name            Price
--------------  -----
Cordless drill  89.00

No waiting. Session 2 gets the last committed price, 89.00, at once. Not a dirty read: 95.00 is not committed yet. After Session 1 commits, the same SELECT returns 95.00.

Under RCSI each statement sees the data as it was committed when that statement started. So a transaction can still read a row twice and get two values (here Session 2 took 10 off between Session 1’s two reads, and Session 1 saw 12, then 2). Writers also still block writers: a second UPDATE of the same row waits, exactly as before (LCK_M_X).

SNAPSHOT isolation

The second form is a separate isolation level, SNAPSHOT. A snapshot transaction sees the database as it was committed when the transaction started, for every statement, however long it runs. Allow it once for the database, then ask for it per session:

1ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;2 3SELECT snapshot_isolation_state_desc4FROM sys.databases5WHERE database_id = DB_ID();

snapshot_isolation_state_desc
-----------------------------
ON

1SET TRANSACTION ISOLATION LEVEL SNAPSHOT;2BEGIN TRANSACTION;3SELECT Name, Stock FROM dbo.Products WHERE ProductId = 2;

Name            Stock
--------------  -----
Cordless drill     12

1UPDATE dbo.Products SET Stock = Stock - 10 WHERE ProductId = 2;2SELECT Name, Stock FROM dbo.Products WHERE ProductId = 2;

Name            Stock
--------------  -----
Cordless drill      2

Session 2 committed 2. Session 1 still sees 12, the value when its transaction began: a repeatable read, with no locks held. But what if Session 1 now tries to change the row?

1SELECT Name, Stock FROM dbo.Products WHERE ProductId = 2;2UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 2;

Name            Stock
--------------  -----
Cordless drill     12

Msg 3960, Level 16, State 2, Line 2
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Products' directly or indirectly in database 'BuildMart' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Session 1 would be changing a row on the basis of a version that is out of date, so SQL Server refuses: an update conflict, error 3960, and Session 1’s transaction is rolled back. Snapshot isolation never loses an update silently, but code that writes under SNAPSHOT must be ready to retry, just like a deadlock victim.

1CREATE OR ALTER PROCEDURE dbo.MoveStock2    @FromId int, @ToId int, @Qty int3AS4BEGIN5    SET NOCOUNT ON;6    SET XACT_ABORT ON;7 8    UPDATE dbo.Products9    SET Stock = Stock + CASE WHEN ProductId = @ToId THEN @Qty ELSE -@Qty END10    WHERE ProductId IN (@FromId, @ToId);11END;12GO13EXEC dbo.MoveStock @FromId = 3, @ToId = 1, @Qty = 5;14 15SELECT ProductId, Name, Stock FROM dbo.Products WHERE ProductId IN (1, 3);

ProductId  Name              Stock
---------  ----------------  -----
        1  Claw hammer          45
        3  Tape measure 8 m     60