Repeatable read possible deadlock in isolation level

In today's article I want to talk about the 2 types of deadlocks that can be caused when you run a transaction in the repeatable read isolation level (Transaction isolation levels repeatable Read) . When you use the repeatable read (REPEATABLE Read) isolation level to set up your transaction, SQL Server takes a shared lock on the read data hold (shared Locks) until the end of the transaction (commit or Rollbak). Then when you try to modify the read data (via the UPDATE statement), it heroic spirit different types of deadlocks if the same transaction runs multiple times concurrently.

cyclic deadlock (cycle Deadlock)

a cyclic deadlock is one of the most common deadlocks-you access resources in a different order (for example, different tables) and finally each query waits for another. The REPEATABLE read isolation level is used, but when you read and write only one table with multiple transactions, it is also possible to cause a circular deadlock. Let's take a look at the T-SQL code for the 1th transaction:

1 SET TRANSACTION Isolation  Level Repeatable READ2 GO3 4 BEGIN TRANSACTION5 6 SELECT *  fromPerson.person7 WHEREModifiedDate= '20030208'8 9 UPDATEPerson.personTen SETFirstName= '...' One WHEREModifiedDate= '20030208' A  - SELECT *  fromPerson.person - WHEREModifiedDate= '20030209' the  - UPDATEPerson.person - SETFirstName= '...' - WHEREModifiedDate= '20030209' +  - ROLLBACK + GO

This is the T-SQL code for the 2nd transaction:

1 SET TRANSACTION Isolation  Level Repeatable READ2 GO3 4 BEGIN TRANSACTION5 6 SELECT *  fromPerson.person7 WHEREModifiedDate= '20030209'8 9 UPDATEPerson.personTen SETFirstName= '...' One WHEREModifiedDate= '20030209' A  - SELECT *  fromPerson.person - WHEREModifiedDate= '20030208' the  - UPDATEPerson.person - SETFirstName= '...' - WHEREModifiedDate= '20030208' +  - ROLLBACK + GO

As can be seen from 2 code, 2 data ranges are read, and finally are updated. If 2 transactions are performed at the same time, a cyclic deadlock occurs because the data range is accessed in a different order.

When SQL Server starts to execute an UPDATE statement, the required update lock (update lock) cannot be fetched because the update lock (updated lock) is incompatible with a shared lock that has been granted from a different session. The last 2 Update statements will wait--there is a classic loop lock on a table because the shared lock is already available in the other session. In this case you have to rewrite your code to get this particular lock resolved--Accessing your data range in the same order.

read/write/update deadlock (read/update Deadlock)

A class 2nd deadlock with REPEATABLE read isolation levels occurs, and if you read the data, it is intentionally updated later. Let's take a look at the T-SQL code for 1 simple transactions:

1 SET TRANSACTION Isolation  Level Repeatable READ2 GO3 4 BEGIN TRANSACTION5 6 SELECT *  fromPerson.person7 WHEREModifiedDate= '20030208'8 9 UPDATEPerson.personTen SETFirstName= '...' One WHEREModifiedDate= '20030208' A  - ROLLBACK - GO

To cause this kind of deadlock, you simply run the transaction through multiple sessions. As you can see from the code, you don't even need to access different data ranges. Let's explain what's going on here. When this transaction executes concurrently across multiple sessions, all sessions receive a shared lock on the data being read.

Because you control the shared lock in repeatable reads until the end of the transaction (commit or rollback), the following update statements do not obtain the required renewal Lock (update Locks) because they have been shared by a different session Locks) is blocked. Dead lock!

Here the deadlock can be obtained by using the hint in the SELECT statement to get an update lock in advance (update lock).

1 SET TRANSACTION Isolation  Level Repeatable READ2 GO3 4 BEGIN TRANSACTION5 6 SELECT *  fromPerson.person with(UPDLOCK)7 WHEREModifiedDate= '20030208'8 9 UPDATEPerson.personTen SETFirstName= '...' One WHEREModifiedDate= '20030208' A  - ROLLBACK - GO

Therefore, at the beginning, only one SELECT statement can obtain the required update lock (update Locks), which is incompatible with its own update lock, which continues to use the UPDATE statement, and finally releases the required lock. Then the 2nd transaction will continue with its select and UPDATE statements.

Here you will use the same technique that SQL Server uses internally in the update execution plan: At the REPEATABLE read isolation level, when the data you read is intentionally updated later, you need to obtain an update lock to block this type of deadlock during the read phase.

Summary

As you can see from this article, if you use repeatable read isolation levels It is easy to cause various types of deadlocks. So when you write a transaction at this particular isolation level, you have to be very careful.

Thanks for your attention!

Repeatable read possible deadlock in isolation level