Why does the SQL Server Isolation level revert to default within the same session using EF TransactionScope

Question

I am having an issue with using Entity Framework TransactionScopes.

Upon reading the documentations and viewing multiple examples and suggestions, I have implemented Transaction Scopes on many queries that I have in my Web Application. The issue I am facing here is related to Isolation Levels. I want every query within the TransactionScope to be ReadUncommited, but for some reason, only the first query has to desired Isolation Level (READ UNCOMMITED), but all subsequent queries revert back to READ COMMITED. These queries read a lot of data, and I do not mind dirty reads here.

This is my EF TransactionScope and Context (Very basic):

    var transactionOptions = new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted };
    using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions, TransactionScopeAsyncFlowOption.Enabled))
    {
         using (var db = new Context())
         {
               //db.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");
               //var SessionID = await db.Database.SqlQuery<short>("SELECT @@SPID").FirstOrDefaultAsync();
               //db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

               //QUERY1
               var list1= await db.Table1.Include(x => x.ExternalProperty).Where(x => x.Created >= sevenDaysAgo).ToListAsync();                    

               //QUERY2
               var list2 = await db.Table1.Include(x => x.ExternalProperty).Where(x => x.Created >= fourteenDaysAgo && x.Created <= eightDaysAgo).ToListAsync();

                //... Doing more stuff here

                transactionScope.Complete();
         }
     }

QUERY 1 executes with READ UNCOMMITED, while, for some reason, QUERY 2 executes with READ COMMITED. Am I missing something? Because in my understanding, this should not happen since both queries are withing the same TransactionScope.

I used await db.Database.SqlQuery<short>("SELECT @@SPID").FirstOrDefaultAsync() to get the session ID reserved by the context, to make sure that the same session is being used.

I have also tried to set the Isolation Level manually using: db.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"); which resulted in the same behavior.

I have searched around, and almost all answers suggesting using the above code. For example: THIS ANSWER

Why would this happen, especially since the TransactionScope has not completed yet?

Thanks

On an unrelated note, consider using snapshot isolation instead. You say you don't mind dirty reads, but READ UNCOMMITTED is actually much worse than that -- it can omit or duplicate rows if data movement happens, so your results aren't just potentially out of date but entirely wrong. Don't use it unless you're getting transient results that will be re-queried shortly anyway (in monitoring setups, for example). — Jeroen Mostert
There's no EF TransactionScope. That's a .NET class that can cover multiple databases and providers. It can escalate to a distributed transaction if multiple servers/providers are involved. Use the isolation level you want from the start, or start a nested transaction with the level you want if possible. — Panagiotis Kanavos
And avoid using READ UNCOMMITTED or NOLOCK. This doesn't mean "don't take locks". It means "read dirty data". This won't fix any concurrency issues caused by bad queries or missing indexes. Use Snapshot isolation instead, and fix whatever is causing the delays — Panagiotis Kanavos
@JeroenMostert Thanks for the input. This data is something that will re-queried every few minutes on a regular basis. Its purpose to give rough estimates on some data. READ UNCOMMITED is fine is this case. Otherwise I will use Snapshop Isolation as you said. — M O H
@PanagiotisKanavos Thanks for the information. I understand that this is not part of EF, I only meant to explain my scenario (Meaning how I am using it). Nested transactions might be the way to go. Can you provide an example? — M O H

M O H M O H · Accepted Answer · 2017-10-26T14:01:27

Unless someone has a better answer, here is what solved my issue.

I had to manually open and close the connection within the transactionscope, or order to stop the EF context from returning the connection back to the pool between each query.

I used db.Database.Connection.Open(); and db.Database.Connection.Close();

NOTE that this would keep the connection active until you dispose of the context. Be careful as it may not be proper for your scenario.

Why does the SQL Server Isolation level revert to default within the same session using EF TransactionScope

1 Answers