2

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

M O H
  • 284
  • 3
  • 15
  • 1
    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 Oct 26 '17 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 Oct 26 '17 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 Oct 26 '17 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 Oct 26 '17 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 Oct 26 '17 at 14:14
  • @MOH I'd suggest you *don't* use this isolation level at all. 99.9995% of the time it means there's a bug or bad query. Fix them – Panagiotis Kanavos Oct 26 '17 at 14:16
  • @PanagiotisKanavos The query takes a few seconds to complete. It scans through millions of records, and returns data between datetime ranges. I would say that it runs decently. Unless we have to talk about datetime indexing and other performance tweaks, which is not the subject of this post. Note that the queries in the question are just dummies. – M O H Oct 26 '17 at 14:19
  • Putting a `SELECT` in a transaction that's running under `READ UNCOMMITTED` does nothing, by the way -- there's no point to the transaction, nor to grouping multiple `SELECT`s that way. Assuming EF has no other, more elegant way of indicating the isolation level (I don't know, I have no experience with EF) you may as well execute both statements in their own `TransactionScope`s. It will have no impact on speed or correctness, though it's slightly more verbose. – Jeroen Mostert Oct 26 '17 at 14:21
  • @JeroenMostert The default isolation level for the db I am working on READ COMMITTED with Snapshot Isolation OFF. I have experience some blocking since the table I am reading from has 10-30 new rows every second, as well as updates. Since the query I require does not care about the changeable data within the row, READ UNCOMMITED would prevent lock since each query takes around 3-7 seconds. – M O H Oct 26 '17 at 14:30
  • You misunderstand -- I meant to say that it's unnecessary to put both statements in the same transaction (where the isolation level getting reset due to connection pool recycling comes into play), not that the isolation level change does nothing. In other words, having one `TransactionScope` per query (each of which running under `READ UNCOMMITTED`) would trivially fix the problem, at the cost of more code. – Jeroen Mostert Oct 26 '17 at 14:32
  • @JeroenMostert I see. It makes sense. Thank you. And now I know why the Isolation level is changing thanks to you. "isolation level getting reset due to connection pool recycling comes into play." – M O H Oct 26 '17 at 14:34
  • It is, incidentally, [by (unfortunate) design](https://connect.microsoft.com/SQLServer/feedback/details/243527/sp-reset-connection-doesnt-reset-isolation-level) that the isolation level is reset as a result of the connection going through the connection pool. Microsoft actually "fixed" this in SQL Server 2014, only to have to revert this when they realized it broke other scenarios instead. I'm not sure why they haven't implemented a fix by making `SqlConnection` explicitly set the isolation level again from the scope when it enlists, possibly also backwards compat issues. – Jeroen Mostert Oct 26 '17 at 14:37
  • Wait, no, I'm confusing things. :-) The issue was that `sp_reset_connection` does *not* reset the isolation level, which is in fact exactly what you want in this scenario (but does cause problems elsewhere). Not sure why it's not working as designed for this case, where you actually want it to work this way. (Unless you're using an old version of SQL Server 2014, which includes the "fix".) – Jeroen Mostert Oct 26 '17 at 14:41

1 Answers1

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.

M O H
  • 284
  • 3
  • 15
  • 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 – Panagiotis Kanavos Oct 26 '17 at 14:11
  • @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. – M O H Oct 26 '17 at 14:16