In today’s blog posting I want to talk about 2 kinds of deadlocks that can occur when you run transactions in the Transaction Isolation Level Repeatable Read. When you set your transaction to use the isolation level Repeatable Read, SQL Server holds acquired Shared Locks for reading data until the end of the transaction (COMMIT or ROLLBACK). When you afterwards try to change the read data (through an UPDATE statement), it can cause different types of deadlocks if the same transaction runs concurrently multiple times. Let’s have a more detailed look at 2 different kinds of such deadlocks.
Cycle Deadlock
A Cycle Deadlock is one of the most common deadlocks – you just access resources (like various tables) in a different order, and at the end every query waits for another one. With the isolation level Repeatable Read it is even possible to cause a cycle deadlock when you just work with one table across multiple transactions. Let’s have a look at the T-SQL code of the first transaction.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRANSACTION SELECT * FROM Person.Person WHERE ModifiedDate = '20030208' UPDATE Person.Person SET FirstName = '...' WHERE ModifiedDate = '20030208' SELECT * FROM Person.Person WHERE ModifiedDate = '20030209' UPDATE Person.Person SET FirstName = '...' WHERE ModifiedDate = '20030209' ROLLBACK GO
And here is the T-SQL code of the second transaction.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRANSACTION SELECT * FROM Person.Person WHERE ModifiedDate = '20030209' UPDATE Person.Person SET FirstName = '...' WHERE ModifiedDate = '20030209' SELECT * FROM Person.Person WHERE ModifiedDate = '20030208' UPDATE Person.Person SET FirstName = '...' WHERE ModifiedDate = '20030208' ROLLBACK GO
As you can see from both listings, 2 data ranges are read and finally updated. If both transactions run at the same time, a Cycle Deadlock can occur, because the data ranges are accessed in the wrong order.
When SQL Server starts to run the UPDATE statement, the necessary Update Lock (U) can’t be acquired, because the Update Lock isn’t compatible with the already granted Shared Lock from the different session. Finally both UPDATE statements are waiting because of the Shared Lock acquired in the other session – a classical Cycle Deadlock just with one table! In this case you have to rewrite your code to get resolve this specific deadlock – just access your data ranges in the same order.
Read/Update Deadlock
A second type of deadlock can occur with the isolation level Repeatable Read if you read data with the intention to update it later. Let’s have a look at the T-SQL code of a simple transaction.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRANSACTION SELECT * FROM Person.Person WHERE ModifiedDate = '20030208' UPDATE Person.Person SET FirstName = '...' WHERE ModifiedDate = '20030208' ROLLBACK GO
To cause this type of deadlock you just need to run the transaction across multiple sessions. You even don’t need to access different data ranges as you can see from the code. Let’s try to explain what happens here. When this transaction runs across multiple sessions concurrently, all sessions can acquire the Shared Locks for reading the data.
Because you hold the Shared Locks until the end of the transaction (COMMIT or ROLLBACK) in Repeatable Read, the following UPDATE statement can’t acquire the necessary Update Locks, because they are already blocked by the Shared Locks acquired in the different session. Deadlock!
This type of deadlock can be avoided by using a hint in the SELECT statement to acquire an Update Lock in advance.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRANSACTION SELECT * FROM Person.Person WITH (UPDLOCK) WHERE ModifiedDate = '20030208' UPDATE Person.Person SET FirstName = '...' WHERE ModifiedDate = '20030208' ROLLBACK GO
Therefore just one SELECT statement can acquire the necessary Update Locks at the beginning (the Update Lock is not compatible with the Update Lock itself), continue with the UPDATE statement, and finally release the acquired locks. Afterwards the second transaction can continue with its SELECT and UPDATE statement.
You have to use the same technique here that SQL Server uses internally in an UPDATE execution plan: when you read data with the intention to update it later, you have to acquire an Update Lock during the reading phase to prevent this type of deadlock in the isolation level Repeatable Read.
Summary
As you have seen in this blog posting, it is very easy to cause various types of deadlocks if you use the isolation level Repeatable Read. Therefore you have to be very careful when you write transactions in this specific isolation level.
Thanks for your time,
-Klaus
