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
– Jeroen Mostert, Commented Oct 26, 2017 at 14:07
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
– Panagiotis Kanavos, Commented Oct 26, 2017 at 14:07
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
– Panagiotis Kanavos, Commented Oct 26, 2017 at 14:09
@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
– M O H, Commented Oct 26, 2017 at 14:11
@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, Commented Oct 26, 2017 at 14:14

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

0

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.

answered Oct 26, 2017 at 14:01

M O H

2843 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Panagiotis Kanavos Over a year ago

If you used READ UNCOMMITTED due to blocking, keeping the connection open will make things a LOT worse for other connections. READ UNCOMMITED doesn't mean "don't take locks", it means "ignore others' locks and read their dirty data". Locks are release when a transaction or connection closes. Keeping the transaction and connection open, will cause a LOT more blocking

M O H Over a year ago

@PanagiotisKanavos I agree that keeping the connection open is not a good idea. Which I did not say that I am doing. I am making sure the connection is closed after this is done. Note that this is the only place where I have this setup, and can be only called from one instance. This is not a public method, so only one session will be used, not many.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related