SQL Server NonClustered DeadLock

Question:

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

Answer:

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:

1. Find rows to update, taking U locks on touched data
2. Update rows, taking X locks on modified data

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:

1. (Session 1, step 1) U lock taken on index key value for id = 1
2. (Session 1, step 1) X lock taken on RID for row with id = 1
3. (Session 1, step 1) U lock released
4. (Session 2) U lock taken on index key value for id = 1
5. (Session 2) X lock blocked for RID for row with id = 1
6. (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. (Session 1, step 1) U lock taken on index key value for id = 1
2. (Session 1, step 1) U lock upgraded to X lock
3. (Session 2) U lock blocked on index key value for id = 1
4. (Session 1, step 2) lock already held on index key value for id = 1
5. (Session 1, commit) lock released
6. (Session 2) U lock granted
7. (Session 2) U lock upgraded to X lock
8. (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.