Знаете, когда повторить или потерпеть неудачу при вызове SQL Server из C#?

Question

У меня есть приложение C#, которое извлекает данные из SQL Server, размещенного в несколько нестабильной среде. Я ничего не могу сделать для решения экологических проблем, поэтому мне нужно решать их как можно более изящно.

Для этого я хочу повторить операции, которые являются результатом сбоев инфраструктуры, таких как сбои в работе сети, отключение SQL-серверов из-за их перезагрузки, тайм-ауты запросов и т. Д. В то же время я не хочу повторить запросы, если они не прошли из-за логических ошибок. Я просто хочу, чтобы те выдавали исключение клиенту.

Мой вопрос заключается в следующем: как лучше всего различать проблемы окружающей среды (потерянные соединения, тайм-ауты) и другие виды исключений (такие вещи, как логические ошибки, которые произошли бы, даже если среда была стабильной).

Есть ли обычно используемый шаблон в C# для работы с такими вещами? Например, есть ли свойство, которое я могу проверить на объекте SqlConnection для обнаружения неудачных соединений? Если нет, то как лучше всего подойти к этой проблеме?

Что бы это ни стоило, мой код не является чем-то особенным:

using (SqlConnection connection = new SqlConnection(myConnectionString))
using (SqlCommand command = connection.CreateCommand())
{
  command.CommandText = mySelectCommand;
  connection.Open();

  using (SqlDataReader reader = command.ExecuteReader())
  {
    while (reader.Read())
    {
      // Do something with the returned data.
    }
  }
}

Answer 1

Один сингл SqlException (может) переносит несколько ошибок SQL Server. Вы можете перебирать их с помощью Errors имущество. Каждая ошибка SqlError:

foreach (SqlError error in exception.Errors)

каждый SqlError имеет Class свойство, которое вы можете использовать, чтобы приблизительно определить, можете ли вы повторить попытку или нет (и в случае, если вы повторите попытку, если вам придется воссоздать соединение тоже). Из MSDN:

• Class <10 - для ошибок в информации, которую вы передали, тогда (вероятно) вы не сможете повторить попытку, если сначала вы не исправите ввод.
• Class от 11 до 16 "генерируются пользователем", тогда, вероятно, вы снова ничего не сможете сделать, если пользователь сначала не исправит свои входные данные. Обратите внимание, что класс 16 содержит много временных ошибок, а класс 13 предназначен для взаимоблокировок (благодаря EvZ), поэтому вы можете исключить эти классы, если обрабатываете их один за другим.
• Class с 17 до 24 - типичные аппаратные / программные ошибки, и вы можете повторить попытку. когда Class 20 или выше, вы должны воссоздать соединение тоже. 22 и 23 могут быть серьезными аппаратными / программными ошибками, 24 указывает на ошибку носителя (что-то должно быть предупреждено пользователем, но вы можете повторить попытку, если это была просто "временная" ошибка).

Вы можете найти более подробное описание каждого класса здесь.

В общем, если вы обрабатываете ошибки с их классом, вам не нужно точно знать каждую ошибку (используя error.Number собственность или exception.Number который просто ярлык для первого SqlError в этом списке). Это имеет тот недостаток, что вы можете повторить попытку, когда это бесполезно (или ошибка не может быть исправлена). Я бы предложил двухэтапный подход:

• Проверьте известные коды ошибок (перечислите коды ошибок с помощью SELECT * FROM master.sys.messages) чтобы увидеть, что вы хотите справиться (зная, как). Это представление содержит сообщения на всех поддерживаемых языках, поэтому вам может потребоваться отфильтровать их по msglangid колонка (например, 1033 для английского языка).
• Во всем остальном полагайтесь на класс ошибок, повторяя при Class 13 или выше 16 (и переподключение, если 20 или выше).
• Ошибки со степенью серьезности выше 21 (22, 23 и 24) являются серьезными ошибками, и небольшое ожидание не устранит эти проблемы (сама база данных также может быть повреждена).

