事务隔离级别REPEATABLE READ导致死锁。

Question

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

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.

以可重复读的隔离级别启动长事务。

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.

在执行步骤3的同时，在另一个连接上启动另一个事务。

Begin tran

update accounts
set Amount = 5000
where id = 1

commit tran

在第三步开始的交易最终会完成，但在第四步开始的交易将会失败，并且出现以下错误信息。

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步最终运行事务呢？我的想法是将记录重置为默认值，并且在这种情况下执行的任何其他事务都应该被覆盖。如果两个事务不同时进行，那么我就看不到任何问题。

- Ron5504

---

那么 Account 表是一个没有任何非聚集索引的堆表吗？ - Bogdan Sahlean

对于这个重新处理的示例，它是一个堆，但我必须检查实际的表格。这会有影响吗？ - Ron5504

当然。每个索引都很重要。请在账户表中添加包含所有索引的列表。 - Bogdan Sahlean

谢谢@BogdanSahlean，让我检查一下问题表中的索引。 - Ron5504

Answer 1

我们的目标是能够将记录重置为默认值。

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

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

这样重置将等待其他交易完成，因为其他交易有S锁。

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

- usr

---

谢谢，这正是我想要的。我尝试了你的建议，它似乎按照要求工作。我不确定 XLOCK 在这里的作用。我阅读了文档 http://technet.microsoft.com/en-us/library/ms187373.aspx ，但无法与此情况联系起来。 - Ron5504

XLOCK让查询获取独占锁，而不是U锁。您不希望锁与第3步兼容，以便第4步必须等待第3步完成。 - usr

刚刚意识到另一件事，如果仅将第3步的事务隔离级别更改为“SERIALIZABLE”，则会授予X锁（如sys.dm_tran_locks中所示），而第4步事务则等待IX请求模式。这样，第3步完成后，第4步才会继续进行。 - Ron5504

你在第三步看到的 X-LOCK 只能来自于（已完成的）更新语句，因为 Serializable 不会导致原本只有 S-locking 的情况下出现 X-locking。Serializable 实际上只会导致更多的 S-locks，并使这些锁定时间更长。顺便说一下，在你的示例中添加一个基于 id 的聚集索引，以便可以使用行锁定。现在你可能会看到来自表锁定的人为现象（该表是必须始终扫描的堆）。 - usr

Answer 2

您可以在第4步中设置事务的死锁优先级更高。有关详细信息，请参阅http://technet.microsoft.com/en-us/library/ms186736.aspx。

- Mangoose

---

这将使第4步成功，但会终止其他转换。 - usr