为什么Entity Framework忽略TransactionScope（不添加NOLOCK）？

Question

我需要使用TransactionScope来读取NOLOCK，代码如下：

我缺少什么？

我正在尝试使用TransactionScope来读取NOLOCK，代码如下：

var scopeOptions = new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted };
using (var scope = new TransactionScope(TransactionScopeOption.Required, scopeOptions))
{
   using (var db = new MyDbContext(ConnectionStringEntities))
   {
      // Simple read with a try catch block...
   }
   scope.Complete();
}

我希望在SQL查询中添加NOLOCK（查看SQL Profiler和自定义DbCommandInterceptor，但没有找到...）

更新：经过更多的研究，我想知道是否仍然使用了所选的游标，只是没有使用NOLOCK "hint"（仅适用于SQL Server，也仅适用于一个表），我找到了一些代码，可以获取当前事务，并且它似乎显示了正确的选定事务隔离级别（ReadUncommitted / Serializable等）。我仍然想测试它，但如果你有任何想法，请让我知道。 获取当前.net TransactionScope IsolationLevel

Transaction trans = Transaction.Current;
System.Transactions.IsolationLevel level = trans.IsolationLevel;
LogService.Instance.Debug($"Transaction IsolationLevel = {level.ToString()}");

- Yovav

Answer 1

看起来Entity Framework确实尊重隔离级别，但它不使用NOLOCK提示（可能因为它太具体于数据库），这也是我对EF的主要抱怨 - 它不太适用于不同类型的数据库。另一个例子是新标识正在将AspNetUsers的GUID主键保存为字符串（再次由于缺乏优化）。除此之外（和其他一些事情），EF非常棒！

我无法在任何地方找到解决方案，我绝对不想让所有查询都使用NOLOCK - 只有未提交的查询，所以我最终结合了两个解决方案（并进行了一些更改）：

1. NoLockInterceptor - for adding NOLOCK on the fly (Entity Framework with NOLOCK):

   /// <summary>
   /// Add "WITH (NOLOCK)" hint to SQL queries, SQL Server specifc - may break queries on different databases.
   /// (conditionally turn off with NoLockInterceptor.AddNoLockHintToSqlQueries = false to change on runtime)
   /// <para>
   /// https://dev59.com/a3NA5IYBdhLWcg3whuV_
   /// </para>
   /// </summary>
   public class NoLockInterceptor : DbCommandInterceptor
   {
       private static readonly Regex TableAliasRegex = new Regex(
           @"(?<tableAlias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))",
           RegexOptions.Multiline | RegexOptions.IgnoreCase);
   
       /// <summary>
       /// Add "WITH (NOLOCK)" hint to SQL queries - unique to each thread 
       /// (set to true only when needed and then back to false)
       /// </summary>
       [ThreadStatic]
       public static bool AddNoLockHintToSqlQueries;
   
       public NoLockInterceptor()
       {
           // Do not use by default for all queries
           AddNoLockHintToSqlQueries = false;
       }
   
       public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
       {
           if (AddNoLockHintToSqlQueries)
           {
               command.CommandText = TableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
           }
       }
   
       public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
       {
           if (AddNoLockHintToSqlQueries)
           {
               command.CommandText = TableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
           }
       }
   }
   

2. TransactionWrapper - to invoke the NoLockInterceptor behaviour and also useful for repeated use of transactions (http://haacked.com/archive/2009/08/18/simpler-transactions.aspx/):

   /// <summary>
   /// Transaction wrapper for setting pre-defined transaction scopes
   /// <para>
   /// http://haacked.com/archive/2009/08/18/simpler-transactions.aspx/
   /// </para>
   /// </summary>
   public static class TransactionWrapper
   {
       /// <summary>
       /// Set transaction scope and using NoLockInterceptor for adding SQL Server specific "WITH (NOLOCK)" 
       /// to ReadUncommitted isolation level transactions (not supported by Entity Framework)
       /// </summary>
       /// <param name="isolationLevel"></param>
       /// <param name="transactionScopeOption"></param>
       /// <param name="timeout"></param>
       /// <param name="action"></param>
       public static void SetScope(IsolationLevel isolationLevel, TransactionScopeOption transactionScopeOption,
           TimeSpan timeout, Action action)
       {
           var transactionOptions = new TransactionOptions { IsolationLevel = isolationLevel, Timeout = timeout };
           using (var transactionScope = new TransactionScope(transactionScopeOption, transactionOptions))
           {
               if (isolationLevel == IsolationLevel.ReadUncommitted)
                   NoLockInterceptor.AddNoLockHintToSqlQueries = true;
   
               action();
               transactionScope.Complete();
   
               if (isolationLevel == IsolationLevel.ReadUncommitted)
                   NoLockInterceptor.AddNoLockHintToSqlQueries = false;
           }
       }
   }
   

使用方法如下：

var timeout = TimeSpan.FromSeconds(ConfigVariables.Instance.Timeout_Transaction_Default_In_Seconds);
TransactionWrapper.SetScope(IsolationLevel.ReadUncommitted, TransactionScopeOption.Required, timeout, () =>
{
    using (var db = new MyDbContext(MyDbContextConnectionStringEntities))
    {
       // Do stuff...
    }
});

现在，只有在ReadUncommitted事务隔离级别范围内的查询中添加NOLOCK。

- Yovav

Answer 2

你无法让 Entity Framework 渲染 NOLOCK 提示。如果你想要读取未提交数据，你需要采取不同的方法，例如通过向 TransactionOptions 添加 IsolationLevel.ReadUncommited 的 TransactionScope 来实现你所做的操作。

编写自己的命令拦截器或者自己的 EF 提供程序也可以达到同样的效果。

https://msdn.microsoft.com/en-us/data/dn469464.aspx

- William Xifaras

---

TransactionScope用于与以前的EF版本（大约4年前）一起使用，您知道在EF6中是否有所改变或可能存在错误吗？ - Yovav

它仍然可以工作，但是现在有新的API可以处理事务，例如DbContext.Database.BeginTransaction和DbContext.Database.UseTransaction。 - William Xifaras

Answer 3

我尝试过事务范围，它可以对调用数据库进行性能分析。EF 开始和结束事务，但从未将隔离级别更改为读取已提交。

            using (var scope = new TransactionScope(
            TransactionScopeOption.Required,
            new TransactionOptions()
            {
                IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
            }))
        {
            List<T> toReturn = query.ToList();
            scope.Complete();
            return toReturn;
        }

- Jason Wilson