patternsqlMinor
SQL Server - RangeX-X and RangeI-N locks
Viewed 0 times
rangeiserversqlrangexandlocks
Problem
I came to a dead point in a deadlock analyze. According to msdn:
RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range.
RangeI-N are Insert range, null resource lock; used to test ranges before inserting a new key into an index.
So I understand that if I have an Index on 2 key columns - and I insert a new key I would have RangeI-N lock but if I update an existing key from the index I would have RangeX-X.
But my question is more or less complicated. Say I have the index IX_keys_included on column A, B and included column C.
In Serializable isolation mode I insert a new value for the included column C. Will there be RangeI-N or RangeX-X locks for the index IX_keys_included? Actually , will there be any locks given the fact that I insert a new column for an included column in the index?
RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range.
RangeI-N are Insert range, null resource lock; used to test ranges before inserting a new key into an index.
So I understand that if I have an Index on 2 key columns - and I insert a new key I would have RangeI-N lock but if I update an existing key from the index I would have RangeX-X.
But my question is more or less complicated. Say I have the index IX_keys_included on column A, B and included column C.
In Serializable isolation mode I insert a new value for the included column C. Will there be RangeI-N or RangeX-X locks for the index IX_keys_included? Actually , will there be any locks given the fact that I insert a new column for an included column in the index?
Solution
I have figured this on my own and I wrote on my blog. For those interested in the solution visit this posts: RangeS-S, RangeS-U, RangeX-X
NB: The links above have been modified to point to archive.org because the site is no longer valid. It's unfortunate. Also, the content of the blogs is quite extensive or I would try to capture some of that data to here. It's just too much for one post. ~ jcolebrand
PS: Don't forget to throw a few dollars at the archive.org folks if you follow these links.
NB: The links above have been modified to point to archive.org because the site is no longer valid. It's unfortunate. Also, the content of the blogs is quite extensive or I would try to capture some of that data to here. It's just too much for one post. ~ jcolebrand
PS: Don't forget to throw a few dollars at the archive.org folks if you follow these links.
Context
StackExchange Database Administrators Q#2425, answer score: 4
Revisions (0)
No revisions yet.