4

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?

1 Answer 1

8

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.

1
  • 1
    Actually the NOLOCK hint was one of my attempts to fix that didn't work. I missed it when I put the code into the question. Unfortunately joining to sys.schemas and sys.objects only works if I'm pulling data for a single database. The OBJECT_* functions have an additional database_id parameter that I can add. Jan 8, 2014 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.