Одним словом о высших классах. Как обрабатывать эти ошибки не просто, и это зависит от многих факторов (включая управление рисками для вашего приложения). В качестве простого первого шага я бы не повторил попытки 22, 23 и 24 при попытке операции записи: если база данных, файловая система или носитель серьезно повреждены, то запись новых данных может еще больше ухудшить целостность данных (SQL Server крайне осторожен с не ставить под угрозу БД для запроса даже в критических обстоятельствах). Поврежденный сервер, это зависит от сетевой архитектуры вашей БД, может даже быть заменен в "горячем" режиме (автоматически, через определенное время или при срабатывании указанного триггера). Всегда консультируйтесь и работайте рядом с вашим администратором баз данных.

Стратегия повторной попытки зависит от обрабатываемой ошибки: освободите ресурсы, дождитесь завершения ожидающей операции, выполните альтернативное действие и т. Д. В общем, вам следует повторить попытку, только если все ошибки "повторяются":

bool rebuildConnection = true; // First try connection must be open

for (int i=0; i < MaximumNumberOfRetries; ++i) {
    try {
        // (Re)Create connection to SQL Server
        if (rebuildConnection) {
            if (connection != null)
                connection.Dispose();

            // Create connection and open it...
        }

        // Perform your task

        // No exceptions, task has been completed
        break;
    }
    catch (SqlException e) {
        if (e.Errors.Cast<SqlError>().All(x => CanRetry(x))) {
            // What to do? Handle that here, also checking Number property.
            // For Class < 20 you may simply Thread.Sleep(DelayOnError);

            rebuildConnection = e.Errors
                .Cast<SqlError>()
                .Any(x => x.Class >= 20);

            continue; 
        }

        throw;
    }
}

Оберните все в try/finally правильно распоряжаться соединением. С этим простым-фальшиво-наивным CanRetry() функция:

private static readonly int[] RetriableClasses = { 13, 16, 17, 18, 19, 20, 21, 22, 24 };

private static bool CanRetry(SqlError error) {
    // Use this switch if you want to handle only well-known errors,
    // remove it if you want to always retry. A "blacklist" approach may
    // also work: return false when you're sure you can't recover from one
    // error and rely on Class for anything else.
    switch (error.Number) {
        // Handle well-known error codes, 
    }

    // Handle unknown errors with severity 21 or less. 22 or more
    // indicates a serious error that need to be manually fixed.
    // 24 indicates media errors. They're serious errors (that should
    // be also notified) but we may retry...
    return RetriableClasses.Contains(error.Class); // LINQ...
}

Некоторые довольно хитрые способы найти список некритических ошибок здесь.

Обычно я встраиваю весь этот (шаблонный) код в один метод (где я могу спрятать все грязные вещи, сделанные для создания / удаления / воссоздания соединения) с этой подписью:

public static void Try(
    Func<SqlConnection> connectionFactory,
    Action<SqlCommand> performer);

Быть использованным так:

Try(
    () => new SqlConnection(connectionString),
    cmd => {
             cmd.CommandText = "SELECT * FROM master.sys.messages";
             using (var reader = cmd.ExecuteReader()) {
                 // Do stuff
         }
    });

Обратите внимание, что скелет (повтор при ошибке) можно использовать и тогда, когда вы не работаете с SQL Server (на самом деле его можно использовать для многих других операций, таких как ввод-вывод и другие вещи, связанные с сетью, поэтому я бы предложил написать общую функцию и многократно использовать его).

Answer 2

Вы можете просто SqlConnectionStringBuilder свойства, чтобы повторить попытку подключения SQL.

var conBuilder = new SqlConnectionStringBuilder(Configuration["Database:Connection"]); conBuilder.ConnectTimeout = 90; conBuilder.ConnectRetryInterval = 15; conBuilder.ConnectRetryCount = 6;

Примечание:- Требуется.Net 4.5 или более поздняя версия.

Answer 3

Я не знаю ни одного стандарта, но вот список Sql-Server исключения, которые я обычно рассматриваю как повторяемые, с добавлением DTC:

