RangeS-S – Key-Range Locks in SERIALIZABLE isolation level (part 1)

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.

Rows ordered in ascending order by the rname column.


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

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

RangeS-S locks - Execution Plan

RangeS-S locks - 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:

RangeS-S for equality operation - Non-Unique Index

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.

58 blocked by 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

RangeS-S and CNVT locks


 

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.

  1. I wanted to thank you for this great read!! I definitely enjoying every little bit of it I have you bookmarked to check out new stuff you post

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>