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:
- 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.
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'
or you can check the database active options with:
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 :
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.