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.