Dapper: Retry mechanism for transient errors

[PaulDMendoza]

It would be great if there was a simple additional optional parameters on the Query and Execute methods for retrying a query automatically to handle transient errors.

[devlead]

Indeed, the Microsoft Transient Fault Handling Application Block has some good patterns with its retrying policies
http://msdn.microsoft.com/en-us/library/hh680899(v=PandP.50).aspx

[ianlee74]

Has anything been done in this regard? As cloud databases are quickly becoming the norm, this is becoming more important. Does someone have an example of how they are possibly using Dapper in conjunction with the application block mentioned by @devlead? I agree this is something that should be built into Dapper, though.

[devlead]

@ianlee74 one could probably do something like below (this could easily be wrapped in a "utility/contrib" function)

using System;
using System.Collections.Generic;
using System.Linq;
using Dapper;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;

namespace TransientDapper
{
    internal static class Program
    {
        private class Customer
        {
            public int CustomerId { get; set; }
        }

        private static void Main()
        {
            const int retryCount = 4;
            const int minBackoffDelayMilliseconds = 2000;
            const int maxBackoffDelayMilliseconds = 8000;
            const int deltaBackoffMilliseconds = 2000;

            var exponentialBackoffStrategy =
                new ExponentialBackoff(
                    "exponentialBackoffStrategy",
                    retryCount,
                    TimeSpan.FromMilliseconds(minBackoffDelayMilliseconds),
                    TimeSpan.FromMilliseconds(maxBackoffDelayMilliseconds),
                    TimeSpan.FromMilliseconds(deltaBackoffMilliseconds)
                    );

            var manager = new RetryManager(
                new List<RetryStrategy>
                {
                    exponentialBackoffStrategy
                },
                exponentialBackoffStrategy.Name
                );

            RetryManager.SetDefault(manager);

            using (var conn = new ReliableSqlConnection("Server=localhost;Database=master;Trusted_Connection=True;"))
            {
                conn.Open();
                var customers = conn.CommandRetryPolicy.ExecuteAction(
                    () => conn.Query<Customer>(
                        "SELECT 1 AS CustomerId"
                        )
                    );
                Console.WriteLine(
                    "Result: {0}",
                    customers.Select(customer => customer.CustomerId).FirstOrDefault()
                    );
            }
        }
    }
}

This is using these Nuget packages

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="Dapper" version="1.42" targetFramework="net45" />
  <package id="EnterpriseLibrary.TransientFaultHandling" version="6.0.1304.0" targetFramework="net45" />
  <package id="EnterpriseLibrary.TransientFaultHandling.Data" version="6.0.1304.1" targetFramework="net45" />
</packages>

[ianlee74]

@devlead - Thanks! I'll give it a try.

[ianlee74]

@devlead - we've implemented something based off your sample and it seems to be working great. It makes the code a bit more verbose and detracts from the simple, elegant nature of Dapper but its understandable. Thanks!

[devlead]

@ianlee74 excellent! Glad to be of assistance. You could probably if you haven't already do an QueryWithRetry extension method that would make the API very similar to what Dapper offers today, this is a quick rewrite of code above but should work:)
Move all dapper foreign logic into a TransientDapperExtensions.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling;

namespace TransientDapper
{
    public static class TransientDapperExtensions
    {
        private static readonly RetryManager SqlRetryManager = GetDefaultRetryManager();
        private static readonly RetryPolicy SqlCommandRetryPolicy = SqlRetryManager.GetDefaultSqlCommandRetryPolicy();
        private static readonly RetryPolicy SqlConnectionRetryPolicy =
            SqlRetryManager.GetDefaultSqlConnectionRetryPolicy();

        private static RetryManager GetDefaultRetryManager()
        {
            const int retryCount = 4;
            const int minBackoffDelayMilliseconds = 2000;
            const int maxBackoffDelayMilliseconds = 8000;
            const int deltaBackoffMilliseconds = 2000;

            var exponentialBackoffStrategy =
                new ExponentialBackoff(
                    "exponentialBackoffStrategy",
                    retryCount,
                    TimeSpan.FromMilliseconds(minBackoffDelayMilliseconds),
                    TimeSpan.FromMilliseconds(maxBackoffDelayMilliseconds),
                    TimeSpan.FromMilliseconds(deltaBackoffMilliseconds)
                    );

            var manager = new RetryManager(
                new List<RetryStrategy>
                {
                    exponentialBackoffStrategy
                },
                exponentialBackoffStrategy.Name
                );

            return manager;
        }

