EF6与TransactionScope - 使用IsolationLevel.ReadUncommitted却得到了ReadCommitted

Question

在使用EF进行基于MSSQL 2008的查询更新时，存在性能和锁定问题。因此，我设置了 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 ），但我想知道为什么它在读取已提交的命令之后发送了read-uncommitted命令，并且如何使用EF和 TransactionScope进行修复？谢谢。

- nwpie

---

您能否在分析器中添加与事务相关的事件？（begin tran，rollback，commit）。了解是否实际创建了任何事务以及何时创建将是有趣的。 - Alexei - check Codidact

你能否更详细地指出性能问题？（例如选择太多，其他查询被此查询阻塞等）。未提交读通常是一个不好的选项，应该是最后的选择。 - Alexei - check Codidact

你使用哪种EF方法？'myEntities'是一个DbContext还是ObjectContext？ - Patrick

你应该调用 TransactionScope.Complete()。这不会影响你所看到的内容，而且你无法回滚一个 SELECT，但这是一个好习惯。另外请注意，如果你没有设置隔离级别，你将得到最后应用于你的池化连接的任何事务级别。如果你从未对隔离级别进行任何操作，则默认为 read committed，否则可能是任何值。请参见此处。 - Jeroen Mostert

@Jeroen，我更新了我的帖子。可能会更清楚问题所在。感谢您的建议。 - nwpie

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

我说“有点儿”的原因是每个语句都在自己的会话中运行。

也许这是在长篇大论地说，即使您无法通过 Profiler 证明它，EF 事务仍能正确地工作。

- Chris F Carroll

---

http://www.paulkiddie.com/2013/03/tracing-all-sql-generated-by-entity-framework/ 提到存储过程/远程过程调用事件是我们需要跟踪的内容。 - Chris F Carroll

Answer 2

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

如果连接被池化，则重置其隔离级别不会重置服务器上的隔离级别。因此，后续使用相同池化内部连接的连接将以池化连接的隔离级别作为其隔离级别开始。关闭连接池的替代方法是为每个连接显式设置隔离级别。

因此，我得出的结论是，在SQL Server 2012之前，将隔离级别设置为除ReadCommitted以外的任何级别都需要在创建可疑的SqlConnection时关闭连接池或显式地在每个连接中设置隔离级别，以避免意外行为，包括死锁。另外，可以通过调用ClearPool Method来清除连接池，但由于该方法既不绑定到事务范围也不绑定到底层连接，因此当多个连接同时针对同一池化内部连接运行时，我认为它并不合适。

参考SQL论坛中的帖子SQL Server 2014 reseting isolation level和我的测试，当使用SQL Server 2014和具有TDS 7.3或更高版本的客户端驱动程序时，这些解决方法已过时。

- Patrick

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 在整个过程中输出的内容，我们会看到类似于这样的东西（去掉了 GO）：

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. 长事务 - 应该避免长事务，以尝试最小化死锁

因此，在您的特定情况下（简单更新），建议的解决方案应该更好。

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

我认为，如果处理大量数据（选择数百万条记录，执行某些操作，然后更新回去等），存储过程可能是解决方案，因为所有内容都在服务器端执行。

- Alexei - check Codidact

---

这并没有回答为什么事务范围没有在它应该设置的事务上设置隔离级别。默认情况下，所有数据库操作都会生成一个隐式事务，该事务使用数据库的默认事务级别。 - Patrick

@Patrick - 这是正确的，但我更喜欢解决实际问题（在编辑后变得清晰）而不是问题本身。请参见XY问题。 - Alexei - check Codidact

手动设置事务范围是一种常见的模式，但在Entity Framework下似乎存在错误。因此，请回答这个问题。 - Patrick