catch (SqlException sqlEx)
{
    canRetry = ((sqlEx.Number == 1205) // 1205 = Deadlock
        || (sqlEx.Number == -2) // -2 = TimeOut
        || (sqlEx.Number == 3989) // 3989 = New request is not allowed to start because it should come with valid transaction descriptor
        || (sqlEx.Number == 3965) // 3965 = The PROMOTE TRANSACTION request failed because there is no local transaction active.
        || (sqlEx.Number == 3919) // 3919 Cannot enlist in the transaction because the transaction has already been committed or rolled back
        || (sqlEx.Number == 3903)); // The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
}

Что касается повторных попыток, предположите, что между повторными попытками добавляется случайная задержка, чтобы уменьшить шансы, например, на повторную блокировку тех же двух транзакций.

С некоторыми из DTC связанные ошибки, удаление соединения может быть необходимым (или, в худшем случае, SqlClient.SqlConnection.ClearAllPools()) - иначе неудачное соединение возвращается в пул.

Answer 4

В духе разделения интересов я представляю три логических слоя в этом случае...

1. Прикладной уровень, который вызывает слой "нестабильный обработчик зависимостей"
2. Уровень "обработчик нестабильной зависимости", который вызывает уровень доступа к данным
3. Слой доступа к данным, который ничего не знает о бесполезности

Вся логика для повторных попыток будет на этом уровне обработчика, чтобы не загрязнять уровень доступа к данным другой логикой, кроме связи с базой данных. (Таким образом, ваш код доступа к данным не должен изменяться. И не нужно беспокоиться о "бесполезности", если он логически нуждается в изменении для новых функций.)

Шаблон повторной попытки может быть основан на перехвате определенных исключений в цикле счетчика. (Счетчик просто для предотвращения бесконечной повторной попытки.) Примерно так:

public SomeReturnValue GetSomeData(someIdentifier)
{
    var tries = 0;
    while (tries < someConfiguredMaximum)
    {
        try
        {
            tries++;
            return someDataAccessObject.GetSomeData(someIdentifier);
        }
        catch (SqlException e)
        {
            someLogger.LogError(e);
            // maybe wait for some number of milliseconds?  make the method async if possible
        }
    }
    throw new CustomException("Maximum number of tries has been reached.");
}

Это зациклило бы некоторое настроенное число раз, повторяя попытку, пока он не будет работать или пока не будет достигнут максимум. После этого максимального числа создается специальное исключение для обработки приложением. Вы можете дополнительно настроить обработку исключений, проверив конкретные SqlException это поймано Возможно, основываясь на сообщении об ошибке, вы можете продолжить цикл или бросить CustomException,

Вы можете дополнительно уточнить эту логику, перехватывая другие типы исключений, изучая их и т. Д. Главное здесь заключается в том, что эта ответственность остается изолированной для определенного логического уровня в приложении, максимально прозрачного для других уровней. В идеале уровень обработчика и уровень доступа к данным реализуют одинаковые интерфейсы. Таким образом, если вы когда-нибудь переместите код в более стабильную среду и вам больше не понадобится уровень обработчика, было бы тривиально удалить его без каких-либо изменений на уровне приложения.

Answer 5

Я не знаю настоящего стандарта. Вы можете попробовать взглянуть на блок приложения Transient Fault Handling. Это достаточно надежно, но для некоторых пользователей может показаться слишком "корпоративным". Другой подход может заключаться в использовании аспектной структуры для отлова ошибок. Или же старый добрый try/catch будет работать.

Что касается определения того, что нужно повторить, вы, как правило, захотите взглянуть на исключение. SqlException предоставляет довольно мало информации относительно источника вашей проблемы, но может быть больно анализировать ее. Я собрал некоторый код, чтобы выделить их и попытаться определить, что можно повторить, а что нет. Это не было поддержано в течение долгого времени, поэтому вы, вероятно, должны принять это как отправную точку, а не готовый продукт. Кроме того, это было нацелено на SQL Azure, поэтому он может не полностью применяться к вашей ситуации (например, регулирование ресурсов является специфической функцией Azure, IIRC).

