SQL Server 2005 deadlock with nonclustered index

Question

Hello,

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 1

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:

1. What transaction isolation level are you using?
2. Is lock escalation (e.g. from row to page) allowed?
3. Is there an index on the 'name' column?

Answer 2

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.

Answer 3

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.