Why am I getting blocked when I call OBJECT_NAME(object_id, database_id) from sys.dm_tran_locks?

by Kenneth Fisher   Last Updated January 12, 2018 01:06 AM

I'm working on a query to tell what objects have locks from a given session and I'm running into some blocking issues.

Here is my basic query:

SELECT request_session_id AS session_id, 
    request_owner_id AS transaction_id,
    OBJECT_SCHEMA_NAME(resource_associated_entity_id, resource_database_id),
    OBJECT_NAME(resource_associated_entity_id, resource_database_id),
    COUNT(1) AS lock_count
FROM sys.dm_tran_locks WITH (NOLOCK)
WHERE resource_type = 'OBJECT'
GROUP BY request_session_id, request_owner_id, 
    resource_database_id, resource_associated_entity_id

When I run this query I am occasionally blocked by one of the sessions actually holding the locks. If I remove OBJECT_NAME and OBJECT_SCHEMA_NAME then I don't have any problems. I've tried dumping the information into a table and then using the functions on the values in the table with similar problems.

This leads me to believe the problem is in the OBJECT_NAME and OBJECT_SCHEMA_NAME functions but I'm not sure why or how to get around it. I'm also not certain why I am sometimes blocked and sometimes not. Does anyone have any suggestions?

Answers 1

Because the metadata functions do not obey transaction isolation semantics. If you want to avoid getting blocked, join to sys.schemas and sys.objects instead of using the metadata functions. This will also allow you to set the isolation level in a single statement instead of peppering NOLOCK hints all over the query...

This was reported by Adam Machanic on Connect several versions ago:


Closed as Won't Fix.

Aaron Bertrand
Aaron Bertrand
January 08, 2014 18:31 PM

Related Questions

LCK_M_S causing blocking with session id < 50

Updated January 06, 2017 08:02 AM

Log Flushes/Sec very high

Updated October 02, 2017 15:06 PM

Lots of short term blockings on SQL DB

Updated January 25, 2018 21:06 PM