I will continue in this part describing other types of Key – Range locks.
As a reference I will use the same table created in part 1 – range_lock.
RangeS-U locks are locks acquired on Index key values from a specific range when the following conditions are met:
- The transaction contains an UPDATE statement
- WHERE clause is used to specify a condition that limit the rows that are updated. The condition will include at least one column from the Index. This will define the range of key value(s) from the Index. In this case, locks are needed on defining key value(s) that refer to the rows updated.
- The UPDATE modifies a column(s) which is(are)
notcontained in the Index definition or:
- The UPDATE modifies a column(s) which
is(are)contained in the Index definition.
I hope from the below example the explanation will more straightforward. I will use a larger range of values in this example, meaning I will use an inequality operation. The equality operation will have the same types of locks.
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 sname ='surname' where rname between 'anna' and 'arlen'
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.
The update on PK_range_locks happens because in this case the PK is a
Here is what you would get:
I see 3 Exclusive locks (X) are held on IndId=1 (Primary Key) which are needed for the Clustered Index update, and I see that RangeS-U locks are acquired on IndId = 2 (ix_rname) on four key values. If I go back in part 1, and take a look on table rows and their order, I will see that between “anna” and “arlen” are 3 rows, not 4. The fourth lock is on the next key value after “arlen” – “barry”.
As the name suggests
- Other transactions that need to Select the same rows inside the range will be permitted.
- Other transactions that perform an update on any of the key values from the range – will be blocked by the first one until the first one is commited or rollbacked.
Open a second SQL session (here will be known as
begin transaction select rname from range_lock where rname = 'barry'
This statement will work fine. Rollback this transaction.
Inside the same session execute:
begin transaction update range_lock set rname = 'barri' where rname = 'barry'
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 “barry” row is blocked by the first update on sname column for the rows between “anna” and “arlen” 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: “barry”.
Microsoft explains WAIT locks as:
This is all about RangeS-U locks and further in part 3, I will detail RangeX-X locks and RangeX-X combined with Range S-U locks.