SQL Server - RangeX-X and RangeI-N locks

Question

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?

Answer 1

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.}

Answer 2

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.

From the documentation on serializable operations (emphasis added):

When inserting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the insert operation. Locking the inserted key value until the end of the transaction is sufficient to maintain serializability. For example, given this INSERT statement:

INSERT mytable VALUES ('Dan');

The RangeI-N mode key-range lock is placed on the index entry corresponding to the name David to test the range. If the lock is granted, Dan is inserted and an exclusive (X) lock is placed on the value Dan. The RangeI-N mode key-range lock is necessary only to test the range and is not held for the duration of the transaction performing the insert operation.

Note the insert tests the range regardless of the isolation it is running under, to ensure compatibility with any other concurrent transactions running at the serializable isolation level.

---

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?

Well one cannot "insert a new value for the included column C" in isolation. If you are talking about inserting a complete new row, the situation is exactly as described above. Range I-N is used to the test the range, then an X lock is taken on the new row. There is no need for a Range X-X lock with an insert.

Perhaps you mean an UPDATE, where only the value of column C is changed. The behaviour here depends on whether the nonclustered index is unique, and whether the row you are targeting exists or not:

1. Unique index, row exists: No range locks are needed. The target row is located and X locked for the duration of the transaction.
2. Unique index, row does not exist: This is a Singleton Fetch of Non-existent Data. The range where the target index keys would fall is locked. This would normally be Range S-S, but because this is an update statement, Range S-U is taken to help avoid a common cause of conversion deadlocks. Note that multiple locks may be needed here.
3. Non-unique index, row exists: This is a Range Scan Query to locate rows to update. The range(s) are locked using Range S-U as before. When the target row is updated, an X lock on the row is acquired. There is no Range S-X lock, so the resulting combined locking mode is Range X-X.
4. Non-unique index, row does not exist: Range S-U locks are taken and held to the end of the transaction. No row is updated, so no exclusive locks are taken.

Overall, the question of included vs index key columns doesn't really matter. The serializable isolation level guarantees are provided in SQL Server by locking index keys and ranges as necessary to ensure correctness. Included columns are always contained by their associated parent keys.

Range locks are only acquired at row level, when using an index as the access method. In other cases, SQL Server uses 'regular' (non-range) locks at a higher granularity to honour the isolation level.

Answer 3

Check this link out, from the looks of it, an intent exclusive (IX) lock is illegal with an RI-N and RX-X lock. Or I am waaay off base, and really didn't help at all. I found the table in the print book, Professional SQL Server 2008 Internals and Troubleshooting,