Snapshot isolation transaction fails when querying metadata

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

 

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. 
 
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 are 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.

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.