I love to write about Deadlocks, because there are so many different ways and situations in which you can end up with Deadlocks if you don’t think carefully when writing your code. In today’s blog post I want to show you a very simple Deadlock that can occur when you want to write and read records from the same table in a different order.

A Write/Read Deadlock

The following listing shows a simple table definition statement, where I also insert 2 records.

-- Create a new table
CREATE TABLE Foo
(
	Col1 INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	Col2 INT,
	Col3 INT
)
GO

-- Insert 2 records
INSERT INTO Foo VALUES (1, 1), (2, 2)
GO

SELECT * FROM Foo
GO

Imagine now the following situation: you have 2 queries, which access that table for writing and reading, but you access the records in a different order. The first transaction performs the following:

• UPDATE the 1st record
• SELECT the 2nd record

And a second transaction accesses both records in the opposite order:

• UPDATE the 2nd record
• SELECT the 1st record

The following code shows you both transactions:

-- UPDATE 1st record
-- SELECT 2nd record
BEGIN TRANSACTION

UPDATE Foo SET Col3 = 3
WHERE Col1 = 1

SELECT * FROM Foo
WHERE Col1 = 2

ROLLBACK
GO

-- UPDATE 2nd record
-- SELECT 1st record
BEGIN TRANSACTION

UPDATE Foo SET Col3 = 3
WHERE Col1 = 2

SELECT * FROM Foo
WHERE Col1 = 1

ROLLBACK
GO

If the timing is right (wrong), it’s quite easy to trigger a Deadlock here, because both transactions acquire an Exclusive Lock on the 1st and 2nd record, and then they are not able to acquire the Shared Lock for reading on the other record – Deadlock!

How can you solve that Deadlock? There are many ways:

• Access the records in the same order (1st record, 2nd record) across all of your transactions
• Perform the SELECT statement outside of the transaction
• Enable Read Committed Snapshot Isolation to get rid of the Shared Locks during SELECT statements

Summary

In today’s blog post I wanted to show you how easy it is to trigger a Deadlock in SQL Server when you access records in a table for writing and reading in a different order across transactions. When you write your queries, you always have to think very carefully and implement your queries accordingly. There is always someone else who can block you.

Thanks for your time,

-Klaus