Andy Mallon shows us that the
OBJECT_NAME() function does not care about your
READ UNCOMMITTED transaction isolation level:
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:
What the… It’s still being blocked by the uncommitted
Read on to understand why, and what you can do about it.