Implementing retry logic for deadlock exceptions

Question

I've implemented a generic repository and was wondering if there is a smart way to implement a retry logic in case of a deadlock exception?

The approach should be the same for all repository methods. So is there anyway I can avoid writing 'try/catch - call method again with retry-count', in every single method?

Any suggetsion are welcome.

A bit of my Repository code:

public class GenericRepository : IRepository
{
    private ObjectContext _context;

    public List<TEntity> ExecuteStoreQuery<TEntity>(string commandText, params object[] parameters) where TEntity : class
    {
        List<TEntity> myList = new List<TEntity>();

        var groupData = _context.ExecuteStoreQuery<TEntity>(commandText, parameters);

        return myList;
    }


    public IQueryable<TEntity> GetQuery<TEntity>() where TEntity : class
    {          
        var entityName = GetEntityName<TEntity>();
        return _context.CreateQuery<TEntity>(entityName);
    }

    public IEnumerable<TEntity> GetAll<TEntity>() where TEntity : class
    {
        return GetQuery<TEntity>().AsEnumerable();
    }

EDIT:

1.Solution:

Modified slightly from chris.house.00's solution

 public static T DeadlockRetryHelper<T>(Func<T> repositoryMethod, int maxRetries)
    {
        var retryCount = 0;

        while (retryCount < maxRetries)
        {
            try
            {
                return repositoryMethod();
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                if (ex.Number == 1205)// Deadlock                         
                    retryCount++;
                else
                    throw;                   
            }
        }
        return default(T);
    }

And you call it like this:

    public TEntity FirstOrDefault<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class
    {
        return RetryUtility.DeadlockRetryHelper<TEntity>( () =>p_FirstOrDefault<TEntity>(predicate), 3);
    }

    protected TEntity p_FirstOrDefault<TEntity>(Expression<Func<TEntity, bool>> predicate) where TEntity : class
    {
        return GetQuery<TEntity>().FirstOrDefault<TEntity>(predicate);
    }

Touch

Whats wrong with a try statement? – Runic

I think the OP wants to avoid repeating try...catch blocks in every single repo method. – Cressler

Ensuring your repository can handle deadlock retries could be difficult. Even if you consolidate the calls to the repository, each respository function will still have to be tested. – Sloven

Answer 1

How about something like this:

public T DeadlockRetryHelper<T>(Func<T> repositoryMethod, int maxRetries)
{
  int retryCount = 0;

  while (retryCount < maxRetries)
  {
    try
    {
      return repositoryMethod();
    }
    catch (SqlException e) // This example is for SQL Server, change the exception type/logic if you're using another DBMS
    {
      if (e.Number == 1205)  // SQL Server error code for deadlock
      {
        retryCount++;
      }
      else
      {
        throw;  // Not a deadlock so throw the exception
      }
      // Add some code to do whatever you want with the exception once you've exceeded the max. retries
    }
  }
}

With the above code, your retry logic is all in this method and you can just pass your repository method in as a delegate.

Ferrick

Be careful to catch only the deadlock exceptions. Otherwise you may be simply repeating an error or, worse, performing the same function with the same side-effects multiple times. – Sloven

OP didn't specify what DBMS or ORM he's using so I opted not to make assumptions on how he'd detect a deadlock. That being said, you raise a good point so I edited to make the example specific to SQL Server/ADO.Net to give the code more clarity. – Ferrick

Was something like this i was experimenting with. Modified yours a bit to in case someone would like to re-use it for deadlocks :) – Touch

If you exhaust your retries, then the function will return without throwing an exception. – Emblematize

Yup, it sure does. See the comment in the code that states this. Since the OP didn't state exactly what sort of behavior he wanted, I added the comment to make it clear that if some behavior was desired when max retries is exceeded, code should be added at that point. – Ferrick

your code is fragile because the repositoryMethod() can not only run in its own sql-transaction, but use transaction as parameter in closure. And in this case you should retry the whole call-stack. – Balfore

You should pause briefly between retries to give the lock-holder time to release its locks. You should also pause for a random interval to prevent mutual deadlocks from retrying at the same time and deadlocking again. – Partake

With C# 6 catch (SqlException ex) when (ex.Number == 1205) – Harmonia

Answer 2

I know this is an old post but wanted to share an updated answer.

EF 6 now has a built-in solution, you can set the execution strategy which would be a one time implementation. You create a class that inherits from DbExectutionStrategy and overrides the ShouldRetryOn() virtual method. You can create a static class of the exceptions containing constant field valuess that are retry eligible codes and loop through each one to determine if the current sql exception being thrown matches the list of eligible retry codes...

 public static class SqlRetryErrorCodes
{
    public const int TimeoutExpired = -2;
    public const int Deadlock = 1205;
    public const int CouldNotOpenConnection = 53;
    public const int TransportFail = 121;
}

public class MyCustomExecutionStrategy : DbExecutionStrategy
{
    public MyCustomExecutionStrategy(int maxRetryCount, TimeSpan maxDelay) : base(maxRetryCount, maxDelay) { }

