带非聚簇索引的SQL Server 2005死锁

 

任何人都可以帮助我解决SQL Server 2005中的死锁问题吗？

对于一个简单的测试，我有一个表“Book”，它有一个主键（id）和一个列名。 此主键的默认索引是非聚簇 。

当两个会话同时运行时发生死锁。 活动监视器显示第一个会话“//第1步”用X锁定行（摆脱锁定）。 第二个会话保持U行锁定和U锁定。 死锁图片显示第一个会话的“//第二步”需要密钥U锁。

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

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

--// 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

这里的相关因素是，您在具有非聚集索引的where子句中使用了一列。 当SQL Server处理更新时，它就像这样：

  • 找到要更新的行，对触摸的数据进行U锁
  • 更新行，在修改的数据上获取X锁

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

    在您的非聚集索引情况下，SQL Server寻找id上的索引并使用它来查找实际行。 锁定是这样发挥的：

  • （会话1，步骤1）对id = 1的索引键值进行U锁
  • （会话1，步骤1）对ID为1的行采用RID进行X锁定
  • （会议1，第1步）U锁释放
  • （会话2）对id = 1的索引键值进行U锁
  • （会话2）对于id为1的行，R锁定了R锁
  • （会话1，步骤2）U锁被锁定在id = 1的索引键值 - DEADLOCK上

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

  • （会话1，步骤1）对id = 1的索引键值进行U锁
  • （会话1，步骤1）U锁升级到X锁
  • （会话2）U锁被锁定在id = 1的索引键值上
  • （会话1，步骤2）锁定已经保持在id = 1的索引键值上
  • （会话1，提交）锁定发布
  • （会话2）授予U锁
  • （会话2）U锁升级到X锁
  • （会话2）锁定发布

    • 与往常一样，请记住，尽管这可能是在这种情况下使用的查询计划，但优化程序可以自由地以不同的方式做事。 例如，它可以选择一个表扫描或取出更多粗粒度的锁。 在这些情况下，僵局可能不会发生。

    这个链接有很多有用的建议：选择/更新或多个选择之间的SQL Server死锁。

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

  • 您使用的是什么事务隔离级别？
  • 是否允许锁升级（例如，从一行到另一页）？
  • “名称”列是否有索引？

    • 你的第一次更新实际上并不修改任何内容 

    update Book set name = name where id = 1

    你的命令实际上改变了你的列，然后一个独占锁将被保持在行中。

    链接地址: http://www.djcxy.com/p/17097.html

    上一篇: SQL Server 2005 deadlock with nonclustered index

    下一篇: How to get a random number in Ruby