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

Question

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?

Answer 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:

http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=432497

Closed as Won't Fix.