Retry
A utility to retry certain ADO commands that fail with specific transient errors.
If you use EntityFramework, you can set an ExecutionStrategy for Azure-type transient errors (DbConfiguration with SetExecutionStrategy("System.Data.SqlClient", () => new System.Data.Entity.SqlServer.SqlAzureExecutionStrategy());)
This is a little utility for non-EF code which retries deadlock errors in a high-contention database. It's static for simplicity, but it could be non-static with a true strategy pattern. We try 5 times, with a sleep of 1/2 second between each try. There are synchronous and asynchronous versions.
Retry
using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading.Tasks;
namespace Data
{
/// <summary>
/// Execute ADO commands and retry transient errors
/// </summary>
public static class Retry
{
/// <summary>
/// Executes the specified operation.
/// </summary>
/// <typeparam name="TResult">The type of the result.</typeparam>
/// <param name="operation">The operation.</param>
public static TResult Execute<TResult>(Func<TResult> operation)
{
const int maxRetries = 5;
var retry = 0;
for (; ; )
{
retry++;
try
{
return operation();
}
catch (SqlException exception)
{
if (retry == maxRetries || !SqlTransientErrorDetectionStrategy.IsTransient(exception))
{
throw;
}
Trace.TraceWarning("Will retry deadlock " + exception);
}
System.Threading.Thread.Sleep(500);
}
}
/// <summary>
/// Executes the specified asynchronous operation.
/// </summary>
/// <typeparam name="TResult">The type of the result.</typeparam>
/// <param name="operation">The asynchronous operation.</param>
public static async Task<TResult> Execute<TResult>(Func<Task<TResult>> operation)
{
const int maxRetries = 5;
var retry = 0;
for (; ; )
{
retry++;
try
{
return await operation();
}
catch (SqlException exception)
{
if (retry == maxRetries || !SqlTransientErrorDetectionStrategy.IsTransient(exception))
{
throw;
}
Trace.TraceWarning("Will retry deadlock " + exception);
}
await Task.Delay(500);
}
}
}
}
ExceptionDetectionStrategy
using System;
using System.Data.SqlClient;
namespace Data
{
/// <summary>
/// A strategy for detecting transient errors. Equivalent to SqlAzureRetriableExceptionDetector in EF
/// </summary>
public static class SqlTransientErrorDetectionStrategy
{
/// <summary>
/// Determines whether the specified exception is transient.
/// </summary>
/// <param name="ex">The exception.</param>
public static bool IsTransient(Exception ex)
{
if (ex == null) return false;
SqlException sqlException;
if ((sqlException = ex as SqlException) != null)
{
// Enumerate through all errors found in the exception.
foreach (SqlError err in sqlException.Errors)
{
switch (err.Number)
{
case 1205: //SqlDeadlockVictim
case 1204: //SqlOutOfLocks
case 1222: //SqlLockRequestTimeout
return true;
}
}
}
return false;
}
}
}
Some Tests
Uses Sam Saffron's Stack Overflow answer to create a SqlException with reflection.
using System.Data.SqlClient;
using System.Diagnostics.CodeAnalysis;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;
using Data;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace Tests.Data
{
[TestClass, ExcludeFromCodeCoverage]
public class WhenDatabaseCalledWithRetry
{
private int _callCount;
private int _errorCount;
[TestMethod]
public async Task GivenDatabaseErrorsThenRetryIsCalled()
{
Log.TestName();
//arrange
_callCount = 0;
_errorCount = 0;
//act
var result = await Retry.Execute(Save);
//assert
Assert.AreEqual(10, result);
Assert.AreEqual(5, _callCount);
Assert.AreEqual(4, _errorCount);
}
[TestMethod]
public async Task GivenDatabaseErrorsThenRetryIsCalledUntilFails()
{
//arrange
_callCount = 0;
_errorCount = 0;
var failed = false;
//act
try
{
await Retry.Execute(async () =>
{
await Task.Delay(1);
_callCount++;
_errorCount++;
throw SqlExceptionMocker.MakeSqlException(1205);
return 10;
});
}
catch (SqlException)
{
failed = true;
}
//assert
Assert.IsTrue(failed);
Assert.AreEqual(5, _callCount);
Assert.AreEqual(5, _errorCount);
}
[TestMethod]
public async Task GivenGeneralDatabaseErrorsThenFailImmediately()
{
//arrange
_callCount = 0;
_errorCount = 0;
var failed = false;
//act
try
{
await Retry.Execute(async () =>
{
await Task.Delay(1);
_callCount++;
_errorCount++;
throw SqlExceptionMocker.MakeSqlException(999);
return 10;
});
}
catch (SqlException)
{
failed = true;
}
//assert
Assert.IsTrue(failed);
Assert.AreEqual(1, _callCount);
Assert.AreEqual(1, _errorCount);
}
[TestMethod]
public async Task GivenNoDatabaseErrorsThenCalledOnce()
{
//arrange
_callCount = 0;
_errorCount = 0;
//act
var result = await Retry.Execute(async () =>
{
await Task.Delay(1);
_callCount++;
return 10;
});
//assert
Assert.AreEqual(10, result);
Assert.AreEqual(1, _callCount);
Assert.AreEqual(0, _errorCount);
}
private async Task<int> Save()
{
await Task.Delay(1);
_callCount++;
if (_callCount < 5)
{
_errorCount++;
throw SqlExceptionMocker.MakeSqlException(1205);
}
return 10;
}
static class SqlExceptionMocker
{
private static T Construct<T>(params object[] p)
{
var ctor = (from ctors in typeof(T).GetConstructors(BindingFlags.NonPublic | BindingFlags.Instance)
where ctors.GetParameters().Count() == p.Count()
select ctors
).Single();
return (T)ctor.Invoke(p);
}
public static SqlException MakeSqlException(int errorNumber)
{
var collection = Construct<SqlErrorCollection>();
var error = Construct<SqlError>(errorNumber, (byte)2, (byte)3, "server name", "This is a Mock-SqlException", "proc", 100);
typeof(SqlErrorCollection)
.GetMethod("Add", BindingFlags.NonPublic | BindingFlags.Instance)
.Invoke(collection, new object[] { error });
var e = typeof(SqlException)
.GetMethod("CreateException", BindingFlags.NonPublic | BindingFlags.Static, null, CallingConventions.ExplicitThis, new[] { typeof(SqlErrorCollection), typeof(string) }, new ParameterModifier[] { })
.Invoke(null, new object[] { collection, "7.0.0" }) as SqlException;
return e;
}
}
}
}