Locks placed by queries on SQL Server on selected Isolation Levels.

Locks placed by queries on SQL Server on selected Isolation Levels.

When a query executes, SQL Server places locks on resources. In this article I am going to analyze in detail, what are the locks placed, types of locks, number of locks based on the query executed against the database depending on the isolation level.

First I’ll open a new query window against the adventure works database and executes below query. It will show the current locks placed against this database. Please note that at the moment there are no any current activities on this database and the default isolation (READ COMMITTED) level is set.

SELECT resource_type, request_mode, request_type, request_session_id, request_owner_type, request_status

FROM sys.dm_tran_locks

 

Results looks as below

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
DATABASE	S	LOCK	52	SHARED_TRANSACTION_WORKSPACE	GRANT

 

According to the results, this is a database level shared lock. This lock was requested by a shared transaction workplace which is a session. This session was created by the query window I opened to execute above query.

Now I’ll open another query window to execute a query. Before executing the query, I execute the above query again to see the current locks.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
DATABASE	S	LOCK	53	SHARED_TRANSACTION_WORKSPACE	GRANT
DATABASE	S	LOCK	52	SHARED_TRANSACTION_WORKSPACE	GRANT

 

Now we can see two shared database locks with two different session ids. This is because two query windows I have opened. Let’s execute a simple SELECT query and observe how locks are placed.

SELECT COUNT(1) FROM Production.TransactionHistory

 

Even though it is included in a transaction, it seems no locks have been applied. This is because the SELECT statement commits just after it is executed regardless of the transaction. So we can’t see the lock applied by the SELECT query.

Let’s change the transaction Isolation Level to a higher level by issuing following command

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

 

And execute the SELECT query again and see the locks results are as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
DATABASE	S	LOCK	53	SHARED_TRANSACTION_WORKSPACE	GRANT
DATABASE	S	LOCK	52	SHARED_TRANSACTION_WORKSPACE	GRANT
OBJECT	S	LOCK	53	TRANSACTION	GRANT

We can see that there is an additional SHARED lock on an object. Actually it is the table in the query and therefore it is a TABLE LOCK. The lock has been requested by a transaction which is the transaction I started with the SELECT query.

Now I will try to execute an INSERT and an UPDATE statement against the same table. Before that I’ll change the query to check the locks to filter out the locks created by sessions which are not important to our context.

SELECT resource_type, request_mode, request_type, request_session_id, request_owner_type, request_status

FROM sys.dm_tran_locks

WHERE request_owner_id <> 0

 

Then I try to execute below UPDATE query.

UPDATE Production.TransactionHistory SET ModifiedDate = ModifiedDate + 1

 

Once this is executed an additional lock can be seen as below

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
OBJECT	S	LOCK	53	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	WAIT

 

This is the lock requested by the UPDATE statement and resource cannot be granted because of the SHARED lock already placed. So it is in the WAIT status. So this query has placed an INTENT EXLUSIVE lock since it can’t place to prevent EXCLUSIVE placing exclusive locks by any other requet. Let’s try with an INSERT statement in another window.

INSERT INTO  Production.TransactionHistory(ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate)

VALUES (316, 100000, 1000001, GETDATE(), 'I', 100, 1000, GETDATE())

 

 

Locks looks like below

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
OBJECT	S	LOCK	53	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	WAIT

 

Even the INSERT statement is waiting.

Let’s change the SELECT query to access only a set of records as below. I’ll start it with a transaction to hold the locks.

BEGIN TRANSACTION

 

SELECT * FROM Production.TransactionHistory WHERE TransactionID BETWEEN 150001 AND 150010

 

This query returned 10 rows and locks are placed as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status
PAGE	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT

 

This shows that there are 10 SHARED locks on the 10 rows selected and two INTENT SHARED locks on the page and on the table. Now I’ll execute an UPDATE statement which will try to update set of records from the selected 10 records and some additional records.

BEGIN TRANSACTION

UPDATE Production.TransactionHistory SET ModifiedDate = ModifiedDate + 1 WHERE TransactionID BETWEEN 150008 AND 150015

 

