Key-Range Locks in the Serializable Isolation Level and using sys.dm_tran_locks and DBCC PAGE to see what’s locked

In a previous post I talked about Locking, Blocking and Isolation Levels.  In this post I’m going to go into more detail about the key-range locks used in the SERIALIZABLE isolation level and also demonstrate to use the sys.dm_tran_locks DMV to view existing locks and also how to work out what resources the locks are placed on.

Key-range locks are only used in the SERIALIZABLE isolation level and their purpose is to stop phantom reads which are possible in the other three pessimistic concurrency isolation levels, namely read uncommitted, read committed and repeatable read.  A phantom read is where one transaction performs a read on a range of data, e.g. using a query with a WHERE clause, and another transaction adds data that will satisfy the conditions of the WHERE clause.  If the first transaction performs the same read it will now return the new data resulting in phantom data appearing in the second read within the same transaction.  The key-range locks are used to prevent phantom data from being added and they are placed on the keys of the index that is used to retrieve the data.  The following example using the Person.Person table in the AdventureWorks2012 database will demonstrate how these key-range locks are used.

The Person.Person table has a non-clustered index IX_Person_LastName_FirstName_MiddleName with the LastName column as the first column in the index key.  This means that any query where we are filtering on LastName should use this index to retrieve the data.

If we run a query to return a few rows from Person.Person filtering on a range of last names then we should see this index being used.  For example

SELECT *
FROM Person.Person
WHERE LastName BETWEEN 'Ware' AND 'Warthen'
ORDER BY LastName

Returns the following result set.

And looking at the execution plan we can see that this index is used.

Now if we set the isolation level to SERIALIZABLE, open a transaction and run the same select statement we should be able to see what locks are being held.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT *
FROM Person.Person
WHERE LastName BETWEEN 'Ware' AND 'Warthen'

Now from another connection we can query the DMV sys.dm_tran_locks to see the current locks being held.  We are only interested in the locks on the KEY resources so we filter accordingly.

SELECT *
FROM sys.dm_tran_locks
WHERE resource_type = 'KEY'
ORDER BY request_mode

We get seven rows retuned with two different request_mode values.  The “S” request_mode corresponds to shared locks placed on the three rows that are returned.  Because we’re using the SERIALIZABLE isolation level these shared locks are held until the transaction commits or rolls back to prevent non-repeatable reads.  The “RangeS-S” request_mode corresponds to the key-range locks that have been granted.  But why are there four rows?  To answer this we can use the data returned from sys.dm_tran_locks and use this to work out what resources have actually been locked.

The columns we’re  interested in are resource_associated_entity_id and resource_description.  The data in these columns can vary based on the resource_type and for more details on this see TechNet. For KEY resources the resource_associated_entity_id corresponds to the partition_id from sys.partitions

SELECT *
FROM sys.partitions
WHERE partition_id in (72057594057523200, 72057594045595648)

The object_id returned corresponds to the Person.Person table

SELECT object_name(1765581328)

We can see that the two different partition_id values correspond to different indexes on Person.Person

SELECT *
 FROM sys.indexes
 WHERE object_id = 1765581328

The sys.partitions view tells us that the partitions we’ve got locks on are for the indexes PK_Person_BusinessEntityID and IX_Person_LastName_FirstName_MiddleName.

We can combine this all in one query for clarity.

 SELECT
      L.resource_type
     ,L.resource_description
     ,L.resource_associated_entity_id
     ,L.request_mode
     ,L.request_type
     ,L.request_status
     ,OBJECT_NAME(I.object_id) AS TableName
     ,I.object_id
     ,I.index_id
     ,I.name AS IndexName
     ,I.type_desc
 FROM sys.dm_tran_locks L
 INNER JOIN sys.partitions P ON P.partition_id = L.resource_associated_entity_id
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE L.resource_type = 'KEY'
 ORDER BY L.request_mode

This shows us that three shared locks are placed on the rows themselves (the leaf level of the clustered index) and that four key-range locks are placed on keys within the non-clustered index. But this hasn’t answered the question of why we have four locks?

Before we do that I’m going to demonstrate two ways to prove that the three shared locks are placed on the rows that were returned by the original query and then use that to work out what resources the four “RangeS-S” locks are placed on.

Firstly we are going to use the %%physloc%% and %%lockres%% values along with the sys.fn_PhysLocFormatter() function to tell us the file, page and slot of the rows returned by the original query.  Secondly we use this information in the DBCC PAGE command.

We just need to update our original query as follows

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS FilePageSlot, %%lockres%% AS LockResource, *
FROM Person.Person
WHERE LastName BETWEEN 'Ware' AND 'Warthen'

We can now see that the row for Dennis Ware is physically located on data file 1, on page 2806 and at slot 3. We can also see that it’s lock resource is (05c585e839d4).

If we look back to the data returned from sys.dm_tran_locks we can see that one of the shared locks has a resource_description of (05c585e839d4) so this tells us that that lock is placed on that row in the clustered index.

Now we can run the following DBCC PAGE command to actually see information about what is on the page but in order for this to return any results we need to turn on trace flag 3604.

DBCC TRACEON (3604);
DBCC PAGE (10, 1, 2806, 3); -- DB_ID, FileId, PageId, Format

