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

本文介绍了具有非聚集索引的SQL Server 2005死锁的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

有人可以帮助我解决SQL Server 2005的僵局吗？

Can anybody help me for a deadlock in SQL Server 2005?

对于一个简单的测试，我有一个表 Book，该表具有一个主键（id）和一个列名。此主键的默认索引为非集群。

For a simple test, I have a table "Book" which has a primary key (id), and a column name. The default index of this primary key is nonclustered.

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

The deadlock happens when two sessions run at a same time. Activity monitor shows the first session "//step 1" locks the row(rid lock) with X lock. The second session keeps row U lock and key U lock. The deadlock picture shows "//step2" of first session requires key U lock.

如果索引是聚集的，则没有死锁这个案例。 //步骤1将同时保持行和键锁定，因此没有问题。我可以理解，由于聚集索引的叶节点是行数据，因此锁定行也将锁定索引。

If the index is clustered, there is no deadlock in this case. "//step 1" will keep the row and key lock at same time, so there is no problem. I can understand locking a row will also lock the index since leaf node of clustered index is row data.

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

But, why nonclustered index is in this way? If the second session holds the key U lock, why "step 1" of first session does not hold this lock since they are same the update statement.

--// 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处理更新时，它是这样的：

The relevant factor here is that you're using a column in your where clause that has a nonclustered index. When SQL Server processes an update, it goes something like this:

查找要更新的行，对触摸的数据采取U锁

更新行，对已修改的数据进行X锁定

语句完成后（默认情况下 READ COMMITTED 隔离），将释放U锁，但将X锁保持到事务结束，以保持隔离。

After the statement completes (under the default READ COMMITTED isolation), the U locks are released but the X locks are held until the end of the transaction to maintain isolation.

在您的非聚集索引情况下，SQL Server在id上寻找索引，并使用它来查找实际行。锁定如下所示：

In your nonclustered index situation, SQL Server seeks on the index on id and uses this to look up the actual row. The locking plays out like this:

（会话1，第1步）对id = 1的索引键值进行U锁定

（会话1，步骤1）对ID = 1的行在RID上进行了X锁定

（会话1，步骤1）U锁定已释放

（会话2）对id = 1的索引键值采取U锁定

（会话2）对ID = 1的行的RID阻止X锁定

（会话1，第2步）在ID = 1的索引键值上阻止了U锁-DEADLOCK

(Session 1, step 1) U lock taken on index key value for id = 1

(Session 1, step 1) X lock taken on RID for row with id = 1

(Session 1, step 1) U lock released

(Session 2) U lock taken on index key value for id = 1

(Session 2) X lock blocked for RID for row with id = 1

(Session 1, step 2) U lock blocked on index key value for id = 1 -- DEADLOCK

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

However, when the index is the clustered index, there isn't a separate step for converting the index key into the row -- the clustered index value is the row identifier. Therefore, the locking ends up like this:

（会话1，第1步）对id = 1

（会话1，第1步）U锁已升级为X锁

（会话2）U锁已阻止ID = 1的索引键值

（会话1，步骤2）对ID = 1的索引键值已持有的锁定

（会话1，提交）锁已释放

（会话2）授予U锁

（会话2）U锁已升级为X锁

（会话2 ）锁定已释放

(Session 1, step 1) U lock taken on index key value for id = 1

(Session 1, step 1) U lock upgraded to X lock

(Session 2) U lock blocked on index key value for id = 1

(Session 1, step 2) lock already held on index key value for id = 1

(Session 1, commit) lock released

(Session 2) U lock granted

(Session 2) U lock upgraded to X lock

(Session 2) lock released

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

As always, keep in mind that while this may be the query plan used in this case, the optimizer is free to do things differently. For example, it may choose a table scan or to take out more coarsely-grained locks. In these cases the deadlock may not happen.