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

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.

Start Serializable Transaction

Open a new SQL session and issue below query without commiting the transaction.
Until the end of this blog this session will be known to readers as session 55.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
go
begin transaction
go

update range_lock set rname ='ana' where rname = 'anna' 

Analyze the Query Execution Plan

 
 
 
 
 
 
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 Index that 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.

Check Held Locks

Execute:

sp_lock 55

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?
 
 
 
Create Blockage

Open a second SQL session (here will be known as session 58) and execute:

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.

 
 
 
 

Check Locked resources on both transactions

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:

Execute:

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:

WAIT – Lock is blocked by another process.

Execute Rollback on both sessions (55 and 58) to preserve the initial image of the table.
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.

 

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

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) not contained 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.

Start Serializable Transaction

Open a new SQL session and issue below query without commiting the transaction.
Until the end of this blog this session will be known to readers as session 55.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
go
begin transaction
go

update range_lock set sname ='surname' where rname between 'anna' and 'arlen'

Analyze the Query Execution Plan

 
 
 
 
 
 
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 Clustered Index that 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.

Check Held Locks

Execute:

sp_lock 55

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 Shared – Update locks mean that:

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

Create Blockage

Open a second SQL session (here will be known as session 58) and execute:

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.

 
 
 
 

Check Locked resources on both transactions

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:

Execute:

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:

WAIT – Lock is blocked by another process.

Execute Rollback on both sessions (55 and 58) to preserve the initial image of the table.
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.

Overview
 

Locking mechanism in SQL is by far one of the most complex mechanism and hard to explain. Blockages and Locks are a DBA’s daily problems when confronting with concurrent processes which access same records of the same table. In order to have a high – performance system, a very important aspect to consider is isolation of concurrent operations. In high-activity systems like web-sites, ERP’s or online shops there is a huge amount of concurrent transactions that select data from same tables, modify data and insert new data. In order to isolate some processes, the isolation level of transactions must be changed. The Transaction Isolation Level controls the default transaction locking behavior for all SQL statements issued by a connection.

READ COMMITTED is the default level of isolation which is also the most recommended for most processes. But this level is not always enough in order to have an ACID transaction because the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data. In order to isolate completely a Transaction which has a BEGIN TRANSACTION in front – you can SET TRANSACTION ISOLATION LEVEL to SERIALIZABLE inside a stored procedure. This does not affect other transactions and their isolation level.
Also, If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control, the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

SERIALIZABLE isolation level is equivalent HOLDLOCK hint and has the same behavior when dealing with locks.

When SERIALIZABLE isolation level is set – a range lock is placed on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. And also… it is the less explained on WWW. This is mainly the reason I want to write about the Locking types inside SERIALIZABLE transaction.

Key-Range Lock Modes
 

In serializable isolation level Key – Range locks are used. Serializability implies that any query executed inside a transaction must obtain the same set of data from the beginning until the end of the transaction. The set of data locked inside the serializable transaction is based on a range of key values of the index that covers the rows extracted. It is assured that the values from the range of values locked will not be modified or new values will not be inserted for the same range of values by other concurrent transactions. Any attempt of a second transaction to insert, update, or delete any row within the same range as the first one requires a modification to the index. Thus, the second transaction is blocked until the first transaction completes because key-range locks cover the index entries.

There are 4 types of locks:

1. RangeS-S – Shared range, shared resource lock; serializable range scan.
2. RangeS-U – Shared range, update resource lock; serializable update scan.
3. RangeX-X – Exclusive range, exclusive resource lock; used when updating a key in a range.
4. RangeI-N – Insert range, null resource lock; used to test ranges before inserting a new key into an index.

There are couple of basic things to know about key-range locking which are not mentioned in MSDN documentation:

1. In equality operations (“=”, “IN”) – Key-Range locks are not held on key values that are found inside an Unique Index even if the transaction is issued in Serializable isolation level.

   Example:
   If the ix_range index created on range_lock table would be Unique, the following statement would not require Key-Range locks.

   Shared locks will be enough.

   select rname from range_locks where rname = 'anna' 
   

2. In equality operations (“=”, “IN”) – Key-Range locks are held on “next” key values that are not found inside an Unique or Non-Unique Index.

   Example:
   Whether ix_range index created on range_lock table is Unique or Non-Unique, the following statement will hold Key-Range locks on the “next” value of the key value from the equality operation that was not found inside the index.

   In this example “annabella” field does not exist so, Key-Range locks will be put on next value from the list: “antony”.

   select rname from range_locks where rname = 'annabella' 
   

