Blocking with OBJECT_NAME()
Tags: tsql, sql, sqlserver
Published: 2020-01-01 12:53:53

---
title: Blocking with OBJECT_NAME()
published: true
date: 2019-12-24 15:38:36 UTC
tags: TSQL, SQL, sqlserver
cover_image: 
canonical_url: https://am2.co/2019/12/blocking-with-object_name/
---

[![](http://am2.co/wp-content/uploads/berkay-gumustekin-ngqyo2AYYnE-unsplash-1024x683.jpg)](https://www.youtube.com/watch?v=bphPp8k9aZo)<figcaption id="caption-attachment-2392">Doggos increase blog readership by 140%. 14/10. They are good doggos.</figcaption>

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](https://docs.microsoft.com/en-us/sql/t-sql/functions/object-name-transact-sql?view=sql-server-ver15) 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](https://github.com/amtwo/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.

```sql
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:

```sql
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:

```sql
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](http://am2.co/2017/10/finding-leader-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:

```sql
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](https://sqlperformance.com/2014/05/t-sql-queries/read-committed-snapshot-isolation):

```sql
--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?

```sql
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:

```sql
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()](https://am2.co/2019/12/blocking-with-object_name/) appeared first on [Andy M Mallon - AM²](https://am2.co).