How to Identify Which Row is Locked in table??

I am sure most of us know how to see the information about locked table in SQL server but when we ask which row is locked and what data is present in that row then it can confused some of us. But believe me its very easy to find out and I promise in next 60 seconds you will also know how to get that information.

Lets create a sample database and then will create a scenario to explain the things.

Use below script to create a Sample Database.

CREATE DATABASE LOCKINFO

GO

Create Table in this database as Test1:

USE LOCKINFO

GO

CREATE TABLE TEST1 (ID INT IDENTITY PRIMARY KEY, NAME CHAR(200));

Go

insert into Test1 values ('Vimal'),('Kumar'), ('Prajapati')

Go

Select * from Test1;

Now update the record of ID=2 as below:

Begin Tran

Update Test1 set name ='Adam'

where id =2

Now open a new session in sql server and select  the data as below:

Select * from Test1 where ID =2

This query will go in wait state as there is one update transaction in another session which has not yet completed. Now using sys.dm_tran_locks view we can see the lock related information as below:   

The View sys.dm_tran_locks : Returns information about currently active lock manager resources in SQL Server. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

You can get database id by: select db_id('db_name'). In our case its 'LockInfo'

We can see the column resource description. The select query is waiting for the resource (61a06abd401c).This is Hash value of key for which our query is waiting to get executed. Now using this value we will try to find the row against it.

From here we can use DBCC Page or  %%lockres%% to examine the same.

Let see how to get the information about that row using page. If we look at the output of sys.dm_tran_locks the value against resource type PAGE is 1:154 in resource_description column. we can explore this page using DBCC page. Then we will search for hash key (61a06abd401c) in that page dump. We can see the exact row in output as below:

Using DBCC Page

Use LockInfo

GO

DBCC Traceon (3604)

DBCC Page(52,1,154,3)

So from screen shot it is clear that id=2 from table Test1 is locked.

Second way to find out the same is to use undocumented value  %%Lockres%% as below:

select * from test1 with(nolock) where  %%lockres%%  ='(61a06abd401c)' 

As table "Test1" is locked so we need to use nolock to select the data.

So from above screen shot it is clear that the row with id=2 is locked.

If your table is a heap then using DBCC Page you can identify the locked row.

 Thanks For Reading This Blog. 

 Your Comments/Suggestions are  welcome!!

References: Sql Server Internals 2008