/// <summary>
/// Helps to extract useful information from SQLExceptions, particularly in SQL Azure
/// </summary>
public class SqlExceptionDetails
{
    public ResourcesThrottled SeriouslyExceededResources { get; private set; }
    public ResourcesThrottled SlightlyExceededResources { get; private set; }
    public OperationsThrottled OperationsThrottled { get; private set; }
    public IList<SqlErrorCode> Errors { get; private set; }
    public string ThrottlingMessage { get; private set; }

    public bool ShouldRetry { get; private set; }
    public bool ShouldRetryImmediately { get; private set; }

    private SqlExceptionDetails()
    {
        this.ShouldRetryImmediately = false;
        this.ShouldRetry = true;
        this.SeriouslyExceededResources = ResourcesThrottled.None;
        this.SlightlyExceededResources = ResourcesThrottled.None;
        this.OperationsThrottled = OperationsThrottled.None;
        Errors = new List<SqlErrorCode>();
    }

    public SqlExceptionDetails(SqlException exception) :this(exception.Errors.Cast<SqlError>())
    {
    }

    public SqlExceptionDetails(IEnumerable<SqlError> errors) : this()
    {
        List<ISqlError> errorWrappers = (from err in errors
                                         select new SqlErrorWrapper(err)).Cast<ISqlError>().ToList();
        this.ParseErrors(errorWrappers);
    }

    public SqlExceptionDetails(IEnumerable<ISqlError> errors) : this()
    {
        ParseErrors(errors);
    }

    private void ParseErrors(IEnumerable<ISqlError> errors)
    {
        foreach (ISqlError error in errors)
        {
            SqlErrorCode code = GetSqlErrorCodeFromInt(error.Number);
            this.Errors.Add(code);

            switch (code)
            {
                case SqlErrorCode.ServerBusy:
                    ParseServerBusyError(error);
                    break;
                case SqlErrorCode.ConnectionFailed:
                    //This is a very non-specific error, can happen for almost any reason
                    //so we can't make any conclusions from it
                    break;
                case SqlErrorCode.DatabaseUnavailable:
                    ShouldRetryImmediately = false;
                    break;
                case SqlErrorCode.EncryptionNotSupported:
                    //this error code is sometimes sent by the client when it shouldn't be
                    //Therefore we need to retry it, even though it seems this problem wouldn't fix itself
                    ShouldRetry = true;
                    ShouldRetryImmediately = true;
                    break;
                case SqlErrorCode.DatabaseWorkerThreadThrottling:
                case SqlErrorCode.ServerWorkerThreadThrottling:
                    ShouldRetry = true;
                    ShouldRetryImmediately = false;
                    break;


                //The following errors are probably not going to resolved in 10 seconds
                //They're mostly related to poor query design, broken DB configuration, or too much data
                case SqlErrorCode.ExceededDatabaseSizeQuota:
                case SqlErrorCode.TransactionRanTooLong:
                case SqlErrorCode.TooManyLocks:
                case SqlErrorCode.ExcessiveTempDBUsage:
                case SqlErrorCode.ExcessiveMemoryUsage:
                case SqlErrorCode.ExcessiveTransactionLogUsage:
                case SqlErrorCode.BlockedByFirewall:
                case SqlErrorCode.TooManyFirewallRules:
                case SqlErrorCode.CannotOpenServer:
                case SqlErrorCode.LoginFailed:
                case SqlErrorCode.FeatureNotSupported:
                case SqlErrorCode.StoredProcedureNotFound:
                case SqlErrorCode.StringOrBinaryDataWouldBeTruncated:
                    this.ShouldRetry = false;
                    break;
            }
        }

        if (this.ShouldRetry && Errors.Count == 1)
        {
            SqlErrorCode code = this.Errors[0];
            if (code == SqlErrorCode.TransientServerError)
            {
                this.ShouldRetryImmediately = true;
            }
        }

        if (IsResourceThrottled(ResourcesThrottled.Quota) ||
            IsResourceThrottled(ResourcesThrottled.Disabled))
        {
            this.ShouldRetry = false;
        }

        if (!this.ShouldRetry)
        {
            this.ShouldRetryImmediately = false;
        }

        SetThrottlingMessage();
    }

    private void SetThrottlingMessage()
    {
        if (OperationsThrottled == Sql.OperationsThrottled.None)
        {
            ThrottlingMessage = "No throttling";
        }
        else
        {
            string opsThrottled = OperationsThrottled.ToString();
            string seriousExceeded = SeriouslyExceededResources.ToString();
            string slightlyExceeded = SlightlyExceededResources.ToString();

            ThrottlingMessage = "SQL Server throttling encountered. Operations throttled: " + opsThrottled
                        + ", Resources Seriously Exceeded: " + seriousExceeded
                        + ", Resources Slightly Exceeded: " + slightlyExceeded;
        }
    }

    private bool IsResourceThrottled(ResourcesThrottled resource)
    {
        return ((this.SeriouslyExceededResources & resource) > 0 ||
                (this.SlightlyExceededResources & resource) > 0);
    }

    private SqlErrorCode GetSqlErrorCodeFromInt(int p)
    {
        switch (p)
        {
            case 40014:
            case 40054:
            case 40133:
            case 40506:
            case 40507:
            case 40508:
            case 40512:
            case 40516:
            case 40520:
            case 40521:
            case 40522:
            case 40523:
            case 40524:
            case 40525:
            case 40526:
            case 40527:
            case 40528:
            case 40606:
            case 40607:
            case 40636:
                return SqlErrorCode.FeatureNotSupported;
        }

        try
        {
            return (SqlErrorCode)p;
        }
        catch
        {
            return SqlErrorCode.Unknown;
        }
    }

    /// <summary>
    /// Parse out the reason code from a ServerBusy error. 
    /// </summary>
    /// <remarks>Basic idea extracted from http://msdn.microsoft.com/en-us/library/gg491230.aspx
    /// </remarks>
    /// <param name="error"></param>
    private void ParseServerBusyError(ISqlError error)
    {
        int idx = error.Message.LastIndexOf("Code:");
        if (idx < 0)
        {
            return;
        }

        string reasonCodeString = error.Message.Substring(idx + "Code:".Length);
        int reasonCode;
        if (!int.TryParse(reasonCodeString, out reasonCode))
        {
            return;
        }

        int opsThrottledInt = (reasonCode & 3);
        this.OperationsThrottled = (OperationsThrottled)(Math.Max((int)OperationsThrottled, opsThrottledInt));


        int slightResourcesMask = reasonCode >> 8;
        int seriousResourcesMask = reasonCode >> 16;
        foreach (ResourcesThrottled resourceType in Enum.GetValues(typeof(ResourcesThrottled)))
        {
            if ((seriousResourcesMask & (int)resourceType) > 0)
            {
                this.SeriouslyExceededResources |= resourceType;
            }
            if ((slightResourcesMask & (int)resourceType) > 0)
            {
                this.SlightlyExceededResources |= resourceType;
            }
        }
    }
}

public interface ISqlError
{
    int Number { get; }
    string Message { get; }
}

public class SqlErrorWrapper : ISqlError
{
    public SqlErrorWrapper(SqlError error)
    {
        this.Number = error.Number;
        this.Message = error.Message;
    }

    public SqlErrorWrapper()
    {
    }

    public int Number { get; set; }
    public string Message { get; set; }
}

/// <summary>
/// Documents some of the ErrorCodes from SQL/SQL Azure. 
/// I have not included all possible errors, only the ones I thought useful for modifying runtime behaviors
/// </summary>
/// <remarks>
/// Comments come from: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management-in-sql-azure.aspx
/// </remarks>
public enum SqlErrorCode : int
{
    /// <summary>
    /// We don't recognize the error code returned
    /// </summary>
    Unknown = 0,

    /// <summary>
    /// A SQL feature/function used in the query is not supported. You must fix the query before it will work.
    /// This is a rollup of many more-specific SQL errors
    /// </summary>
    FeatureNotSupported = 1,

