How to get LOCK information and resource name when running a query

Question

Post reply

How to get LOCK information and resource name when running a query

sql-lover

SSCoach

Points: 18530
More actions
September 27, 2012 at 12:53 pm

#262367

Hi,
I am trying to get the lock information for a T-SQL query that I have. It looks it's doing page lock most of the time, but I want more information and able to re-use the DMV on future cases.
Based on an awesome book from Grant Fritchey plus another information I Google, I was able to write this simple T-SQL snippet:
SELECT
DB_NAME(dtl.resource_database_id) AS dbname,
CASE
WHEN resource_type = 'object'
THEN OBJECT_NAME(dtl.resource_associated_entity_id)
ELSE OBJECT_NAME(p.OBJECT_ID)
END AS ObjectName,
dtl.request_session_id,
dtl.resource_associated_entity_id,
dtl.resource_type,
dtl.resource_description,
dtl.request_mode,
dtl.request_status
FROM sys.dm_tran_locks AS dtl
LEFT JOIN sys.partitions AS p ON p.hobt_id = dtl.resource_associated_entity_id
But it's not actually working as expected and I am still confused about few columns.
What I need:
-Ability to see current type of lock: page, database, etc
-Ability to see the specific locked resource's name (not id) . Example, table name, if the lock escalated to that
-Database name (not id)
Pretty much, I think I covered the basics but I am confused with the "resource type" column. What if the lock is a row level, what will be displayed there? Also, I need to run this from MASTER, assuming I don't know the problem or databases and I want a generic solution. I found that if I run on the database, will give more information.
Any comments or help is appreciated!
Thanks in advance,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

sql-lover (9/27/2012)
-Ability to see current type of lock: page, database, etc

You already have that, it's the resource_type columns

-Ability to see the specific locked resource's name (not id) . Example, table name, if the lock escalated to that

That one's complex. The query will already give you that for table locks, the Object Name column. Just change it to use the 2-parameter version of object name with the database id as the second parameter.

Row and page locks are fairly hard to decode within a query.

For database locks, the database name is the first column.

-Database name (not id)

The first column of that query you posted is the database name.

Also, I need to run this from MASTER, assuming I don't know the problem or databases and I want a generic solution. I found that if I run on the database, will give more information.

The query is already that. At most here you need to change the object_name function to use the 2-parameter version.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 2

Thanks for reply.

Yeah, I've spent almost the whole morning trying to write it for "rows".

Got this so far ...

CASE

WHEN resource_type = 'object'

THEN OBJECT_NAME(dtl.resource_associated_entity_id)

ELSE OBJECT_NAME(p.OBJECT_ID)

END AS ObjectName,

But in certain cases, it fails, giving a mathematical error or something like that.

I also got confused when the resource associated entity id is zero, but shows database as object type? does that mean that I the lock went up to a db lock?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

No, it just means that there's a database-level lock. All sessions take a shared database lock, it's what prevents you from dropping an in-use database.

Have you tried google? I'm sure I've seen a locking script that did at least decode the row lock structure and identify the index involved.

Edit: correction, the resource_associated_entity_id is indeed the allocation unit for page, key and Rid locks. Avoids needing to decode the lock resource (which is file_id:page_id for pages, the index key hash for key and file_id:page_id:slot for RIDs)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 4

Thanks!

That's clarifies the db lock or zero there.

I am almost there. Once done, I will post it, maybe it can help someone else ...

sql-lover SSCoach Points: 18530 More actions · Answer 5

What I got so far ...

SELECT request_session_id,

DB_NAME(resource_database_id) AS DatabaseName,

COUNT(*) AS TotalLocksPerType,

request_mode,

request_status,

resource_associated_entity_id,

resource_type AS LockType

FROMsys.dm_tran_locks

WHERE DB_NAME(resource_database_id) = 'DBnameHere' AND resource_type IN ( 'page', 'key', 'object' )

GROUP BY request_session_id,

DB_NAME(resource_database_id),

resource_associated_entity_id,

request_mode,

request_status,

resource_type

No idea how to translate resource_associated_entity_id to an actual name, so it can be easier to read.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

See my edit of the previous post.

You're missing RID locks in your IN.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sql-lover SSCoach Points: 18530 More actions · Answer 7

GilaMonster (9/27/2012)
See my edit of the previous post.
You're missing RID locks in your IN.

You're right!

I'll take a look this morning and fix ...