     private readonly List<int> _errorCodesToRetry = new List<int>
    {
        SqlRetryErrorCodes.Deadlock,
        SqlRetryErrorCodes.TimeoutExpired,
        SqlRetryErrorCodes.CouldNotOpenConnection,
        SqlRetryErrorCodes.TransportFail
    };
    protected override bool ShouldRetryOn(Exception exception)
    {
        var sqlException = exception as SqlException;
        if (sqlException != null)
        {
            foreach (SqlError err in sqlException.Errors)
            {
                // Enumerate through all errors found in the exception.
                if (_errorCodesToRetry.Contains(err.Number))
                {
                    return true;
                }
            }
        }
        return false;
    }
}

Finally once, you've set up your custom execution strategy, you simply create another class that inherits from DbConfiguration with a public constructor that Sets the execution strategy:

 public class MyEfConfigurations : DbConfiguration
    {
        public MyEfConfigurations()
        {
            SetExecutionStrategy("System.Data.SqlClient",() => new MyCustomExecutionStrategy(5,TimeSpan.FromSeconds(10)));
        }
    }

Wraparound

Answer 3

EntityFramework 6 add ExecutionStrategy feature. All that is need is to setup up the strategy properly.

My retry policy:

public class EFRetryPolicy : DbExecutionStrategy
{
    public EFRetryPolicy() : base()
    {
    }
    //Keep this constructor public too in case it is needed to change defaults of exponential back off algorithm.
    public EFRetryPolicy(int maxRetryCount, TimeSpan maxDelay): base(maxRetryCount, maxDelay)
    {
    }
    protected override bool ShouldRetryOn(Exception ex)
    {

        bool retry = false;

        SqlException sqlException = ex as SqlException;
        if (sqlException != null)
        {
            int[] errorsToRetry =
            {
                1205,  //Deadlock
                -2,    //Timeout
            };
            if (sqlException.Errors.Cast<SqlError>().Any(x => errorsToRetry.Contains(x.Number)))
            {
                retry = true;
            }

        }          
        return retry;
    }
}

Tell EF to apply my strategy:

public class EFPolicy: DbConfiguration
{
    public EFPolicy()
    {
        SetExecutionStrategy(
            "System.Data.SqlClient",
            () => new EFRetryPolicy());
    }
}

Sources:

• Implementing Connection Resiliency with Entity Framework 6
• Microsoft documentation

The retry strategy will not work with user initiated transactions (transaction created with TransactionScope) as explained here. If used you will get the Error The configured execution strategy does not support user initiated transactions

Shipping

Answer 4

The solution works though I prefer not to have to worry about the number of arguments to the Action or Func that will be retired. If you create a single retry method with a generic Action, you can handle all of the variability of the method to be called in a lambda:

public static class RetryHelper
{

    public static void DeadlockRetryHelper(Action method, int maxRetries = 3)
    {
        var retryCount = 0;

        while (retryCount < maxRetries)
        {
            try
            {
                method();
                return;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                if (ex.Number == 1205)// Deadlock           
                {
                    retryCount++;
                    if (retryCount >= maxRetries)
                        throw;
                    // Wait between 1 and 5 seconds
                    Thread.Sleep(new Random().Next(1000, 5000));
                }
                else
                    throw;
            }
        }

    }
}

Then use it like so:

RetryHelper.DeadlockRetryHelper(() => CopyAndInsertFile(fileModel));

Burnette

Answer 5

Have you considered some form of policy injection? You could use Unity interception, just as an example, to capture all your repository calls. Then you just write the retry logic once, in the interceptor, rather than repeating it many times in each method.

Cressler

Sounds just like what I'm looking fore. Do you have an example, or link to simular use? – Touch

Answer 6

I have used the below solution provided by MiguelSlv in above post and it worked for me as expected. Its simple and easy.

EntityFramework 6 add ExecutionStrategy feature. All that is need is to setup up the strategy properly.

My retry policy:

public class EFRetryPolicy : DbExecutionStrategy
{
    public EFRetryPolicy() : base()
    {
    }
    //Keep this constructor public too in case it is needed to change defaults of exponential back off algorithm.
    public EFRetryPolicy(int maxRetryCount, TimeSpan maxDelay): base(maxRetryCount, maxDelay)
    {
    }
    protected override bool ShouldRetryOn(Exception ex)
    {

        bool retry = false;

        SqlException sqlException = ex as SqlException;
        if (sqlException != null)
        {
            int[] errorsToRetry =
            {
                1205,  //Deadlock
                -2,    //Timeout
            };
            if (sqlException.Errors.Cast<SqlError>().Any(x => errorsToRetry.Contains(x.Number)))
            {
                retry = true;
            }
        }          
        return retry;
    }
}

Tell EF to apply this policy

public class EFPolicy: DbConfiguration
{
    public EFPolicy()
    {
        SetExecutionStrategy(
            "System.Data.SqlClient",
                () => new EFRetryPolicy());
    }
}

Sources:

Implementing Connection Resiliency with Entity Framework 6 Microsoft documentation The retry strategy will not work with user initiated transactions (transaction created with TransactionScope) as explained here. If used you will get the Error The configured execution strategy does not support user initiated transactions

Osmond