具有非聚集索引的Server 2005死锁

Question

有人能帮我解决Server 2005中的死锁吗？

对于一个简单的测试，我有一个表"Book“，它有一个主键(id)和一个列名。此主键的默认索引是非群集。

当两个会话同时运行时，会发生死锁。活动监视器显示第一个会话“//步骤1”用X锁锁定行(rid锁)。第二个会话保持U行锁和键U锁。死锁图显示第一个会话的“//step2 2”需要键U锁。

如果索引是聚集，则在这种情况下不存在死锁。“//步骤1”将同时保持行和键锁，因此没有问题。我可以理解，锁定一行也会锁定索引，因为聚集索引的叶节点是行数据。

但是，为什么非聚集索引是这样的呢？如果第二个会话持有U键锁，为什么第一个会话的“步骤1”不持有此锁，因为它们与update语句相同。

--// first session
BEGIN TRAN
  update Book set name = name where id = 1 //step 1
  WaitFor Delay '00:00:20'
  update Book set name = 'trans' where id = 1 //step2
COMMIT

--// second session
BEGIN TRAN
--// this statement will keep both RID(U lock) and KEY(U lock) if first session did not use HOLDLOCK
  update Book set name = name where id = 1
COMMIT

Answer 1

这里的相关因素是在where子句中使用具有非聚集索引的列。当SQL Server处理更新时，它会如下所示：

1. 查找要更新的行，在触摸数据
2. 更新行上使用U锁，对修改后的数据

使用X锁

在语句完成后(在默认的READ COMMITTED隔离下)，U锁会被释放，但是X锁会一直保持到事务结束，以保持隔离。

在非聚集索引情况下，Server对id上的索引进行查找，并使用该索引查找实际行。锁的效果如下：

I=1

1. (会话1，步骤1)对id =1
2. 索引键值采取的U锁(会话1，步骤1)在RID =1
3. (会话1，步骤1) U锁上对id =1
4. (会话2)的索引键值采取U锁(会话2)用id =1

H 119(会话1，步骤2)在索引键值上阻塞的U锁( id =1-死锁H 220G 221)

但是，当索引是聚集索引时，没有单独的步骤将索引键转换为行--聚集索引值是行标识符。因此，锁定结果如下：

对id =1 lock

• (Session

• (会话1，步骤1)的索引键值采取U锁(会话1，步骤1) U锁升级为X

2) U锁在索引键值上被封锁，用于id =1

• (会话1，步骤2)已持有的索引键值上的锁，用于id =1

• (会话1，提交)锁granted

• (Session 2) U锁升级到X lock

• (Session 2)锁已释放的

G 239

与往常一样，请记住，虽然这可能是在本例中使用的查询计划，但优化器可以自由地进行不同的操作。例如，它可能选择表扫描或取出更粗粒度的锁。在这种情况下，死锁可能不会发生。

Answer 2

这个链接有很多有用的建议：SQL Server deadlocks between select/update or multiple selects。

以下是一些可以帮助人们回答你问题的要点：

1. 您正在使用什么事务隔离级别？
2. 允许锁升级(例如从一行到页)？
3. 在“name”列上有索引吗？

Answer 3

您的第一次更新实际上没有修改任何内容：

update Book set name = name where id = 1

您的命令实际上更改了您的列，则行上将持有独占锁。