事务隔离级别 REPEATABLE READ 导致死锁 [英] Transaction isolation level REPEATABLE READ causes deadlocks

问题描述

在事务隔离级别 REPEATABLE READ 上打开连接后，我的应用程序的一部分根据业务逻辑更新表.在极少数情况下，如果此操作与应用程序的另一部分同时打开不同的连接并尝试将同一记录重置为其默认值.我收到以下错误

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.

启动一个隔离级别为 REPEATABLE READ 的长事务

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.

虽然上面的 Step.3 仍在执行中.在不同的连接上开始另一个事务

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

在步骤 3 中开始的事务最终会完成，但在步骤 4 中开始的事务将失败并显示以下错误消息.

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.

我有哪些选项可以最终在第 4 步中运行事务.这个想法是能够将记录重置为默认值，并且在这种情况下应该覆盖对其他事务执行的任何操作.如果两个事务不是并发的，我看不到任何问题.

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.

推荐答案

这个想法是能够将记录重置为默认值

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

您希望以什么顺序应用更新?您是否希望重置"始终通过?那么您需要在步骤 3 中的更新完成后严格执行重置.另外，重置更新应该使用更高的锁模式来避免死锁:

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

这样重置将等待另一个事务先完成，因为另一个事务有一个 S 锁.

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

或者，第 3 步获取 U 型锁或 X 型锁.

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

这篇关于事务隔离级别 REPEATABLE READ 导致死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！