如何解决SQL Server死锁 - 当改变顺序和缩短查询已经无效时？

Question

我有两个假想问题:

UPDATE BankAccounts SET HomePhone = '[phone]' 
WHERE AccountNumber = 14400000619

并且

SELECT * FROM BankAccounts 
WHERE HomePhone = '555-1212'

在一个没有额外索引的假设表格上：

CREATE TABLE BankAccounts 
( 
   AccountNumber bigint NOT NULL PRIMARY KEY CLUSTERED,
   FirstName nvarchar(50) NOT NULL,
   MiddleName nvarchar(50) NULL,
   LastName nvarchar(50) NOT NULL,
   HomePhone varchar(50) NULL,
   IsClosed tinyint DEFAULT 0
)

一切都会变得很好。如果我在HomePhone上添加索引：

CREATE INDEX IX_BankAccounts_HomePhone ON BankAccounts 
( HomePhone)

现在我的SELECT语句可能成为死锁的受害者:

事务（进程ID 169）与另一个进程在锁资源上发生死锁，并被选择为死锁受害者。重新运行该事务。

一般的建议是：

• 以相同的顺序访问表
• 尽可能缩短事务时间

但在这种情况下：

• 我正在以相同的顺序访问（一个）表（1选1等于1）
• 事务是一个单一的语句；我无法再缩短了

消除这样的死锁的长期解决方案是什么？

我正在考虑将我的事务隔离级别更改为READ UNCOMMITTED（即取消完整性），但由于我实际上正在处理一个金融系统，我不愿意允许客户两次提取他的全部余额。

我能找到的唯一其他解决方案来自KB Article 83252:

SQL Server技术公告 - 如何解决死锁

……无法避免死锁。这就是为什么前端应用程序应该设计来处理死锁。

在一个设计良好的应用程序中，前端应用程序应该捕获1205错误，重新连接到SQL Server，然后重新提交事务。

我想这是在说："无法胜利，不要尝试" 还有其他的解决方案吗？

- Ian Boyd

Answer 1

如果您将SELECT *替换为列列表（或考虑所有列），然后将它们作为INCLUDEd列添加到索引中，那么 SELECT 查询就不需要查询聚集索引来完成。这样， SELECT 始终可以完全运行。

如果您不想这样做，那么与其更改整个隔离级别，我会考虑在 SELECT 上使用锁定提示是否合适。适当的提示是 NOLOCK 还是 READPAST ，这是您需要仔细考虑的事情（在理想情况下，将有一种方法可以指定 READPAST 但仍可获得有关实际发生的任何跳过行的信息）。

当然，也可以考虑 SNAPSHOT 隔离（如果没有锁定，就没有死锁）。

---

（一个喜欢受罚的人也可以考虑在 SELECT 语句上使用TABLOCKX。它将防止死锁并确保在存在行的情况下读取一行，但并发性会付出巨大的代价）。

- Damien_The_Unbeliever

---

在大型仓储应用程序中，我通过串行化有问题的操作来“解决”一些严重的死锁问题。其中每个操作都尝试锁定相同的单个记录。我想这相当于您的TABLOCKX？ - Vincent Vancalbergh

@VincentVancalbergh - 是的，这实际上是一种请求序列化的本地方式。 - Damien_The_Unbeliever

Answer 2

不能赢，不要尝试

没错。您可能正在看到一个读写死锁（如果没有死锁图，那只是我们的猜测）。反向访问之所以发生，恰恰是因为您正在做正确的事情（即您确实拥有正确的索引）。即使您访问不同的键（查找一个电话号码，更新另一个电话号码），由于哈希冲突，您仍然在玩概率游戏。

您可以接受死锁或采用快照隔离模型。

- Remus Rusanu