EF6 with TransactionScope - IsolationLevel.ReadUncommitted但ReadCommitted第一

Question

在MSSQL 2008上使用EF进行更新查询时，存在性能和锁问题.所以我把ReadUncommitted事务隔离级别，希望解决它，像这样，

先于

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

后

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

我们使用SQL profiler来跟踪。但是，对这些脚本进行了排序(对于第一个脚本，期望读取未提交的)。

审计登录

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

审计登录

set transaction isolation level read uncommitted

虽然我可以重新发送这个请求并使其正确的顺序(将显示以下请求的read-uncommitted，相同的SPID)，但我想知道为什么它在读取提交命令之后发送读未提交的命令，以及如何使用EF和修复？谢谢。

Answer 1

根据ADO.NET文档SQL Server中的快照隔离中的以下说明，只要底层连接池化，隔离级别就不会绑定到事务范围：

如果连接已池化，则重置其隔离级别不会重置服务器上的隔离级别。因此，使用相同池内连接的后续连接从设置为池连接的隔离级别开始。关闭连接池的另一种方法是为每个连接显式地设置隔离级别。

因此，在Server 2012之前，将隔离设置为ReadCommitted以外的任何级别都需要在创建有问题的SqlConnection时调用连接池，或者在每个连接中显式地设置隔离级别，以避免意外行为，包括死锁。或者，可以通过调用ClearPool法来清除连接池，但由于该方法既不绑定到事务范围，也不绑定到底层连接，因此我认为，当几个连接同时在同一个池内连接上运行时，它并不适用。

引用Server论坛中的post Server 2014重置隔离级别和我自己的测试，当使用Server 2014和TDS 7.3或更高版本的客户端驱动程序时，这样的解决方案是过时的。

Answer 2

我认为这是一个由审计登录事件引起的青鱼。这是，而不是，它显示了客户机告诉服务器'set事务隔离级别读取未提交‘的时刻。当从池中选择并重用该连接时，它将向您展示稍后的隔离级别。

我通过将Pooling=false添加到连接字符串来验证这一点。然后，审计登录总是显示事务隔离级别已提交。

到目前为止，我还没有在SQL中看到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 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)。

编辑

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在整个过程中输出的内容，我们会看到这样的情况(删除了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

这有两个缺点：

1. 许多往返-许多查询是针对数据库发出的，这给数据库引擎带来了更大的压力，而且客户端也需要更长的时间。
2. 长事务-长事务应该避免作为最小化死锁的一种尝试

因此，建议的解决方案应该在特定情况下更好地工作(简单更新)。

在更复杂的情况下，可能需要更改隔离级别。

我认为，如果一个人处理大量的数据(选择数百万，做一些事情，更新回来等)。存储过程可能是解决方案，因为所有东西都执行服务器端。