Online index maintenance in SQL Server using SNAPSHOT isolation level

Question

I have recently altered our SQL Server 2008 R2 database to enable SNAPSHOT isolation level and made appropriate changes to hibernate to run all transactions in SNAPSHOT mode. I have however noticed that when I add/delete/alter indexes, the queries which are accessing the underlying table are rolled back and I get this exception:

Snapshot isolation transaction failed in database 'foo' 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

...which is exactly what has happened.

According to https://msdn.microsoft.com/en-us/library/bb933783(v=sql.105).aspx "These statements are permitted when you are using snapshot isolation within implicit transactions." So I tried:

set implicit_transactions on
GO

DROP INDEX blabla
GO

IF @@TRANCOUNT > 0 COMMIT TRAN set implicit_transactions off

But the problem still persists. There must be a way to do this in SQL Server?

Answer 1

The problem is in your application layer. You are starting explicit snapshot transactions before doing DDL. You have to modify the app logic to be smarter about its intent. If about to execute DDL then you must either a) use read committed isolation transaction or b) don't start a transaction and let the DDL use an implicit transaction.

Looks like you used a carpet bombing approach and modified your app to use snapshot isolation everywhere. There is no pixie dust, you'll have to make sure you choose the correct isolation/transaction mode on a case by case basis, in your code.