Export (0) Print
Expand All

Metadata Access, Isolation Levels, and Lock Hints

SQL Server 2005 does not guarantee that lock hints will be honored in queries that access metadata through catalog views, compatibility views, information schema views, metadata-emitting built-in functions.

Internally, the SQL Server 2005 Database Engine only honors the READ COMMITTED isolation level for metadata access. If a transaction has an isolation level that is, for example, SERIALIZABLE and within the transaction, an attempt is made to access metadata by using catalog views or metadata-emitting built-in functions, those queries will run until they are completed as READ COMMITTED. However, under snapshot isolation, access to metadata might fail because of concurrent DDL operations. This is because metadata is not versioned. Therefore, accessing the following under snapshot isolation might fail:

  • Catalog views
  • Compatibility views
  • Information Schema Views
  • Metadata-emitting built-in functions
  • sp_help group of stored procedures
  • SQL Native Client catalog procedures
  • Dynamic management views and functions

For more information about snapshot isolation, see Isolation Levels in the Database Engine.

The following table provides a summary of metadata access under various isolation levels.

Isolation level Supported Honored

READ UNCOMMITTED

No

Not guaranteed

READ COMMITTED

Yes

Yes

REPEATABLE READ

No

No

SNAPSHOT ISOLATION

No

No

SERIALIZABLE

No

No

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft