SQL Server Performance Forum – Threads Archive

Key lock on index

I have a situation where two procudures (sp1 and sp2) often deadlocks. sp1 only reads data.
sp2 reads and modifies data. sp1 and sp2 executes from many clients simultaneously, many times a minute and the duration for the sp’s are about 5 seconds. sp1 only reads data and requests a shared page lock.
sp1 also holds a "key lock" for update although sp1 doesn’t modifies any data!? sp2 owns an "intent exclusive" lock where sp1 request a "shared lock".
sp2 requests an "exclusive lock" where sp1 holds the "key lock" sp1 -> request mode: S -> [page lock] -> owner mode: IX -> sp2
sp1 <- owner mode: U <- [Key lock] <- request mode: X <- sp2 The above illustration is from the profilers "deadlock graph". My question is really;
Can a procedure that only reads and returns data own a key lock on an index?

---

"Key" is about the granularity of the lock. Not the mode. So it is possible to have a shared key lock. But I dont think that a read procedure will request an Xclusive lock on an index. Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

---

]]>