I will continue in this part describing other types of Key – Range locks: RangeX-X combined with RangeS-U locks.
As a reference I will use the same table created in part 1 – range_lock.
RangeX-X locks are exclusive resource locks acquired on Index key values from a specific range when the transaction modifies a key value from the Index. The locks are exclusive locks meaning, no other type of transaction (Select/Update/Insert/Delete) can be executed until the first transaction is completed/commited on the range key values modified.
Open a new SQL session and issue below query
Until the end of this blog this session will be known to readers as
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE go begin transaction go update range_lock set rname ='ana' where rname = 'anna'
You will see a Clustered Index Update, and an Index seek on ix_rname (rname column) – this defines the range of the key values to seek in.
At first look, the execution plan looks exactly as it did on RangeS-U locks – with a slight difference on the operator cost (%): 75% on RangeS-U and 86% on RangeX-X. Why? If I mouse over the Clustered Index Update operation I see 2 indexes listed for Update operation: Primary Key and ix_rname index.
Both Indexes are planned for update because:
- PK_range_locks – in this case is a
Clustered Indexthat orders the data rows in the table based on its (Primary Key) key values. The update is made on another column than the defined Primary key column. All data rows from a table are called the leaf level of the index. The Primary Key values contain pointers to all data rows from the table. Any update on another column than the primary key will result to an update on the leaf level of the Clustered Index.
- ix_rname – Update is made on a key value from the Index, so the index should be updated with a different value.
Here is what you would get:
I see that one Exclusive lock (X) is held on IndId=1 (Primary Key) which is needed for the Index update, and I see that RangeX-X lock is acquired on IndId = 2 (ix_rname) on one key value. Also RangeS-U is acquired on IndId = 2 (ix_rname) on one key value. If I go back in part 1, and take a look on table rows and their order, I will see that after “anna” is “antony” row. I can take a smal guess that the RangeS-U lock is on the next key value after “anna” – “antony”. How can I prove that?
Open a second SQL session (here will be known as
begin transaction select rname from range_lock where rname = 'antony'
This statement will work fine. RangeS-U locks allows Select statements to be executed. Rollback this transaction.
Inside the same session execute:
begin transaction update range_lock set rname = 'antoni' where rname = 'antony'
You will see that this transaction will be blocked by the first one.
To figure out why the update operation on rname column for the “antony” row is blocked by the first update on rname column for the “anna” rows check this out:
sp_lock 55 go sp_lock 58 go
I see one WAIT type of lock for the second transaction on same resource the first transaction has put RangeS-U lock. It is clear that we are speaking about same row: “antony”.
Microsoft explains WAIT locks as:
The conclusion I get is: RangeX-X locks will only be acquired on those columns that are actually modified from the range. Even if the range contains the “next” value from the table list of rows, RangeS-U locks will be held on that value (s), in order to maintain the integrity of data until the transaction is commited.