Lesson Learned #486: Snapshot Isolation Transaction Failed Due to a Concurrent DDL Statement

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.

ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

Then, let's create a sample table.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(50)
);

Let's create two different transactions:

Transaction #1:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

SELECT * FROM Employees;
-- Transaction A remains open

Transaction #2:

BEGIN TRANSACTION;

ALTER TABLE Employees ADD Email NVARCHAR(100);

COMMIT;

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.