SQL Server 2005 deadlock with nonclustered index
Can anybody help me for a deadlock in SQL Server 2005?
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 .
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.
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.
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
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:
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.
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.
This link has a lot of useful suggestions: SQL Server deadlocks between select/update or multiple selects.
Here are some points to consider that might help people to answer your question:
Your first update does not actually modify anything:
update Book set name = name where id = 1
Thy a command which actually changes your column, then an exclusive lock will be held on the row.
链接地址: http://www.djcxy.com/p/17098.html上一篇: 游戏引擎脚本语言