sql-server – SQL Server 2005与非聚簇索引的死锁

对于一个简单的测试,我有一个表“Book”,它有一个主键(id)和一个列名.此主键的默认索引是非聚集的.

当两个会话同时运行时,会发生死锁.活动监视器显示第一个会话“//步骤1”用X锁锁定行(摆脱锁定).第二个会话保持行U锁和键U锁.死锁图片显示第一个会话的“// step2”需要键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

解决方法

>查找要更新的行,对触摸的数据进行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的行,X锁定为RID
>(会话1,步骤2)U锁定id = 1的索引键值 – DEADLOCK

然而,当索引是聚簇索引时,没有将索引键转换为行的单独步骤 – 聚簇索引值是行标识符.因此,锁定结束如下：

>(会话1,步骤1)U锁升级到X锁
>(会话2)U锁定id = 1的索引键值
>(会话1,步骤2)锁已经保存在id = 1的索引键值上
(会话1,提交)锁释放
>(会话2)U锁授予
>(会话2)U锁升级到X锁
(会话2)锁释放

一如往常,请记住,尽管这可能是在这种情况下使用的查询计划,但优化器可以自由地做不同的事情.例如,它可以选择一个表扫描或者取出更粗糙的锁.在这些情况下,死锁可能不会发生.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!