READ READING transaction isolation level causes deadlocks

Question

Part of my application updates the table according to business logic after opening a connection at the transaction isolation level REPEATABLE READ. In a rare scenario, if this operation matches another part of the application, which opens another connection and tries to reset the same entry by default. I get the following error

Msg 1205, Level 13, State 45, Line 7 Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 

I think I can repeat the problem using the following example.

one.

 create table Accounts ( id int identity(1,1), Name varchar(50), Amount decimal ) 

2.

 insert into Accounts (Name,Amount) values ('ABC',5000) insert into Accounts (Name,Amount) values ('WXY',4000) insert into Accounts (Name,Amount) values ('XYZ',4500) 

3.

Run a long transaction with isolation level as REPEATABLE READ

 Set transaction isolation level REPEATABLE READ begin tran declare @var int select @var=amount from Accounts where id=1 waitfor delay '0:0:10' if @var > 4000 update accounts set amount = amount -100; Commit 

4.

While step 3 above is still performed. Run another transaction in a different connection

 Begin tran update accounts set Amount = 5000 where id = 1 commit tran 

The transaction that you started in step 3 will eventually complete, but the one that you started in step 4 will fail with the following error message.

 Msg 1205, Level 13, State 45, Line 7 Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 

What are my options that ultimately allow me to complete the transaction in step 4. The idea is to be able to reset the default value, in which case all other operations should be redefined. I do not see any problems if both transactions are not parallel.

Answer 1

The idea is to reset write the default value

In what order do you want to use updates? Do you want "reset" to always pass? Then you need to reset strictly after the update is completed in step 3. In addition, the reset update must use a higher lock mode to avoid a deadlock:

 update accounts WITH (XLOCK) set Amount = 5000 where id = 1 

Thus, reset will wait until another transaction completes first, because the other trance has S-lock.

Alternatively, step 3 of the hub acquires a U-lock or X-lock.

Answer 2

This is a deadlock schedule:

Explanations:

t = SPID 56 ( SELECT @var ... WHERE id=1 ) takes S lock (shared) for all rows with id = 1

t + 1 = SPID 59 ( UPDATE ... WHERE id=1 ) takes U lock (update lock) in the first row with id = 1 (this table is a bunch, and there is no order for the rows → this is the reason for those ".. . "). This is possible because the locks S and U are compatible. He then checks the predicate ( WHERE id=1 ), the result of this evaluation is TRUE , and then he tries to convert the U lock to X lock to update the current line ( SET Amount = 5000 ), but X lock and S lock (SPID 56) are incompatible. Thus, SPID 59 starts waiting for SPID 56 to release its S lock .

t + 2 = SPID 56 ( UPDATE ... SET Amount = Amount - 100 ) has S lock on id = 1 (I think there is an error in the deadlock diagram because it shows that SPID 56 has U lock : see Owner mode: U ), and this is trying to convert this S lock to U lock . But SPID 59 already has a U lock on this line (id = 1). Since the two U lock(s) are incompatible, you get a good dead end.

So, for the first line (id = 1, RowId = "RID: 7:1:488:0" ;: :0 means slot 0 inside page 488 in the file id1; 7 = db id):

• SPID 56 accepts S lock on line with id = 1
• SPID 59 takes U lock , and then tries to convert it to X lock (it cannot), and it waits,
• SPID 56 is trying to convert this S lock to U lock
• Since U locks are incompatible -> dead end.

Solution: you just need the index in the Id column (which has the IDENTITY property). In this case, I added the CLUSTERED index:

 CREATE UNIQUE CLUSTERED INDEX PK_Accounts_Id ON dbo.Accounts(Id); GO 

Answer 3

You can set transaction deadlock priority in setp 4 to be higher. For more details see http://technet.microsoft.com/en-us/library/ms186736.aspx