Results for the locks applied looks below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status	resource_description
PAGE	IS	LOCK	52	TRANSACTION	GRANT	1:16779
PAGE	IX	LOCK	54	TRANSACTION	GRANT	1:16779
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT	(4b8cf61b9bed)
KEY	S	LOCK	52	TRANSACTION	GRANT	(3bc698094207)
KEY	S	LOCK	52	TRANSACTION	GRANT	(52f4b303ba5d)
KEY	S	LOCK	52	TRANSACTION	GRANT	(6ad1848ad628)
KEY	S	LOCK	52	TRANSACTION	GRANT	(8ae5aa051294)
KEY	S	LOCK	52	TRANSACTION	GRANT	(b2c09d8c7ee1)
KEY	S	LOCK	52	TRANSACTION	GRANT	(c28af39ea70b)
KEY	S	LOCK	52	TRANSACTION	GRANT	(939def1d3324)
KEY	S	LOCK	52	TRANSACTION	GRANT	(abb8d8945f51)
KEY	S	LOCK	52	TRANSACTION	GRANT	(dbf2b68686bb)
KEY	X	LOCK	54	TRANSACTION	WAIT	(c28af39ea70b)

 

Here I have included an additional column “resourse_description” to see what are the resources acquired by the queries. Other than the locks applied by the SELECT query, there are two INTENT EXCLUSIVE locks applied by the UPDATE query and it is WAITIING for an ECLUSIVE lock on one KEY.  So the UPDATE statement doesn’t intend to apply locks on other KEYs until the lock is granted to the first KEY.

It looks like, it tries to apply the locks on the order of the KEY. If the UPDATE statement range started, below 150001 in WHERE condition, Locks will be placed to the values below 150001 and WAITING to apply lock on the KEY 150001. If the range is totally out of the SELECT statement’s range, it will apply locks to all the KEYS as below. I change the query to …

BEGIN TRANSACTION

UPDATE Production.TransactionHistory SET ModifiedDate = ModifiedDate + 1 WHERE TransactionID BETWEEN 150011 AND 150015

 

… and the locks are as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status	resource_description
PAGE	IS	LOCK	52	TRANSACTION	GRANT	1:16779
PAGE	IX	LOCK	54	TRANSACTION	GRANT	1:16779
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	GRANT
KEY	S	LOCK	52	TRANSACTION	GRANT	(8ae5aa051294)
KEY	S	LOCK	52	TRANSACTION	GRANT	(b2c09d8c7ee1)
KEY	S	LOCK	52	TRANSACTION	GRANT	(939def1d3324)
KEY	S	LOCK	52	TRANSACTION	GRANT	(abb8d8945f51)
KEY	S	LOCK	52	TRANSACTION	GRANT	(52f4b303ba5d)
KEY	S	LOCK	52	TRANSACTION	GRANT	(6ad1848ad628)
KEY	S	LOCK	52	TRANSACTION	GRANT	(c28af39ea70b)
KEY	S	LOCK	52	TRANSACTION	GRANT	(4b8cf61b9bed)
KEY	S	LOCK	52	TRANSACTION	GRANT	(3bc698094207)
KEY	S	LOCK	52	TRANSACTION	GRANT	(dbf2b68686bb)
KEY	X	LOCK	54	TRANSACTION	GRANT	(03e3af802e72)
KEY	X	LOCK	54	TRANSACTION	GRANT	(22bedd1163b7)
KEY	X	LOCK	54	TRANSACTION	GRANT	(1a9bea980fc2)
KEY	X	LOCK	54	TRANSACTION	GRANT	(e3d7810feace)
KEY	X	LOCK	54	TRANSACTION	GRANT	(faafc417cb7e)

 

Here we can see that there are additional EXCLUSIVE locks for the five rows that are trying to UPDATE.

 

For the above same queries, locks are different if the TRANSACTION_ISOLATION_LEVEL is SERIALIZABLE. Then the locks are as below.

resource_type	request_mode	request_type	request_session_id	request_owner_type	request_status	resource_description
PAGE	IS	LOCK	52	TRANSACTION	GRANT	1:16779
PAGE	IX	LOCK	54	TRANSACTION	GRANT	1:16779
OBJECT	IS	LOCK	52	TRANSACTION	GRANT
OBJECT	IX	LOCK	54	TRANSACTION	GRANT
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(939def1d3324)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(abb8d8945f51)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(dbf2b68686bb)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(52f4b303ba5d)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(6ad1848ad628)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(22bedd1163b7)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(8ae5aa051294)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(b2c09d8c7ee1)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(c28af39ea70b)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(4b8cf61b9bed)
KEY	RangeS-S	LOCK	52	TRANSACTION	GRANT	(3bc698094207)
KEY	X	LOCK	54	TRANSACTION	WAIT	(22bedd1163b7)

 

According to MSDN “A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction”. But it is not only INSERT but also UPDATES are prevented.

In this analysis, we can clearly understand how locks applied differently in different isolation levels.