3

I am getting the following error (using SQL Server 2012).

Snapshot isolation transaction failed accessing database 'db1' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

This situation is different than what other blogs are suggesting. I am querying a database table (db2.table1) from another database (db1) (...using synonyms). The simplified version of the erroring query is the following.

select col1, col2 
from db1.tab1 t 
inner join db2.table1
where xyz = 'abc'

db1 has the default isolation level (read-committed) and db2 has the isolation level set to Snapshot isolation.

The query above fails 5 out of 100 cases with the above error message.

This query is being executed using Entity Framework 6 and part of other similar queries in the application. Application is dependent upon db2 for all other operations.

Any suggestions are welcome.

NOTE --> I cannot set the isolation level of db1 to snapshot isolation as a resolution.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ash
  • 329
  • 3
  • 10
  • How/why do you expect `SNAPSHOT` isolation to work if not all the databases involved in the transaction allow snapshot isolation? – Dan Guzman Sep 21 '16 at 23:34
  • like I mentioned the same is working 95 out of 100 times... – ash Sep 21 '16 at 23:54
  • 1
    Does db2 enforce snapshot isolation (`READ_COMMITTED_SNAPSHOT`) or merely allow it? If it enforces this, it probably can't be made to work reliably. If it's optional, you may have something like [KB972915](https://support.microsoft.com/en-us/kb/972915) on your hands, though this is pure speculation on my part since I don't know how EF treats this situation. – Jeroen Mostert Sep 22 '16 at 05:45
  • Few transactions are using Snapshot Isolation and whenever the same connection is picked from the pool for the above query, error is raised. Because of this behavior (:as designed:) of picking connections from the pool and not resetting the isolation level, the above problem is so random that it cannot be predicted . Thanks, this looks like the actual problem. – ash Sep 22 '16 at 18:10

1 Answers1

4

Answer

The problem is the way Entity Framework re-uses connections from the connection pool. Lets take the example of 2 queries

  • query1 (a query that is setting TrasactionScope to Snapshot Isolation)

  • query2 (a query that represents cross-database query defined above)

When query1 executes, Entity Framework sets the connection (under which query1 executes) to use Snapshot Isolation level. And the same isolation level remains with the connection till another query explicitly changes the trasaction scope or the connection is recycled. Refer this MSDN article.

5 out of 100 cases, the same connection was being used to execute query2 which resulted in running query2 with the Isolation Level = "Snapshot". Since db1 was not set to use snapshot isolation, the error was received.

One important thing to remember.

  • When Entity Framework reuses a connection from the connection pool, it does NOT reset the Isolation level of the connection object. In order to force to use a specific Isolation level for a context, you need to explicitly set it.

Hope this helps people dealing with similar EF related intermittent errors.

ash
  • 329
  • 3
  • 10