EF6 with TransactionScope - IsolationLevel.ReadUncommitted but got ReadCommitted first

c# entity-framework-6 read-uncommitted sql-server transactionscope

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())
{
            var data = from _Contact in db.Contact where _Contact.MemberId == 13 select _Contact; // large results
            for (var item I data)
                  item.Flag = 0;
            db.SaveChanges(); // Probably db lock
}

After

using (var scope =
    new TransactionScope(TransactionScopeOption.RequiresNew,
    new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
    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
    }
}

We use SQL profiler to confirm. 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 using EF and TransactionScope ? Thanks.

1
5
1/21/2017 11:46:07 AM

Accepted Answer

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.

3
1/23/2017 5:20:37 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow