EF6 with TransactionScope - IsolationLevel.ReadUncommitted but got ReadCommitted first

Question

There is a performance and lock issue when using EF for a update-from-query case on MSSQL 2008. So I put ReadUncommitted transaction isolationlevel, hoping to resolve it, like this,

Before

using (MyEntities db = new MyEntities())
{
    // large dataset
    var data = from _Contact in db.Contact where _Contact.MemberId == 13 select _Contact;
    for (var item in data)
          item.Flag = 0;

    // Probably db lock      
    db.SaveChanges(); 
}

After

using (var scope =
    new TransactionScope(TransactionScopeOption.RequiresNew,
    new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
    using (MyEntities db = new MyEntities())
    {
        // large dataset but with NOLOCK
        var data = from _Contact in db.Contact where _Contact.MemberId == 13 select _Contact;
        for (var item in data)
              item.Flag = 0;

        // Try avoid db lock      
        db.SaveChanges();
    }
}

We use SQL profiler to trace. However, got these scripts in order, (Expect read-uncommitted for the 1st script.)

Audit Login

set transaction isolation level read committed

SP:StmtStarting

SELECT 
 [Extent1].[ContactId] AS [ContactId], 
 [Extent1].[MemberId] AS [MemberId], 
FROM [dbo].[Contact] AS [Extent1]
WHERE [Extent1].[MemberId] = @p__linq__0

Audit Login

set transaction isolation level read uncommitted

Though I could resend this request and make it right order (will show read-uncommitted for the following requests, same SPID), I wonder why it sent read-uncommitted command after read-committed command and how to fix by using EF and TransactionScope ? Thanks.

Answer 1

According to the following note in the ADO.NET documentation Snapshot Isolation in SQL Server, the Isolation Level is not bound to the Transaction Scope as long as the underlying connection is pooled:

If a connection is pooled, resetting its isolation level does not reset the isolation level at the server. As a result, subsequent connections that use the same pooled inner connection start with their isolation levels set to that of the pooled connection. An alternative to turning off connection pooling is to set the isolation level explicitly for each connection.

Thus I conclude that until SQL Server 2012, setting the isolation to any other level than ReadCommitted requires to either turn of connection pooling when creating the questionable SqlConnection or to set the Isolation Level in each connection explicitly to avoid unexpected behavior, including deadlocks. Alternatively the Connection Pool could be cleared by calling the ClearPool Method, but since this method is neither bound to the Transaction Scope nor the underlying connection, I don't think that it's approriate when several connections run simultaneously against the same pooled inner connection.

Referencing the post SQL Server 2014 reseting isolation level in the SQL forum and my own tests, such workarounds are obsolete when using SQL Server 2014 and a client driver with TDS 7.3 or higher.

Answer 2

I think a better solution is to perform update by generating a direct query (not selection and update entity by entity). In order to work with objects and not queries, you can use EntityFramework.Extended:

db.Contact.Update(C => c.MemberId == 13, c => new Contact { Flag = 0 });

This should generate something like UPDATE Contact SET Flag = 0 WHERE MemberId = 13 which is much more faster than your current solution.

If I remember correctly, this should generate its own transaction. If this must be executed in a transaction with other queries, `TransactionScope can still be used (you will have two transactions).

Also, isolation level can remain untouched (ReadCommitted).

[EDIT]

Chris's analysis shows exactly what happens. To make it even more relevant the following code shows the difference inside and outside of TransactionScope:

using (var db = new myEntities())
{
    // this shows ReadCommitted
    Console.WriteLine($"Isolation level outside TransactionScope = {db.Database.SqlQuery(typeof(string), selectIsolationLevel).Cast<string>().First()}");
}

using (var scope =
    new TransactionScope(TransactionScopeOption.RequiresNew,
    new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
    // this show ReadUncommitted
    Console.WriteLine($"Isolation level inside TransactionScope = {db.Database.SqlQuery(typeof(string), selectIsolationLevel).Cast<string>().First()}");

    using (myEntities db = new myEntities ())
    {
        var data = from _Contact in db.Contact where _Contact.MemberId == 13 select _Contact; // large results but with nolock

        for (var item I data)
              item.Flag = 0;
        db.SaveChanges(); // Try avoid db lock
    }

    // this should be added to actually Commit the transaction. Otherwise it will be rolled back
    scope.Complete();
}

Coming back to the actual problem (getting deadlocks), if we take a look of what Profiler is outputting during the whole thing, we see something like this (removed GOs):

BEGIN TRANSACTION 
SELECT <all columns> FROM Contact 
exec sp_reset_connection

exec sp_executesql N'UPDATE Contact
    SET [Flag] = @0
    WHERE ([Contact] = @1)
    ',N'@0 nvarchar(1000),@1 int',@0=N'1',@1=1

-- lots and lots of other UPDATEs like above

-- or ROLLBACK if scope.Complete(); is missed
COMMIT

This has two disadvantages:

1. Many round-trips - many queries are issued against the database, which puts more pressure on database engine and also takes much longer for the client

2. Long transaction - long transactions should be avoid as a tentative of minimizing deadlocks

So, the suggested solution should work better in your particular case (simple update).

In more complex cases, changing the isolation level might be needed.

I think that, if one deals with large processing of data (select millions, do something, update back etc.) a stored procedure might be the solution, since everything executes server-side.

Answer 3

I think this is a red herring caused by relying on the Audit Login Event. This is not showing the moment when client tells server 'set transaction isolation level read uncommitted'. It is showing you what the isolation level is later on, when that connection is picked out of the pool and reused.

I verify this by adding Pooling=false to my connection string. Then, audit login always shows transaction isolation level read committed.

I have so far found no way, in SQL Profiler, of seeing the moment when EF sets the transaction level, nor any explicit begin tran.

I can kind of confirm that it is being set somewhere, by reading and logging the level:

    const string selectIsolationLevel = @"SELECT CASE transaction_isolation_level  WHEN 0 THEN 'Unspecified'  WHEN 1 THEN 'ReadUncommitted'  WHEN 2 THEN 'ReadCommitted'  WHEN 3 THEN 'Repeatable'  WHEN 4 THEN 'Serializable'  WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL  FROM sys.dm_exec_sessions  where session_id = @@SPID";

    static void ReadUncommitted()
    {
        using (var scope =
            new TransactionScope(TransactionScopeOption.RequiresNew,
            new TransactionOptions{ IsolationLevel = IsolationLevel.ReadUncommitted }))
        using (myEntities db = new myEntities())
        {
            Console.WriteLine("Read is about to be performed with isolation level {0}", 
                db.Database.SqlQuery(typeof(string), selectIsolationLevel).Cast<string>().First()
                );
            var data = from _Contact in db.Contact where _Contact.MemberId == 13 select _Contact; // large results but with nolock

            foreach (var item in data)
                item.Flag = 0;

            //Using Nuget package https://www.nuget.org/packages/Serilog.Sinks.Literate
            //logger = new Serilog.LoggerConfiguration().WriteTo.LiterateConsole().CreateLogger();
            //logger.Information("{@scope}", scope);
            //logger.Information("{@scopeCurrentTransaction}", Transaction.Current);
            //logger.Information("{@dbCurrentTransaction}", db.Database.CurrentTransaction);

            //db.Database.ExecuteSqlCommand("-- about to save");
            db.SaveChanges(); // Try avoid db lock
            //db.Database.ExecuteSqlCommand("-- finished save");
            //scope.Complete();
        }
    }

(I say ‘kind of’ because the statements each run in their own session)

Perhaps this is a long way of saying, yes EF transactions work correctly even if you can't prove it via Profiler.