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”:
1 |
select *from sys.dm_tran_locks |
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.
1 2 3 4 5 |
SELECT %%lockres%% AS RecordPageInfo,* FROM tbl_SampleTable_A SELECT %%lockres%% AS RecordPageInfo,* FROM tbl_SampleTable_B |
After this exercise, Please COMMIT all your open transactions.
Leave a Reply