有人可以帮助我解决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:
语句完成后(默认情况下 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:
但是,当索引是聚簇索引时,没有将索引键转换为行的单独步骤-聚簇索引值是行标识符。因此,锁定最终如下所示:
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:
与往常一样,请记住,尽管在这种情况下这可能是查询计划,但优化程序可以自由地做不同的事情。例如,它可以选择表扫描或取出更粗糙的锁。在这些情况下,僵局可能不会发生。
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.
更多推荐
具有非聚集索引的SQL Server 2005死锁
发布评论