CODEWITHSUNDEEP
sql-servertransactions
Ron5504
Ron5504

Reputation: 2846

Transaction isolation level REPEATABLE READ causes deadlocks

A part of my application updates a table as per business logic after opening a connection on transaction isolation level REPEATABLE READ. In a rare scenario, If this operation coincides with another part of the application which opens a different connection and tries to reset the same record to its default value. I get 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 am able to re-produce the issue using following example.

1.

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.

Start 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 being executed. Start another transaction on a different connection

Begin tran

update accounts
set Amount = 5000
where id = 1

commit tran

Transaction started in Step 3 would eventually complete but the one started in Step 4 would fail with 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 to be able to eventually run transaction in step 4. The idea is to be able to reset the record to a default value and anything being performed on other transactions should be overridden in this case. I don't see any issue if both the transactions are not concurrent.

Upvotes: 5

Views: 3598

Answers (2)

usr
usr

Reputation: 171178

The idea is to be able to reset the record to a default value

In what order do you want the updates applied? Do you want the "reset" to always come through? Then you need to perform the reset strictly after the update in step 3 has completed. Also, the reset update should use a higher lock mode to avoid the deadlock:

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

That way the reset will wait for the other transaction to finish first because the other tran has an S-lock.

Alternatively, habe step 3 acquire an U-lock or X-lock.

Upvotes: 4

Mangoose
Mangoose

Reputation: 922

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

Upvotes: 0

Related Questions