3. In in-equality operations (“>”, “<", BETWEEN, LIKE, "<>“) – Key-Range locks are held on all key values (found) from the range specified and the “next” value. This is true for both Unique or Non-Unique Indexes.

   Example:
   Whether ix_range index created on range_lock table is Unique or Non-Unique, the following statement will hold Key – Range locks on all key values in the range when using “BETWEEN” and on the “next” value of the key value that is outside the range.

   In below example RangeS-S locks will be acquired on all values between “antony” and “barry”.
   “annabella” is missing – so the starting value of the range will be considered the next value in alphabetical order after “annabella”. Also an additional RangeS-S lock will be put on the value after “barry”: “benedict”.

   select rname from range_locks where rname between 'annabella' and 'barry'
   

RangeS-S Locks
 

I will explain through examples each type of locking. In this part I will go through Key – Range Shared – Shared locks. In order to not repeat on following parts I will write only in this part the prerequisites for my tests.
I need to create a table and insert few rows into it. The table will have a Primary Clustered Key and an Index Non-Clustered on one of the three columns.

Here you go:

create table range_lock 
(rid int NOT NULL IDENTITY (1, 1),
rname nvarchar (20),
sname nvarchar (20))

ALTER TABLE range_lock ADD PRIMARY KEY (rid);

CREATE NONCLUSTERED INDEX [ix_rname] ON [dbo].[range_lock] 
(
[rname] ASC
)

Populate the table with names. The rows will be displayed in alphabetical order due to the ix_rname index.

INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('anna')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('antony')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('angel')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('ARLEN')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BARRY')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BENEDICT')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BILL')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('BRYCE')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('CAROL')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('CEDRIC')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('CLINT')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('DARELL')
INSERT INTO [recovery_test_2].[dbo].[range_lock]
           ([rname]) VALUES ('DAVID')

A simple select in the range_lock table will show the inserted values ordered by rname column ascending.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Start Serializable Transaction

Open a new SQL session and issue below query without commiting the transaction.
Until the end of this blog this session will be known to readers as session 55.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
go
Begin Transaction
go
select rname from range_lock where rname = 'anna'

Analyze the Query Execution Plan

 
 
 
 
 
 
 
 
You will see an Index Seek on ix_rname index because the ix_rname contains the rname column needed in the SELECT. Index Seek only touches only the rows that qualify.

IMPORTANT!!!

If I would issue a SELECT on all table columns, then the Query Optimizer will perform an Index Scan on the Primary Key in order to retrieve rid and sname values position from the Clustered Index. This is not optimal and must be avoided. So to avoid this, the rid and sname columns should be added as included columns inside ix_rname index.

Check Held Locks

Execute:

sp_lock 55

Here is what you would get:

It is obvious that RangeS-S locks are acquired on IndId = 2 (ix_rname) for two key values.

Remember what I wrote earlier about equality operations and Unique Indexes – that Range locks are not held? Well, here we deal with a Non-unique Index so, it is quite normal that RangeS-S locks are held. The question that pops in, is why two RangeS-S locks are acquired when the statement only retrieves data about “anna”?
That is because Range locks are acquired on the key value from the “=” operator and on the next value. This is to ensure that no row can be inserted updated or deleted between the requested key and the one after that. It means that “anna” and “antony” rows would become locked if some other transactions tried to modify the fields. Let’s see that.

If you look above in the range_lock table content you will see that after “anna” comes “antony”. Further I will try to delete the “antony” row inside another transaction from the default isolation level and see what happens.

Create Blockage

Open a second SQL session (here will be known as session 58) and execute:

begin transaction
go
delete from range_lock where rname ='antony'

The second transaction will wait for the first one to be committed.
 
Execute sp_who2 active to make sure session 58 is blocked by session 55.

 
 
 
 

Check Locked resources on both transactions

To figure out why the delete operation on “antony” row is blocked by the first select on “anna” row check this out:

Execute:

sp_lock 55 
go
sp_lock 58
go

 

I see one CNVT type of lock for the second transaction on same resource the first transaction has put RangeS-S lock. It is clear that we are speaking about same row: “antony”.

Microsoft explains CNVT locks as:

CNVT – Lock is being converted to another lock. A lock being converted to another lock is held in one mode but is waiting to acquire a stronger lock mode (for example, update to exclusive). When diagnosing blocking issues, a CNVT can be considered similar to a WAIT.

Execute Rollback on both sessions (55 and 58) to preserve the initial image of the table.
This is all about RangeS-S locks and further in part 2 and 3, I will detail more complex locks such as RangeS-U and RangeX-X.