SQL Server 2005 deadlock with nonclustered index -

can me deadlock in sql server 2005?

for simple test, have table "book" has primary key (id), , column name. default index of primary key nonclustered.

the deadlock happens when 2 sessions run @ same time. activity monitor shows first session "//step 1" locks row(rid lock) x lock. second session keeps row u lock , key u lock. deadlock picture shows "//step2" of first session requires key u lock.

if index clustered, there no deadlock in case. "//step 1" keep row , key lock @ same time, there no problem. can understand locking row lock index since leaf node of clustered index row data.

but, why nonclustered index in way? if second session holds key u lock, why "step 1" of first session not hold lock since same update statement.

--// first session begin tran   update book set name = name id = 1 //step 1   waitfor delay '00:00:20'   update book set name = 'trans' id = 1 //step2 commit  --// second session begin tran --// statement keep both rid(u lock) , key(u lock) if first session did not use holdlock   update book set name = name id = 1 commit 

the relevant factor here you're using column in where clause has nonclustered index. when sql server processes update, goes this:

1. find rows update, taking u locks on touched data
2. update rows, taking x locks on modified data

after statement completes (under default read committed isolation), u locks released x locks held until end of transaction maintain isolation.

in nonclustered index situation, sql server seeks on index on id , uses actual row. locking plays out this:

1. (session 1, step 1) u lock taken on index key value id = 1
2. (session 1, step 1) x lock taken on rid row id = 1
3. (session 1, step 1) u lock released
4. (session 2) u lock taken on index key value id = 1
5. (session 2) x lock blocked rid row id = 1
6. (session 1, step 2) u lock blocked on index key value id = 1 -- deadlock

however, when index clustered index, there isn't separate step converting index key row -- clustered index value is row identifier. therefore, locking ends this:

1. (session 1, step 1) u lock taken on index key value id = 1
2. (session 1, step 1) u lock upgraded x lock
3. (session 2) u lock blocked on index key value id = 1
4. (session 1, step 2) lock held on index key value id = 1
5. (session 1, commit) lock released
6. (session 2) u lock granted
7. (session 2) u lock upgraded x lock
8. (session 2) lock released

as always, keep in mind while may query plan used in case, optimizer free things differently. example, may choose table scan or take out more coarsely-grained locks. in these cases deadlock may not happen.