    /// <summary>
    /// Probable cause is server maintenance/upgrade. Retry connection immediately.
    /// </summary>
    TransientServerError = 40197,

    /// <summary>
    /// The server is throttling one or more resources. Reasons may be available from other properties
    /// </summary>
    ServerBusy = 40501,

    /// <summary>
    /// You have reached the per-database cap on worker threads. Investigate long running transactions and reduce server load. 
    /// http://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management.aspx#Throttling_Limits
    /// </summary>
    DatabaseWorkerThreadThrottling = 10928,

    /// <summary>
    /// The per-server worker thread cap has been reached. This may be partially due to load from other databases in a shared hosting environment (eg, SQL Azure).
    /// You may be able to alleviate the problem by reducing long running transactions.
    /// http://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management.aspx#Throttling_Limits
    /// </summary>
    ServerWorkerThreadThrottling = 10929,

    ExcessiveMemoryUsage = 40553,

    BlockedByFirewall = 40615,

    /// <summary>
    /// The database has reached the maximum size configured in SQL Azure
    /// </summary>
    ExceededDatabaseSizeQuota = 40544,

    /// <summary>
    /// A transaction ran for too long. This timeout seems to be 24 hours.
    /// </summary>
    /// <remarks>
    /// 24 hour limit taken from http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management-in-sql-azure.aspx
    /// </remarks>
    TransactionRanTooLong = 40549,

    TooManyLocks = 40550,

    ExcessiveTempDBUsage = 40551,

    ExcessiveTransactionLogUsage = 40552,

    DatabaseUnavailable = 40613,

    CannotOpenServer = 40532,

    /// <summary>
    /// SQL Azure databases can have at most 128 firewall rules defined
    /// </summary>
    TooManyFirewallRules = 40611,

    /// <summary>
    /// Theoretically means the DB doesn't support encryption. However, this can be indicated incorrectly due to an error in the client library. 
    /// Therefore, even though this seems like an error that won't fix itself, it's actually a retryable error.
    /// </summary>
    /// <remarks>
    /// http://social.msdn.microsoft.com/Forums/en/ssdsgetstarted/thread/e7cbe094-5b55-4b4a-8975-162d899f1d52
    /// </remarks>
    EncryptionNotSupported = 20,

    /// <summary>
    /// User failed to connect to the database. This is probably not recoverable.
    /// </summary>
    /// <remarks>
    /// Some good info on more-specific debugging: http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
    /// </remarks>
    LoginFailed = 18456,

    /// <summary>
    /// Failed to connect to the database. Could be due to configuration issues, network issues, bad login... hard to tell
    /// </summary>
    ConnectionFailed = 4060,

    /// <summary>
    /// Client tried to call a stored procedure that doesn't exist
    /// </summary>
    StoredProcedureNotFound = 2812,

    /// <summary>
    /// The data supplied is too large for the column
    /// </summary>
    StringOrBinaryDataWouldBeTruncated = 8152
}

Answer 6

Обратитесь к этим документам: со всеми пользовательскими реализациями, обрабатывающими большинство возникающих проблем.

      // Define the retry logic parameters
var options = new SqlRetryLogicOption()
{
    // Tries 5 times before throwing an exception
    NumberOfTries = 5,
    // Preferred gap time to delay before retry
    DeltaTime = TimeSpan.FromSeconds(1),
    // Maximum gap time for each delay time before retry
    MaxTimeInterval = TimeSpan.FromSeconds(20),
    // SqlException retriable error numbers
    TransientErrors = new int[] { 4060, 1024, 1025}
};



// Create a custom retry logic provider
SqlRetryLogicBaseProvider provider = CustomRetry.CreateCustomProvider(options);


    // Assumes that connection is a valid SqlConnection object 
// Set the retry logic provider on the connection instance
connection.RetryLogicProvider = provider;
// Establishing the connection will trigger retry if one of the given transient failure occurs.
connection.Open();

MS ДОКС.