SQL Server error | Snapshot Isolation related

Question

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.

Answer 1

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.