This article is contributed. See the original author and article here.

 


When a snapshot isolation transaction accesses object metadata that has been modified in another concurrent transaction, it may receive this error:


 


“Snapshot isolation transaction failed in database ‘%.*ls’ because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.”

 

This error can occur if you are querying metadata under snapshot isolation and there is a concurrent DDL statement that updates the metadata that is being accessed under the snapshot isolation. SQL Server does not support versioning of metadata. For this reason, there are restrictions on what DDL operations can be performed within an explicit transaction running under snapshot isolation. An implicit transaction, by definition, is a single statement which makes it possible to enforce the semantics of snapshot isolation even with DDL statements.

 

The following DDL statements are not permitted under snapshot isolation after a BEGIN TRANSACTION statement:



  • ALTER TABLE

  • CREATE INDEX

  • CREATE XML INDEX

  • ALTER INDEX

  • DROP INDEX

  • DBCC REINDEX

  • ALTER PARTITION FUNCTION

  • ALTER PARTITION SCHEME


In addition, any common language runtime (CLR) DDL statement is subjected to the same limitation.


 

Note: The above statements are permitted when you are using snapshot isolation within implicit transactions.

 

An implicit transaction, by definition, is a single statement which makes it possible to enforce the semantics of snapshot isolation even with DDL statements.


 


Queries that run on read-only replicas are always mapped to the snapshot transaction isolation level. Snapshot isolation uses row versioning to avoid blocking scenarios where readers block writers.


 


To resolve the above error, the workaround is to change the snapshot isolation level to a non-snapshot isolation level such as READ COMMITTED before querying metadata.


 


In Azure SQL, snapshot isolation is enabled and the default transaction isolation level is READ COMMITTED SNAPSHOT. To check the default values, you can run the following T-SQL:


 

CREATE DATABASE TestDB(EDITION = 'BASIC', MAXSIZE = 2GB)

SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'TestDB'

 


 1.PNG


 


or you can check the database active options with:


 

DBCC useroptions

 


 2.PNG


 


If you need to change it to read committed to resolve the error described above, check the below steps:


 

ALTER DATABASE TestDB SET READ_COMMITTED_SNAPSHOT OFF

ALTER DATABASE TestDB SET ALLOW_SNAPSHOT_ISOLATION OFF

SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'TestDB'

 


The result is :


3.PNG


 


Azure SQL supports two transaction isolation levels that use row versioning: Read Committed Snapshot and Snapshot isolation level. To read more about this, please check the Database Engine Isolation Levels.

 


The behavior of READ COMMITTED depends on the  READ_COMMITTED_SNAPSHOT database option setting:




  • If READ_COMMITTED_SNAPSHOT is set to OFF , SQL engine will use shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. 




  • If READ_COMMITTED_SNAPSHOT is set to ON (the default on Azure SQL Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.





Note: Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. For more details, see the Transaction Locking and Row Versioning Guide.



Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.