        public static void OpenWithRetry(this SqlConnection cnn)
        {
            cnn.OpenWithRetry(SqlConnectionRetryPolicy);
        }

        public static IEnumerable<T> QueryWithRetry<T>(
            this SqlConnection cnn, string sql, object param = null, IDbTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, CommandType? commandType = null
            )
        {
            return SqlCommandRetryPolicy.ExecuteAction(
                () => cnn.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType)
                );
        }
    }
}

Then using the transient fault handling would look and feel very similar to regular dapper code:

using System;
using System.Data.SqlClient;
using System.Linq;

namespace TransientDapper
{
    internal static class Program
    {
        private class Customer
        {
            public int CustomerId { get; set; }
        }

        private static void Main(string[] args)
        {
            if (args == null) throw new ArgumentNullException("args");

            using (var conn = new SqlConnection("Server=localhost;Database=master;Trusted_Connection=True;"))
            {
                conn.OpenWithRetry();

                var customers = conn.QueryWithRetry<Customer>(
                    "SELECT 1 AS CustomerId"
                    );
                Console.WriteLine(
                    "Result: {0}",
                    customers.Select(customer => customer.CustomerId).FirstOrDefault()
                    );
            }
        }
    }
}

[ianlee74]

@devlead Well, now that is elegant and sadly I didn't do it that way before refactoring 100 or so functions. :( I will definitely come back around for another round of refactoring again sometime in the near future. Thanks!

[devlead]

@ianlee74 sorry about that ;) But this could be very useful, could very easily be converted to an Dapper.Transient nuget, wrapping existing methods. It could be in a different namespace and SqlConnection as extension base, but otherwise same signature. Then refactoring to implement transient fault handling would be something like changing using Dapper; to using Dapper.Transient;

[ianlee74]

@devlead Agreed. How do we get this added as a standard Dapper Nuget package?

[ianlee74]

I'd also add that I refactored all our functions to use ExecuteAsync() instead of ExcecuteAction(). So, I would naturally add a second extension there called QueryWithRetryAsync().

[devlead]

Perhaps check with @NickCraver, @mgravell and the powers of be at StackExchange for an opinion if they think this would be something worth pursuing as an official part of Dapper. ;)

[NickCraver]

Thanks for the ping! I'll be up front: I'd be very hesitant to add this to core Dapper because of the detraction of simplicity likely leading to some feet with bullet holes.

The problem here is that many developers either for speed or just not knowing any better do not handle updates and such in transactions, and even if they did there are potential problems. Let's say he failure happens on the way out, a connection severing to SQL server on the result set after some piece has been committed. Whether this is one statement or an entire batch, anything mutating leaves some bad state. In the batch case, it leaves you in an unknown bad state (unless using multi-readers, potentially). The libraries from Microsoft handle _some_ of this, but things like Query<T> that are actually causing mutations aren't handled all that well.

For example, inserting an order from a user may have succeeded on the insert and then suffered a connection severance. The way the library is currently written, depending on the method call, it may insert twice. I'm not sure behavior like that belongs in core Dapper in a way we "approve" of it by association. Again, my opinion there - I don't speak for everyone of course.

However, I think this is an excellent package on its own and it should expose the caveats of what is and isn't well supported right in the main package description. It's a much better scenario than such info being a buried description or footnote in Dapper proper.

[ianlee74]

@NickCraver - Good points. Someone using the retry functions blindly without knowing or considering the consequences could definitely get unexpected results. I can understand you not wanting to add it as a supported Dapper package.

[NickCraver]

Closing this out to cleanup - hopefully with the above reasoning addressing concerns. If there's a case that's clearly a net win ping me and I'll re-open this.

[digitalmedia34]

I just saw this and I know its closed but for the future Polly is really good and easy to use. Ideal for this scenario

[RehanSaeed]

FYI, Entity Framework Core ships with support for retrying when known transient exceptions occur. See SqlServerTransientExceptionDetector.cs for the SQL Server implementation. See also the docs on connection resiliency. I came here looking for a Dapper equivelant.