SQL Server Performance Forum – Threads Archive

Key range locks and point queries

I think I’m having deadlocks caused by overlapping key-range locks, even though I am only issuing point queries. Here is the scenario:
Serializable isolation level
Query 1 selects from row 1, does some work then later updates row 1
Query 2 selects from row 2, does some work then later updates row 2
The table has a clustered index such that rows 1 and 2 will be immediately adjacent.
Both queries are executed at the same time. I think what happens is that becuase of the serializable isolation level, Query 1 does not just share lock row 1 but takes out a shared range lock that includes row 2 in the initial select.
Similarly Query 2 takes out a shared range lock that includes row 1 in the initial select. When they come to do the update, they are in a deadlock situation. In an ideal world, the range lock would not be necessary, since I’m only issuing a point query but my understanding is that SQL Server has to use RANGE locks in serializable mode. Does that sound plausible friends?
Cheers,
Bill

What happens if you specify rowlock hint in the query? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Gaurav,
That’s one of the things I’ll be trying. It’s interesting that a number of people think it may be a lock escalation issue, even though there is practically nothing else going on in the system that might prompt SQL Server not to use row locks. Does anyone know if there are undocumented lock escalation rules? Thanks
Bill

There is this one thing that happens in SQL Server… When the row is locked, a light weight lock called latch is put over the index page because SQL Server assumes that the key may be changed and hence it’ll need to change the index page as well. Could this explain what is happening? I am waiting for my fellow peers to answer. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Maybe. I had hoped to go through life without needing to understand latches…

May refer to Inside SQL server 2000 book by Kalen Delaneay about locking architecture. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>