Therefore you have to be very careful when you write transactions in this specific isolation level.Blocking and deadlocking are not things you typically want to do deliberately. SummaryĪs 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. 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. Afterwards the second transaction can continue with its SELECT and UPDATE statement. 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. SELECT * FROM Person.Person WITH (UPDLOCK) This type of deadlock can be avoided by using a hint in the SELECT statement to acquire an Update Lock in advance. When this transaction runs across multiple sessions concurrently, all sessions can acquire the Shared Locks for reading the data.īecause 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. You even don’t need to access different data ranges as you can see from the code. To cause this type of deadlock you just need to run the transaction across multiple sessions. Let’s have a look at the T-SQL code of a simple transaction. Read/Update DeadlockĪ second type of deadlock can occur with the isolation level Repeatable Read if you read data with the intention to update it later. 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. 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. If both transactions run at the same time, a Cycle Deadlock can occur, because the data ranges are accessed in the wrong order. SET TRANSACTION ISOLATION LEVEL REPEATABLE READĪnd here is the T-SQL code of the second transaction.Īs you can see from both listings, 2 data ranges are read and finally updated. Let’s have a look at the T-SQL code of the first transaction. 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. Cycle DeadlockĪ 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. Let’s have a more detailed look at 2 different kinds of such deadlocks. 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. 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). 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |