c# - ASP.NET C# MySQL 查询执行失败时重试

Question

是否有任何 ASP.NET 包/DLL 允许 MySQL 查询执行在失败时重试？

我读过Transient Fault Handling 甚至遇到了Dapper issue which shows an example但根据我的研究，这只适用于 SqlServer 和/或 Azure。

我的技术堆栈如下:

• .NET 4.5.2
• Dapper 1.50.2.0
• MySQL 5.6(使用 Amazon Aurora)

最终我试图解决一个sporadic failure issue我相信实现一些重试逻辑将有助于缓解该问题。

我尝试实现此 Dapper issue 中的一些代码但因为我正在使用MySql.Data要连接到我的 MySql 数据库，它无法使用特定于 SqlServer 连接的各种方法。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;

namespace TransientDapper
{
    public static class TransientDapperExtensions
    {
        private static readonly RetryManager SqlRetryManager = GetDefaultRetryManager();
        private static readonly RetryPolicy SqlCommandRetryPolicy = SqlRetryManager.GetDefaultSqlCommandRetryPolicy();
        private static readonly RetryPolicy SqlConnectionRetryPolicy =
            SqlRetryManager.GetDefaultSqlConnectionRetryPolicy();

        private static RetryManager GetDefaultRetryManager()
        {
            const int retryCount = 4;
            const int minBackoffDelayMilliseconds = 2000;
            const int maxBackoffDelayMilliseconds = 8000;
            const int deltaBackoffMilliseconds = 2000;

            var exponentialBackoffStrategy =
                new ExponentialBackoff(
                    "exponentialBackoffStrategy",
                    retryCount,
                    TimeSpan.FromMilliseconds(minBackoffDelayMilliseconds),
                    TimeSpan.FromMilliseconds(maxBackoffDelayMilliseconds),
                    TimeSpan.FromMilliseconds(deltaBackoffMilliseconds)
                    );

            var manager = new RetryManager(
                new List<RetryStrategy>
                {
                    exponentialBackoffStrategy
                },
                exponentialBackoffStrategy.Name
                );

            return manager;
        }

        public static void OpenWithRetry(this SqlConnection cnn)
        {
            cnn.OpenWithRetry(SqlConnectionRetryPolicy);
        }

        public static IEnumerable<T> QueryWithRetry<T>(
            this SqlConnection cnn, string sql, object param = null, IDbTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, CommandType? commandType = null
            )
        {
            return SqlCommandRetryPolicy.ExecuteAction(
                () => cnn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType)
                );
        }
    }
}

Answer 1

发布此内容后不久，我发现了一个名为 Polly 的包这似乎解决了这个“重试”问题。我通过这个StackOverflow question找到了它.

这是我从 MySQL 数据库查询并在失败时重试的实现:

var policy = Policy
    .Handle<AuthenticationException>(ex => ex.InnerException is Win32Exception)
    .Or<AuthenticationException>()
    .Retry((exception, attempt) =>
    {                        
        Log.Error(exception, "Class: {Class} | Method: {Method} | Failure executing query, on attempt number: {Attempt}", GetType().Name,
            MethodBase.GetCurrentMethod().Name, attempt);
    });

try
{
    var token = new Token();

    policy.Execute(() =>
    {
        using (var connection = _mySqlDatabase.GetConnection())
        {
            token = connection.Query<Token>("SELECT * FROM Token...").FirstOrDefault();
        }
    });

    return token;
}
catch (Exception ex)
{
    Log.Error(ex, "Class: {Class} | Method: {Method} | Ultimately failed to retrieve data from the database", GetType().Name,
        MethodBase.GetCurrentMethod().Name);
    throw new HttpError(HttpStatusCode.InternalServerError);
}