How to find the physical location of locked records in SQL Server

SQL Server by default locks the table or page or rows when it executes the transact statement. There are different types of locks such as exclusive, shared, schema modification lock etc., Depending on the statement it executes, SQL Server decides to hold the lock on table or page or even more granular level on a specific row. The behavior of how the lock is managed by SQL Server depends on various factors such as isolation level, query it executes etc.,

This post is to show how the locked records can be identified when the query is being executed.  A table has been created and inserted few records for this demonstration as shown in the following query.

CREATE TABLE dbo.Customer
(
CustomerId INT, 
CustomerName VARCHAR(10), 
[Address] NVARCHAR(100)
)
GO
INSERT INTO dbo.Customer
VALUES(1, 'Rob', N'Keller'),(2,'May', 'Quorum Dr'),(3,'Pop','Belt line')

DMV “sys.dm_tran_locks” returns information about locks which are taken or waiting for the resources. It returns lock information only for the active queries, in other words, you cannot be able to check the lock information for the completed statements.

BEGIN TRAN
	UPDATE dbo.Customer SET [Address] = 'Kellers Spring' 
	WHERE CustomerId = 1
	SELECT * FROM sys.dm_tran_locks 
COMMIT TRAN 

This statement updates the address of the customer whose Id is one and returns the lock information for the update query. Note that the lock is held until the statement commits. The dynamic management view returns the lock information about this query since the update query is still active and neither commit nor rollback.  The result of the DMV is shown in the below image.

The highlighted section in resource_description column shows that the lock is taken on the 0^th row of page 568 in file 1.

The same resource description can be retrieved directly from the actual table using the undocumented virtual column %%lockres%%.

SELECT *, %%LOCKRES%% AS datalocation FROM dbo.Customer

The %%LOCKRES%% returns file number, page number and slot number for each row when the table is HEAP, it returns the KeyHashValue when the table is BTREE structure in other words clustered index is created.

Let’s create a clustered index based on the customer Id column and execute the update statement.

CREATE CLUSTERED INDEX ix_CustomerId_customer ON dbo.Customer(CustomerId)
BEGIN TRAN
	UPDATE dbo.Customer SET [Address] = 'Kellers Spring Rd' 
	WHERE CustomerId = 1
	SELECT * FROM sys.dm_tran_locks 
COMMIT TRAN 

The highlighted section in the below image now shows the lock is taken on the KEY (de42f79bc795)

Let’s cross verify by running the same select query used above, now it returns KeyHashValue for each row of the table as shown in the below image.

%%LOCKRES%% is a virtual column and it is undocumented. It returns the physical location of each and every row when used in the SELECT statement.  It is very useful for identifying actual records that are involved in blocking or deadlock.