Transient Fault Retry logic best practices

Question

Friends, I have a question about implementing a simple retry policy around the execution of the SQL command.

My question is: should the retry loop encapsulate the construction of the connection and transaction, or should it live inside the connection.

For example:

private void RetryLogSave(DynamicParameters parameters, int retries = 3)
{    
    int tries = 0;

    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        using (var transaction = connection.BeginTransaction())
        {
            var logItemCommand = new CommandDefinition(commandText: Constants.InsertLogItem,
                parameters: parameters, transaction: transaction, commandType: System.Data.CommandType.Text);

            do
            {
                try
                {
                    tries++;
                    connection.Execute(logItemCommand);
                    transaction.Commit();
                    break;
                }
                catch (Exception exc)
                {
                    if (tries == retries)
                    {
                        transaction.Rollback();
                        throw exc;
                    }
                    Task.Delay(100 * tries).Wait();
                }
            }
            while (true);
        }
}
}

Is what I've done here appropriate and acceptable, or should the retry logic live on the outside of the SqlConnection construction?

Answer 1

Formalizing my comments as an answer.

should the retry logic live on the outside of the SqlConnection construction?

Yes. If doing retry logic with keeping connection opened you're wasting resources. Someone else may use it while you're waiting N seconds for re-try. Opening/closing connections is usually (for most ODBC drivers) implemented on top of Connection Pooling mechanism. You do not actually close it - you allow connection to go back in pool to be reused by someone else. Keeping connections opened during re-try will force system to create more and more new physical connections (because they are not returned to the pool) and eventually your SQL Server will be exhausted.

Regarding re-try mechanism - to not reinvent the wheel, I usually use Polly library.

You can define somewhere static class with list of your polices:

public static class MyPolices
{
    // Retry, waiting a specified duration between each retry
    public static Policy RetryPolicy = Policy
       .Handle<Exception>() // can be more specific like SqlException
       .WaitAndRetry(new[]
       {
          TimeSpan.FromSeconds(1),
          TimeSpan.FromSeconds(2),
          TimeSpan.FromSeconds(3)
       });
}

Then, simplify your method to

private void LogSave(DynamicParameters parameters)
{    
    using (var connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        using (var transaction = connection.BeginTransaction())
        {
            // make sure to not forget to dispose your command
            var logItemCommand = new CommandDefinition(commandText: Constants.InsertLogItem,
                parameters: parameters, transaction: transaction, commandType: System.Data.CommandType.Text);

            try
            {
                // not sure if conn.Execute is your extension method?
                connection.Execute(logItemCommand);
                transaction.Commit();
            }
            catch (Exception exc)
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

and call it like this

MyPolices.RetryPolicy.Execute(() => LogSave(parameters));

This approach will make your code more SOLID keeping retry logic in isolation.