This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
Today, we worked on a service request that our customer faced the following error message: Msg 3961, Level 16, State 1, Line 4 Snapshot isolation transaction failed in database 'DbName' 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.
What is Snapshot Isolation?
Snapshot isolation allows transactions to read consistent versions of the data without blocking other transactions. However, this isolation level does not version metadata changes, such as table definitions, indexes, etc. Therefore, if a snapshot isolation transaction encounters a metadata change, it can lead to inconsistencies.
Example
First, let's enable snapshot isolation in our database.
Then, let's create a sample table.
Let's create two different transactions:
Transaction #1:
Transaction #2:
When trying to access the Employees table again in Transaction #1, the mentioned error will occur. Msg 3961, Level 16, State 1, Line 4
Snapshot isolation transaction failed in database 'DbName' 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.
Solution and Best Practice
To avoid this error, it is recommended to avoid performing DDL operations in transactions that use snapshot isolation. If DDL operations are unavoidable, consider using a different isolation level or designing your application to minimize concurrency between DDL operations and long-running transactions.