linq – 如何使用SqlAzureExecutionStrategy和“Nolock”

为了处理sql超时我正在尝试使用sqlAzureExecutionStrategy( https://msdn.microsoft.com/en-us/data/dn456835.aspx)

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

示例代码

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

抛出错误

The configured execution strategy ‘sqlAzureExecutionStrategy’ does not support user initiated transactions. See 07003 for additional information.

我已经看到了在每次调用的基础上关闭 sqlAzureExecutionStrategy的答案,但是如果我的所有读取都忽略了策略,那将会破坏使用它的目的.可以同时拥有“NoLock”和sqlAzureExecutionStrategy

解决方法

sqlAzureExecutionStrategy不支持在要重试的操作之外启动的事务.要解决此限制,您需要暂停策略,创建事务范围并将操作作为手动传递给要重试的执行策略的操作：

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中可挂起策略的替代方法,它将自动挂起任何嵌套调用：

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

原文链接：https://www.f2er.com/mssql/77026.html

linq – 如何使用SqlAzureExecutionStrategy和“Nolock”

解决方法

猜你在找的MsSQL相关文章