SQL Server: When records lock, use %%lockres%% to find the page information of records

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

I shared a couple of articles on SQL Server database locking and how to detect a deadlock. Please access few of them.

Different ways to detect a deadlock
What happens after the Deadlock?

I recently found an undocumented function %%lockres%% which we can use to locate the page information of records especially for those records which hold a Lock.

It returns a hash value same as like column “resource_description” of table sys.dm_tran_locks, but the good thing is you can use it in your SELECT statement and can correlate the column resource_description of table sys.dm_tran_locks.

By this way, you can get the exact record, and it’s page info which currently held by a Lock.

Let me demonstrate this,
Prerequisites:
Use below article and generate a sample deadlock situation in your database server.

SQL Server: T-SQL script to generate a DeadLock in a Database

Check the result of below query and note down the result of column “resource_description”:

Use %%lockres%% and SELECT both the tables:
You can easily correlate the result of sys.dm_tran_locks column resource_description and the result of %%lockres%%. You can find an exact record which held by a Lock.

After this exercise, Please COMMIT all your open transactions.