3

I'm using Red Gate SQL Compare to create a release script based on differences between SVN and a database. This results in a script containing a bunch of table- and procedure-changes and it works fine. However, one thing puzzles me, it's using transaction isolation level serializable.

I know what it does to dml-statements, but I'm not sure what it means for ddl. Can someone enlighten me, perhaps with an example?

4

I believe it will mean the same thing for DDL as it does for DML. The msdn article on the topic actually gives you a pretty clear idea under the SERIALIZABLE section:

This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Basically as long as your transaction is running, no DDL can be performed on any of the objects you directly or indirectly reference.

| improve this answer | |
  • So basically no other tables or procedures can be created/changed/dropped when "my" script is running? – Andreas Ågren Feb 16 '12 at 15:00
  • They can, just not anything relating to any objects that you reference. If you query a table, then move on to another table in the same query, you can't change that first table til the whole thing is complete. – JNK Feb 16 '12 at 15:03
  • What happens technically? Is it as simple as/comparable to a set transaction isolation level serializable; select * from sys.sysobject where object_id = <the objects I'm changing>? (Please note, hightly simplified ;) – Andreas Ågren Feb 16 '12 at 15:09
  • Locks are issued on any object that you interact with and not released until the entire transaction completes. – JNK Feb 16 '12 at 15:10
  • To me it sounds like repeatable read and serializable have the same meaning for DDL. Because, what would count as "phantom rows" in a DDL statement? – Andreas Ågren Feb 16 '12 at 20:51
5

I know what it does to dml-statements, but I'm not sure what it means for ddl.

Generally speaking, not much.

DDL encompasses a huge range of operations (many of which have their own special behaviours), but most DDL will take a schema modification (Sch-M) lock on the underlying object, either for the duration of the command, or at a critical stage (e.g. at the end of most online index builds). This is the most restrictive type of lock, preventing any sort of concurrent access, at any isolation level.

Even where Sch-M is not taken, the engine is careful to take the right locks at the appropriate times to ensure the end result is correct. For example, it is not possible to build an index that does not exactly reflect the underlying data.

All of the above applies regardless of any session isolation level specified by the user.


As far as changes to metadata (i.e. the system structures underlying views like sys.tables) are concerned, while SQL Server ensures correct changes automatically, user access is only fully supported and guaranteed to be honoured at the default locking READ COMMITTED isolation level.

Quoting from Metadata Access, Isolation Levels, and Lock Hints (emphasis added):

SQL Server 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 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 Server Native Client catalog procedures
  • Dynamic management views and functions

From the same page:

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

Isolation level support for metadata access

Despite not being guaranteed or supported, it can be important to access metadata (e.g. catalog views, DMVs) under READ UNCOMMITTED isolation to avoid blocking issues. Most tools and diagnostic scripts make heavy use of READ UNCOMMITTED isolation (often using the synonymous but misleadingly-named NOLOCK hint). For an example, see Glenn Berry's Diagnostic Queries.

As an example of READ UNCOMMITTED access not being honoured, see Metadata functions should follow same isolation semantics as metadata queries on Connect and Bad habits : Using (certain) metadata "helper" functions by Aaron Bertrand.

Accessing metadata at an isolation higher than read committed is not even honoured - the information will be read with READ COMMITTED isolation semantics.


If I had to guess, I would say the comparison tool in the question sets isolation to SERIALIZABLE to cover any DML in the generated script.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.