Blocking with OBJECT_NAME()

As a DBA, you probably use the OBJECT_NAME() function quite a bit. I know I do. If you aren’t familiar with it, you can read the documentation if you want, but it’s a pretty straightforward function that converts object IDs into object names.

I ran into a scenario where one of my scripts in my stored procedures in my DBA Database was being blocked, and it turned out that using OBJECT_NAME() was the culprit.

Here’s a script to reproduce the issue. In one query window, we’re going to create a table inside a transaction, then we’re going to get the object_id for the table we just created.

CREATE DATABASE AM2_Wut;
GO 
BEGIN TRAN;
CREATE TABLE dbo.Blep (foo bit); 
SELECT OBJECT_ID(N'dbo.Blep');

On my laptop, I get an object_id of 581577110. That will probably be different for you…but I’ll use this object_id in the next couple of queries.

Now, in a second query window, lets try to convert that object_id back into an object_name:

USE AM2_Wut; 
GO 
SELECT OBJECT_NAME(581577110);

That’s just going to sit there and wait, and wait, and wait, and wait. Because the table was created inside a transaction, the metadata about the table is uncommitted, and thus unavailable to the second session. The result is that my second session waits & waits & waits until the first session is committed (or rolled back).

OK, so it’s being blocked due to an uncommitted transaction. We could try doing dirty reads, right? I’ll kill my second session that’s been blocked, and I’ll throw in SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;. That’s like just like using a nolock hint:

USE AM2_Wut; 
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT OBJECT_NAME(581577110);

What the… It’s still being blocked by the uncommitted CREATE TABLE. This is a very contrived example. In reality, you wouldn’t be doing a simple SELECT OBJECT_NAME() quite like this. But if you’re a DBA who frequently queries DMVs, you might be doing something pretty similar.

Lets go back to the scenario that got me here. We were seeing a bunch of blocking in production. I was trying to determine what the leading blocker was doing, so I was querying the sys.dm_tran_locks DMV.

With that open transaction still dangling in one session, try running this in your second session. This is similar to the one I was trying to run when I encountered this problem. Guess what? It hangs, just like the other two:

USE AM2_Wut; 
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT object_name(resource_associated_entity_id) 
FROM sys.dm_tran_locks l
WHERE resource_type = 'OBJECT' 
AND request_mode = 'Sch-M';

Let’s go back to that first window, with our open transaction, and start over. This time, we’ll create the database and enable RCSI:

--Clean up the database we made 
ROLLBACK; 
USE master; 
DROP DATABASE AM2_Wut; 
GO 
--and recreate it again with RCSI enabled 
CREATE DATABASE AM2_Wut; 
GO 
ALTER DATABASE AM2_Wut
SET READ_COMMITTED_SNAPSHOT ON; 
GO 
USE AM2_Wut
GO 
--And now create the table in a transaction 
BEGIN TRAN;
CREATE TABLE dbo.Blep (foo bit); 
SELECT OBJECT_ID(N'dbo.Blep');

Now, we’ll try querying sys.dm_tran_locks again from the second window. Will RCSI allow the query to return results instead of being blocked?

SELECT object_name(resource_associated_entity_id) 
FROM sys.dm_tran_locks l 
WHERE resource_type = 'OBJECT' 
AND request_mode = 'Sch-M';

Nope. Blocked again. This is because the metadata functions don’t obey the transaction isolation semantics.

If I want to use READ UNCOMMITTED to do a dirty read, I’ll actually need to join to sys.objects to find out the name of that table:

USE AM2_Wut 
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
SELECT o.name 
FROM sys.dm_tran_locks l 
LEFT JOIN sys.objects o ON l.resource_associated_entity_id = o.object_id 
WHERE resource_type = 'OBJECT' 
AND request_mode = 'Sch-M';

There is one really neat feature in the object_name() function that I didn’t make reference to in my above examples. You can pass in a database_id as a parameter: object_name(object_id, database_id). This is handy because it prevents you from having to join to sys.objects in multiple databases in order to decode the object_id from many databases.

Hence, it becomes a trade-off. If you want to have simpler, easier to read code, you can use object_name() and other metadata functions. If you want your monitoring/utility queries to obey your READ UNCOMMITTED isolation level, you’ll have to write the more complicated code. And if you’re dealing with locks in multiple (or unknown) databases, you’d have to switch to using dynamic SQL to generate the proper query using three-part names to reference dbname.sys.objects.

The post Blocking with OBJECT_NAME() appeared first on Andy M Mallon - AM².