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:
- Unique index, row exists: No range locks are needed. The target row is located and
X
locked for the duration of the transaction.
- 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.
- 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
.
- 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.