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
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,