There is a lot of textual information displayed here and you can specify a different number in the format parameter to return different amounts of data. In this case the first bit is the page header and then after that we have the Allocation Status. You’ll see the first slot used is Slot 0. There is an Offset value that tells us where on the page this slot starts and a Length values that tells us how long the record is.

If we scroll down until we find Slot 3, and then scroll past the block of data we can see the physical data for each column and can confirm this is the row for Dennis Ware.

Now, if we go back up to the end of Slot 3 we can see

KeyHashValue = (05c585e839d4)

This matches the resource_description in sys.dm_tran_locks and the value returned by %%lockres%%.

But how do we do something similar for the key-range locks? How do we find out what resources they are locking?  One way would be to use sys.dm_db_database_page_allocations to work out which pages belong to out non-clustered index. We pass in the database_id, the object_id and the index_id of the IX_Person_LastName_FirstName_MiddleName index and it will return a row for every page used within that index. We can then run DBCC PAGE with the supplied allocated_page_page_id values and find the page that contains Dennis Ware.

SELECT allocated_page_page_id, *
FROM sys.dm_db_database_page_allocations (DB_ID(N'AdventureWorks2012'), object_Id('AdventureWorks2012.Person.Person'), 4, null, 'DETAILED')
WHERE is_allocated = 1

We can then run DBCC PAGE with the supplied value and find the page that contains Dennis Ware.

DBCC PAGE (10, 1, 24745, 3); -- DB_ID, FileId, PageId, Format

The handy thing is that in this case DBCC PAGE returns the data in the page in a result set so we can quite quickly find the correct page, in this case page 24745.

Now if we use the resource_description values for  our four key-range locks in sys.dm_tran_locks we can see that the “RangeS-S” locks have actually been granted to the rows that contain data for Dennis Ware, Victoria Ware, Clay Warten and Aaron Washington.  But Aaron Washington is not included in the result set, so why is this fourth key lock present?  It’s because when a key-range is taken on a key, it will prevent any data being added immediately before a key with a key-range lock.  In other words no data can be added between, for example, Victoria Ware and Clay Warthen, e.g. Jennifer Warner.  

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)
VALUES (888888, 'VC', 'Jennifer', 'Warner')
SELECT *
FROM sys.dm_tran_locks
WHERE resource_type = 'KEY'
ORDER BY request_mode

Here we can see the waiting key-range lock.

It also means that no data can be added between Timothy Ward and Aaron Washington and means that the key-range locks actually lock more data than that which could satisfied the query in the open transaction.  For example, we could not insert a row for Simon Wartz which would be put between Clay Warthen and Aaron Washington, even though it would not be returned in the original query in the open transaction.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)
VALUES (999999, 'VC', 'Simon', 'Wartz')
SELECT *
FROM sys.dm_tran_locks
WHERE resource_type = 'KEY'
ORDER BY request_mode

Now we know how the key-range locks are used we can work out what resources have the key-range locks on them without the need for searching through the pages of the non-clustered index.  We can simply include a table hint to return part of the data we require.

SELECT sys.fn_PhysLocFormatter(%%physloc%%), %%lockres%%, *
FROM Person.Person WITH (INDEX = IX_Person_LastName_FirstName_MiddleName)
WHERE LastName BETWEEN 'Ware' AND 'Warthen'

As you can see this gives us the same file, page and slot data and the same resource_description for the three rows returned by the query.  We now know that the fourth “RangeS-S” lock is placed on the key in the index immediately after the key for Clay Warthen, i.e. slot 140.  Again we can use DBCC PAGE to view the data on page 25065.

Now in the demonstration I’ve just run through we happen to have a non-clustered index with the first column in the clustering key being the one that we’re using in our WHERE clause.  But what happens if we remove the non-clustered index?  Or in other words, what happens if we ran a query against another table filtering on a column not included in an index key?  To easily demonstrate this we can just remove the non-clustered index.

DROP INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]
BEGIN TRAN
SELECT *
FROM Person.Person
WHERE LastName BETWEEN 'Ware' AND 'Warthen'

We get the same rows back as expected but this time SQL Server has had to use the clustered index to get the data.

So what locks have we got?  This time there is no key-range lock that can be applied, so a shared lock has to placed on the entire table.

SELECT *
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
ORDER BY request_mode
SELECT object_name(1765581328)

Because this shared lock is held until the transaction is closed, this means that no data in the table can be updated until the transaction completes, even though we’ve only read three rows.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)
VALUES (777777, 'VC', 'Simon', 'ZZZZZ')
SELECT *
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
ORDER BY request_mode

This is something you need to be aware of if using the SERIALIZABLE isolation level.

Summary

If a key column is used to filter data in a query then key-range locks can be taken against the keys in that index.  They will be taken against all the keys that satisfy the query plus the sequentially next key outside of the query.  This then prevents data being added that would insert a new key immediately before a key with a key-range lock.

We also saw how to use sys.dm_tran_locks, sys.dm_db_database_page_allocations and DBCC PAGE to work out what resources have locks on them.

We only saw “RangeS-S” key-range locks but there are several more and they can be seen at TechNet.

Advertisement