Home Dashboard Directory Help

Metadata functions should follow same isolation semantics as metadata queries by Adam Machanic


Status: 

Closed
 as Won't Fix Help for as Won't Fix


26
1
Sign in
to vote
Type: Suggestion
ID: 432497
Opened: 4/14/2009 12:51:55 PM
Access Restriction: Public
0
Workaround(s)
view

Description

Metadata functions such as OBJECT_ID ignore transaction isolation settings, always acting in READ COMMITTED mode. Queries against the metadata catalog, on the other hand, do honor isolation settings, and in cases where blocking may be an issue (e.g. administrative scripts) the functions are rendered useless. The functions should follow the same rules as the catalog views and provide non-locking semantics if the isolation level is set to READ UNCOMMITTED, in order to allow users to more easily create robust administrative scripts.

Example:

--window #1--
use tempdb
go

begin tran

create table x (x int)
go
----------------


--window #2--
use tempdb
go

--this SHOULD work... but doesn't
set transaction isolation level read uncommitted
go

select OBJECT_ID('x')
go
----------------
Details
Sign in to post a comment.
Posted by Microsoft on 3/24/2011 at 12:30 PM
Hello Aaron,

Thank you for submitting this suggestion, but we're trying to clean house and remove items we feel we will likely not address given their priority relative to other items in our queue. We believe it is unlikely that we will address this suggestion, and so we are closing it as “won’t fix”.

This cleaning will help us focus on the high-priority items that we feel need to get done, and we hope that it help provide better clarity to you about the issues we will (and won't) address.

--
Umachandar, SQL Programmability Team
Posted by Adam Machanic on 4/24/2009 at 1:06 PM
One example would be when evaluating a blocking issue. Generally we'll want to find out what object is locked -- so maybe the OBJECT_ID function was a bad example. OBJECT_NAME would have been more appropriate per my goals. Anyway, maybe the blocking object has been exclusively locked by a session, in which case OBJECT_NAME is useless; we have to use sys.objects with a NOLOCK hint instead.
Posted by Microsoft on 4/24/2009 at 10:47 AM
Thank you for reporting the issue. We will look into why there is a discrepancy in behavior. But I am curious why you use read uncommitted in your example. What is it that you are trying to do?
Sign in to post a workaround.