sql-server - 事务隔离级别 REPEATABLE READ 导致死锁

Question

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

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.

我认为我能够使用以下示例重现该问题。

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 的长事务

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仍在执行时。在不同的连接上启动另一个事务

Begin tran

update accounts
set Amount = 5000
where id = 1

commit tran

第 3 步中启动的事务最终会完成，但第 4 步中启动的事务将失败并显示以下错误消息。

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 中运行事务。这个想法是能够将记录重置为默认值，并且在这种情况下应该覆盖对其他事务执行的任何操作。如果两个事务不并发，我没有发现任何问题。

Answer 1

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

您希望以什么顺序应用更新？您希望“重置”始终成功吗？那么您需要在步骤3的更新完成后严格执行重置。另外，重置更新应该使用更高的锁定模式以避免死锁:

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

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

或者，执行步骤 3 获取 U 锁或 X 锁。