EF6 with TransactionScope - IsolationLevel.ReadUnCommitted, but first got ReadCommitted

Question

When using EF for the case of update-from-query on MSSQL 2008. there is a problem with performance and locking. So I put ReadUncommitted transaction isolation level, hoping to allow it, for example,

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 the SQL profiler for validation. However, these scenarios have been streamlined, ( Expect non-fixed-reading the first script.)

Entry audit

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

Entry audit

set transaction isolation level read uncommitted

( read-uncommitted , SPID), , read-uncommitted EF TransactionScope? .

Answer 1

, , . , , , . , , .

, Pooling=false . , .

SQL Profiler , EF , begin tran.

, - , :

    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();
        }
    }

( "- , , )

, , EF- , Profiler.

Answer 2

ADO.NET SQL Server , :

, reset . , , , . - .

, , , SQL Server 2012, , ReadCommitted, SqlConnection, , , . , ClearPool, , , , approriate, .

SQL Server 2014 SQL , SQL Server 2014 TDS 7.3 .

Answer 3

, - , ( ). , , EntityFramework.Extended:

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

UPDATE Contact SET Flag = 0 WHERE MemberId = 13, , .

, . , "TransactionScope ( ).

, (ReadCommitted).

[EDIT]

Chris' , . , 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();
}

( ), , Profiler , - ( GO s):

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

:

• - , ,

• -

, ( ).

.

, ( , -, ..), , .