Automatically retry request on SqlException

Question

I have to modify my static class used to process my SQL calls in order to retry the request if there is some specific SqlException (connection lost for example).

Here is my method used to call a stored procedure:

public static int CallExecuteNonQuery(string storedProcName, Action<SqlCommand> fillParamsAction, Action afterExecution, BDDSource source)
{
    int result;

    try
    {
        using (var connection = InitSqlConnection(source))

        using (var command = new SqlCommand(storedProcName, connection))
        {
            if (connection.State == ConnectionState.Closed)
                connection.Open();

            command.CommandType = CommandType.StoredProcedure;

            if (fillParamsAction != null)
                fillParamsAction(command);

            result = command.ExecuteNonQuery();

            if (afterExecution != null)
                afterExecution();
        }
    }
    catch (SqlException sqlExn)
    {
        Logger.Exception(string.Format("SQL CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), sqlExn);
        throw;
    }
    catch (Exception exception)
    {
        Logger.Exception(string.Format("SOFTWARE CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), exception);
        throw;
    }
    return result;
}

Following this link, I'm trying to retry the request as many time as it's configured for.

I got the following code:

public static int CallExecuteNonQuery(string storedProcName, Action<SqlCommand> fillParamsAction, Action afterExecution, BDDSource source)
{
    bool RetryRequest = true;
    int result = 0;

    for (int i = 0; i < Properties.Settings.Default.Request_MaximumRetry; i++)
    {
        try
        {
            if (RetryRequest)
            {
                using (var connection = InitSqlConnection(source))
                using (var command = new SqlCommand(storedProcName, connection))
                {
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    command.CommandType = CommandType.StoredProcedure;

                    if (fillParamsAction != null)
                        fillParamsAction(command);

                    result = command.ExecuteNonQuery();

                    if (afterExecution != null)
                        afterExecution();
                }

                RetryRequest = false;
            }
        }
        catch (SqlException sqlExn)
        {
            if (sqlExn.Errors.Cast<SqlError>().All(x => (x.Class >= 16 && x.Class < 22) || x.Class == 24))
            {
                RetryRequest = true;
                continue;
            }

            Logger.Exception(string.Format("SQL CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), sqlExn);
            RetryRequest = false;
            throw;
        }
        catch (Exception exception)
        {
            Logger.Exception(string.Format("SOFTWARE CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), exception);
            RetryRequest = false;
            throw;
        }
    }
    return result;
}

But my modifications are not perfect. For example, after 3 retry with exception, the code doesn't throw and goes into the continue; section before going out of the loop.

score 3 · Answer 1 · answered Mar 08 '16 at 10:12

I have created a "RetryPolicy" class for this purpose.

The class:

public struct RetryPolicy<T>
{
    private int mRetryMax;
    private int mRetryWaitSec;

    public RetryPolicy(int retryMax, int retryWaitSec)
    {
        mRetryMax = retryMax;
        mRetryWaitSec = retryWaitSec;
    }

    public T DoWork(System.Func<T> func)
    {
        int retries = 0;

        while (true)
        {
            try
            {
                return func();
            }
            catch when (++retries < RetryMax)
            {
                Thread.Sleep(RetryWaitSec * 1000);
            }
        }
    }

    public int RetryMax
    {
        get
        {
            return mRetryMax;
        }
    }

    public int RetryWaitSec
    {
        get
        {
            return mRetryWaitSec;
        }

        set
        {
            mRetryWaitSec = value;
        }
    }
}

Example usage:

new RetryPolicy<int>(int.MaxValue, 1000).DoWork(() =>
{
  Connect(); return 0;
});

This way you can have one line client code that retries a number of times with a millisecond interval.

You could adjust it to a generic for just catching SQLException or whatever you want. Right now it catches all exceptions.

It's non static so you can cache the RetryPolicy during startup.

RetryPolicy policy = new RetryPolicy<int>(int.MaxValue, 1000);
// later
policy.DoWork(() => { Connect(); return 0; });

That's a clever use of `when`, although I'm not certain that it's safe to have side-effects. — Douglas, Mar 08 '16 at 13:45

score 0 · Accepted Answer · edited Sep 11 '20 at 12:41

I am assuming that you're aware of the caveats of automatic retries, especially when these concern non-idempotent operations.

Rather than using local variables for keeping track of success or failure, I would suggest using control flow keywords directly for this purpose:

public static int CallExecuteNonQuery(string storedProcName, Action<SqlCommand> fillParamsAction, Action afterExecution, BDDSource source)
{
    int retryCount = 0;   // recoverable exception will be rethrown 
                          // when this count reaches limit

    while (true)   // conditions for breaking out of loop inlined
    {
        try
        {
            using (var connection = InitSqlConnection(source))
            using (var command = new SqlCommand(storedProcName, connection))
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                command.CommandType = CommandType.StoredProcedure;
                if (fillParamsAction != null)
                    fillParamsAction(command);
                var result = command.ExecuteNonQuery();
                if (afterExecution != null)
                    afterExecution();
                return result;   // on success, return immediately
            }
        }
        catch (SqlException sqlExn)
        {
            // if error is recoverable, and retry count has not exceeded limit,
            // then retry operation
            if (sqlExn.Errors.Cast<SqlError>().All(x => (x.Class >= 16 && x.Class < 22) || x.Class == 24)
                && ++retryCount < Properties.Settings.Default.Request_MaximumRetry)
            {
                continue;
            }

            // otherwise, rethrow exception
            Logger.Exception(string.Format("SQL CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), sqlExn);
            throw;
        }
        catch (Exception exception)
        {
            Logger.Exception(string.Format("SOFTWARE CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), exception);
            throw;
        }
    }
}

score 0 · Answer 3 · edited Sep 11 '20 at 12:42

Your code seems utterly complex, also I'd take advantage of AggregatedException class to report all recoverable failures.

public static int CallExecuteNonQuery(string storedProcName, Action<SqlCommand> fillParamsAction, Action afterExecution, BDDSource source)
{
    int result = 0;
    var exceptions = new List<Exception>();

    while(true)
    {
        try
        {
            using (var connection = InitSqlConnection(source))
            using (var command = new SqlCommand(storedProcName, connection))
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();
                command.CommandType = CommandType.StoredProcedure;
                if (fillParamsAction != null)
                    fillParamsAction(command);
                result = command.ExecuteNonQuery();
                if (afterExecution != null)
                    afterExecution();
            }
            break;
        }
        catch (SqlException sqlExn)
        {
            if (sqlExn.Errors.Cast<SqlError>().All(x => (x.Class >= 16 && x.Class < 22) || x.Class == 24))
            {
                exceptions.Add(sqlExn);
                if (exceptions.Count == Properties.Settings.Default.Request_MaximumRetry)
                {
                    throw new AggregateException("Too many attempts.", exceptions);
                }
                continue;
            }

            Logger.Exception(string.Format("SQL CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), sqlExn);
            throw;
        }
        catch (Exception exception)
        {
            Logger.Exception(string.Format("SOFTWARE CRITICAL ERROR. Stored Proc Name : {0}", storedProcName), exception);
            throw;
        }
    }
    return result;
}

Automatically retry request on SqlException

3 Answers3