{tocify} $title={Table of Contents}
The implementation in this sample is as simple as possible to demonstrate step-by-step customization. It doesn't include advanced practices like thread safety, async, and concurrency. For a deep-dive into a real implementation, you can study the pre-defined retry logic in the Microsoft.Data.SqlClient GitHub repository.
Define custom configurable retry logic classes:
Enumerator:
Define a fixed sequence of time intervals and extend the acceptable range of
times from two minutes to four minutes.
public class CustomEnumerator : SqlRetryIntervalBaseEnumerator
{
// Set the maximum acceptable time to 4 minutes
private readonly TimeSpan _maxValue = TimeSpan.FromMinutes(4);
public CustomEnumerator(TimeSpan timeInterval, TimeSpan maxTime, TimeSpan minTime)
: base(timeInterval, maxTime, minTime) {}
// Return fixed time on each request
protected override TimeSpan GetNextInterval()
{
return GapTimeInterval;
}
// Override the validate method with the new time range validation
protected override void Validate(TimeSpan timeInterval, TimeSpan maxTimeInterval, TimeSpan minTimeInterval)
{
if (minTimeInterval < TimeSpan.Zero || minTimeInterval > _maxValue)
{
throw new ArgumentOutOfRangeException(nameof(minTimeInterval));
}
if (maxTimeInterval < TimeSpan.Zero || maxTimeInterval > _maxValue)
{
throw new ArgumentOutOfRangeException(nameof(maxTimeInterval));
}
if (timeInterval < TimeSpan.Zero || timeInterval > _maxValue)
{
throw new ArgumentOutOfRangeException(nameof(timeInterval));
}
if (maxTimeInterval < minTimeInterval)
{
throw new ArgumentOutOfRangeException(nameof(minTimeInterval));
}
}
}
Retry logic: Implement retry logic on any command that isn't part of an active transaction. Lower the number of retries from 60 to 20.
public class CustomRetryLogic : SqlRetryLogicBase
{
// Maximum number of attempts
private const int maxAttempts = 20;
public CustomRetryLogic(int numberOfTries,
SqlRetryIntervalBaseEnumerator enumerator,
Predicate<Exception> transientPredicate)
{
if (!(numberOfTries > 0 && numberOfTries <= maxAttempts))
{
// 'numberOfTries' should be between 1 and 20.
throw new ArgumentOutOfRangeException(nameof(numberOfTries));
}
// Assign parameters to the relevant properties
NumberOfTries = numberOfTries;
RetryIntervalEnumerator = enumerator;
TransientPredicate = transientPredicate;
Current = 0;
}
// Prepare this object for the next round
public override void Reset()
{
Current = 0;
RetryIntervalEnumerator.Reset();
}
public override bool TryNextInterval(out TimeSpan intervalTime)
{
intervalTime = TimeSpan.Zero;
// First try has occurred before starting the retry process.
// Check if retry is still allowed
bool result = Current < NumberOfTries - 1;
if (result)
{
// Increase the number of attempts
Current++;
// It's okay if the RetryIntervalEnumerator gets to the last value before we've reached our maximum number of attempts.
// MoveNext() will simply leave the enumerator on the final interval value and we will repeat that for the final attempts.
RetryIntervalEnumerator.MoveNext();
// Receive the current time from enumerator
intervalTime = RetryIntervalEnumerator.Current;
}
return result;
}
}
Provider: Implements a retry provider that retries on synchronous operations without a Retrying event. Adds TimeoutException to the existing SqlException transient exception error numbers.
public class CustomProvider : SqlRetryLogicBaseProvider
{
// Preserve the given retryLogic on creation
public CustomProvider(SqlRetryLogicBase retryLogic)
{
RetryLogic = retryLogic;
}
public override TResult Execute<TResult>(object sender, Func<TResult> function)
{
// Create a list to save transient exceptions to report later if necessary
IList<Exception> exceptions = new List<Exception>();
// Prepare it before reusing
RetryLogic.Reset();
// Create an infinite loop to attempt the defined maximum number of tries
do
{
try
{
// Try to invoke the function
return function.Invoke();
}
// Catch any type of exception for further investigation
catch (Exception e)
{
// Ask the RetryLogic object if this exception is a transient error
if (RetryLogic.TransientPredicate(e))
{
// Add the exception to the list of exceptions we've retried on
exceptions.Add(e);
// Ask the RetryLogic for the next delay time before the next attempt to run the function
if (RetryLogic.TryNextInterval(out TimeSpan gapTime))
{
Console.WriteLine($"Wait for {gapTime} before next try");
// Wait before next attempt
Thread.Sleep(gapTime);
}
else
{
// Number of attempts has exceeded the maximum number of tries
throw new AggregateException("The number of retries has exceeded the maximum number of attempts.", exceptions);
}
}
else
{
// If the exception wasn't a transient failure throw the original exception
throw;
}
}
} while (true);
}
public override Task<TResult> ExecuteAsync<TResult>(object sender, Func<Task<TResult>> function, CancellationToken cancellationToken = default)
{
throw new NotImplementedException();
}
public override Task ExecuteAsync(object sender, Func<Task> function, CancellationToken cancellationToken = default)
{
throw new NotImplementedException();
}
}
Create a retry provider instance consisting of the defined custom types:
public static SqlRetryLogicBaseProvider
CreateCustomProvider(SqlRetryLogicOption options)
{
// 1. create an enumerator instance
CustomEnumerator customEnumerator =
new CustomEnumerator(options.DeltaTime, options.MaxTimeInterval,
options.MinTimeInterval);
// 2. Use the enumerator object to
create a new RetryLogic instance
CustomRetryLogic customRetryLogic =
new CustomRetryLogic(5, customEnumerator, (e) => TransientErrorsCondition(e,
options.TransientErrors));
// 3. Create a provider using the
RetryLogic object
CustomProvider customProvider = new
CustomProvider(customRetryLogic);
return customProvider;
}
The
following function will evaluate an exception by using the given list of
retryable exceptions and the special TimeoutException exception to determine if
it's retryable:'
// Return true if the exception is a transient fault.
private static bool TransientErrorsCondition(Exception e, IEnumerable<int> retriableConditions)
{
bool result = false;
// Assess only SqlExceptions
if (retriableConditions != null && e is SqlException ex)
{
foreach (SqlError item in ex.Errors)
{
// Check each error number to see if it is a retriable error number
if (retriableConditions.Contains(item.Number))
{
result = true;
break;
}
}
}
// Other types of exceptions can also be assessed
else if (e is TimeoutException)
{
result = true;
}
return result;
}
Use the customized retry logic:
Define
the retry logic parameters:
// 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}
};
Use the customized retry logic:
Define
the retry logic parameters:
// 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 provider:
// Create a custom retry logic provider
SqlRetryLogicBaseProvider provider = CustomRetry.CreateCustomProvider(options);
Assign
the retry provider to the SqlConnection.RetryLogicProvider or SqlCommand.RetryLogicProvider:
// 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();
Don't
forget to enable the configurable retry logic switch before using it. For more
information, see Enable configurable retry logic.