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.
In serializable isolation level
RangeS-S– Shared range, shared resource lock; serializable range scan. RangeS-U– Shared range, update resource lock; serializable update scan. RangeX-X– Exclusive range, exclusive resource lock; used when updating a key in a range. 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:
- In equality operations (“=”, “IN”) – Key-Range locks
are not heldon key values that are found inside an Unique Indexeven if the transaction is issued in Serializable isolation level. Example:
ix_rangeindex 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'
- In equality operations (“=”, “IN”) – Key-Range locks
are heldon “next” key values that are not found inside an Unique or Non-Unique Index. Example:
ix_rangeindex 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'
- In in-equality operations (“>”, “<", BETWEEN, LIKE, "<>“) – Key-Range locks
are heldon all key values (found) from the range specified and the “next” value. This is true for both Unique or Non-Unique Indexes. Example:
ix_rangeindex 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'
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
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.
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 select rname from range_lock where rname = 'anna'
You will see an
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.
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.
Open a second SQL session (here will be known as
begin transaction go delete from range_lock where rname ='antony'
The second transaction will wait for the first one to be committed.
To figure out why the delete operation on “antony” row is blocked by the first select on “anna” row check this out:
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:
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.