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