2

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?

2

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.

  • But the DDL mentioned in the question is run in sql server management studio (a scheduled job for example to rebuild or reorganize indexes), not through my application, and I'm not explicitly starting the transaction in snapshot in management studio. I'm assuming it's using the default read committed there. – infiniteLoop Dec 4 '15 at 13:57
  • Yes, you are starting a transaction. Read what SET IMPLICIT_TRANSACTION ON does. Is the opposite of what you believe it does. – Remus Rusanu Dec 4 '15 at 14:16
  • I am starting a transaction, but since I'm not specifying SET TRANSACTION ISOLATION LEVEL SNAPSHOT at the beginning of the transaction, it is running with the default READ COMMITTED isolation. So I am doing what you suggest in point "a". Even when I remove the implicit transaction bit I get the same error. – infiniteLoop Dec 4 '15 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.