如何使用 SqlAzureExecutionStrategy 和“Nolock" [英] How to use SqlAzureExecutionStrategy and "Nolock"

问题描述

为了处理 SQL 超时，我尝试使用 SqlAzureExecutionStrategy (https://msdn.microsoft.com/en-us/data/dn456835.aspx)

To deal with SQL timeouts I'm trying to use SqlAzureExecutionStrategy (https://msdn.microsoft.com/en-us/data/dn456835.aspx)

我遇到的问题是它阻止了用户启动的事务"，这似乎是在 EF 中实现with (nolock)"的推荐方式(http://www.hanselman.com/blog/GettingLINQToSQLAndLINQToEntitiesToUseNOLOCK.aspx, NOLOCK 与 Linq to SQL).

The problem I am running into is it prevents "user initiated transactions" which seem to be the recommended way to implement "with (nolock)" in EF (http://www.hanselman.com/blog/GettingLINQToSQLAndLINQToEntitiesToUseNOLOCK.aspx, NOLOCK with Linq to SQL).

示例代码

    public AspnetUser GetAspnetUserByUserName(string userName)
    {
        using (var tx = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
        {
            return context.AspnetUsers.Where(x => x.UserName == userName).FirstOrDefault();
        }
    }

抛出错误

配置的执行策略SqlAzureExecutionStrategy"不支持用户发起的事务.请参阅 http://go.microsoft.com/fwlink/?LinkId=309381 了解附加信息.

The configured execution strategy 'SqlAzureExecutionStrategy' does not support user initiated transactions. See http://go.microsoft.com/fwlink/?LinkId=309381 for additional information.

我已经看到了关于在每次调用的基础上关闭 SqlAzureExecutionStrategy 的答案，但是如果我的所有读取都忽略了该策略，那将违背使用它的目的.可以同时拥有NoLock"和 SqlAzureExecutionStrategy

I've seen the answers that say to turn off the SqlAzureExecutionStrategy on a per call basis, but that would defeat the purpose of using it, if all my reads ignored the strategy. It is possible to have both "NoLock" and SqlAzureExecutionStrategy

推荐答案

SqlAzureExecutionStrategy 不支持在要重试的操作之外发起的事务.要解决此限制，您需要暂停策略，创建事务范围并将工作作为您手动传递给执行策略以重试的操作:

SqlAzureExecutionStrategy doesn't support transactions initiated outside the action to be retried. To work around this restriction you would need to suspend the strategy, create the transaction scope and do the work as an action that you manually pass to the execution strategy to be retried:

public AspnetUser GetAspnetUserByUserName(string userName)
{
    new SuspendableSqlAzureExecutionStrategy().Execute(() =>
        {
            using (var tx = new TransactionScope(
                    TransactionScopeOption.Required,
                    new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
            {
                return context.AspnetUsers.Where(x => x.UserName == userName).FirstOrDefault();
            }
        });
}

在这里，我使用了 https://msdn.microsoft 中的可暂停策略的替代方案.com/en-us/data/dn307226 将自动暂停任何嵌套调用:

Here I am using an alternative to the suspendable strategy from https://msdn.microsoft.com/en-us/data/dn307226 that will suspend any nested invocations automatically:

using System.Data.Entity.Infrastructure;
using System.Data.Entity.SqlServer;
using System.Data.Entity.Utilities;
using System.Runtime.Remoting.Messaging;
using System.Threading;
using System.Threading.Tasks;

public class SuspendableSqlAzureExecutionStrategy : IDbExecutionStrategy
{
    private readonly IDbExecutionStrategy _azureExecutionStrategy;

    public SuspendableSqlAzureExecutionStrategy()
    {
        _azureExecutionStrategy = new SqlAzureExecutionStrategy();
    }

    private static bool Suspend
    {
        get { return (bool?)CallContext.LogicalGetData("SuspendExecutionStrategy") ?? false; }
        set { CallContext.LogicalSetData("SuspendExecutionStrategy", value); }
    }

    public bool RetriesOnFailure
    {
        get { return !Suspend; }
    }

    public virtual void Execute(Action operation)
    {
        if (!RetriesOnFailure)
        {
            operation();
            return;
        }

        try
        {
            Suspend = true;
            _azureExecutionStrategy.Execute(operation);
        }
        finally
        {
            Suspend = false;
        }
    }

    public virtual TResult Execute<TResult>(Func<TResult> operation)
    {
        if (!RetriesOnFailure)
        {
            return operation();
        }

        try
        {
            Suspend = true;
            return _azureExecutionStrategy.Execute(operation);
        }
        finally
        {
            Suspend = false;
        }
    }

    public virtual async Task ExecuteAsync(Func<Task> operation, CancellationToken cancellationToken)
    {
        if (!RetriesOnFailure)
        {
            await operation();
            return;
        }

        try
        {
            Suspend = true;
            await _azureExecutionStrategy.ExecuteAsync(operation, cancellationToken);
        }
        finally
        {
            Suspend = false;
        }
    }

    public virtual async Task<TResult> ExecuteAsync<TResult>(Func<Task<TResult>> operation, CancellationToken cancellationToken)
    {
        if (!RetriesOnFailure)
        {
            return await operation();
        }

        try
        {
            Suspend = true;
            return await _azureExecutionStrategy.ExecuteAsync(operation, cancellationToken);
        }
        finally
        {
            Suspend = false;
        }
    }
}

public class MyConfiguration : DbConfiguration
{
    public MyConfiguration()
    {
        SetExecutionStrategy("System.Data.SqlClient", () => new SuspendableSqlAzureExecutionStrategy());
    }
}

这篇关于如何使用 SqlAzureExecutionStrategy 和“Nolock"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！