sql-server - SQL Server 2005 非聚集索引死锁

Question

谁能帮我解决 SQL Server 2005 中的死锁问题吗？

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

当两个 session 同时运行时会发生死锁。事件监视器显示第一个 session “//step 1”使用 X 锁锁定该行(rid lock)。第二个 session 保持行 U 锁和键 U 锁。死锁图显示第一个 session 的“//step2”需要U键锁。

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

但是，为什么非聚集索引是这样的呢？如果第二个 session 持有 key U 锁，为什么第一个 session 的“步骤 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

Answer 1

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

1. 查找要更新的行，对接触的数据采取 U 锁
2. 更新行，对修改的数据使用 X 锁

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

在非聚集索引情况下，SQL Server 会查找 id 上的索引并使用它来查找实际行。锁定的效果如下:

1. ( session 1，步骤 1)对 id = 1 的索引键值采取 U 锁定
2. ( session 1，步骤 1)对 id = 1 的行的 RID 采取 X 锁定
3. ( session 1，步骤 1)U 锁已释放
4. ( session 2)对 id = 1 的索引键值采取 U 锁
5. ( session 2)X 锁因 id = 1 的行的 RID 被阻止
6. ( session 1，步骤 2)U 锁在 id = 1 的索引键值上被阻止 -- DEADLOCK

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

1. ( session 1，步骤 1)对 id = 1 的索引键值采取 U 锁定
2. ( session 1，步骤 1)U 锁升级为 X 锁
3. ( session 2)U 锁在 id = 1 的索引键值上被阻止
4. ( session 1，步骤 2)id = 1 的索引键值已锁定
5. ( session 1，提交)锁定已释放
6. ( session 2)已授予 U 锁
7. ( session 2)U锁升级为X锁
8. ( session 2)锁定已释放

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