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_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_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?
Because the metadata functions do not obey transaction isolation semantics. If you want to avoid getting blocked, join to
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.