[C#]How to introduce retry logic into LINQ to SQL to deal with timeouts?

I need to find ways to add retry mechanism to my DB calls in case of timeouts, LINQ to SQL is used to call some sprocs in my code...

using (MyDataContext dc = new MyDataContext())
{
    int result = -1; //d开发者_JAVA百科enote failure
    int count = 0;

    while ((result < 0) && (count < MAX_RETRIES))
    {
        result = dc.myStoredProc1(...);
        count++;
    }

    result = -1;
    count  = 0;
    while ((result < 0) && (count < MAX_RETRIES))
    {
        result = dc.myStoredProc2(...);
        count++;
    }

    ...

    ...
}

Not sure if the code above is right or posed any complications.

It'll be nice to throw an exception after MAX_RETRIES has reached, but I dunno how and where to throw them appropriately :-)

Any helps appreciated.

If you get a timeout from your database, it's not very likely that it's going to respond in a timely fashion a few miliseconds later.

Retrying in a tight loop as you suggest is likely to make a bad situation worse because you would be placing an undue burden on the database server, as well as tying up a thread in the calling code. It would be safer to introduce a wait time between each retry.

For more advanced scenarios, you could consider a progressive wait pattern where you retry more frequently in the beginning and then with longer and longer intervals if you still get timeouts.

You may also want to look into the Circuit Breaker design pattern from the book Release It!, as well as many of the other patterns and anti-patterns described in that book.

The State pattern is a good fit for implementing Circuit Breaker.

Personally, I would use recursion here. It makes for cleaner code since the only "extra code" you have is a parameter into a function. For example:

private MyResult Foo(MyParameters mp, int repeatCall)
{
    var result = null;

    try
    {
        result = mp.dc.myStoredProc(...);
    }
    catch (MyException err)
    {
        if (repeatCall > 0)
        {
            result = Foo(mp, repeatCall - 1);
        }
        else
        {
            throw;
        }
    }

    return result;
}

This is an ideal example for recursion, I think. Whatever is calling this need not be concerned with the looping and it makes MUCH cleaner code.

As Mark Seemann corrrectly mentioned, it's not a good idea to use a retry policy to deal with with timeouts. However, given a delay this might be a good idea after all. To implement it, you can use a custom action invoker that executes your action method and takes care of retries in case of an SQL exception. This way you don't have to take care of the retry policy in each and every action method's code.

We don't have database timeout in our system, but I am using the same technique to handle sql read deadlocks in a generic way.

We use something like this (prefer that a new EF Context is instantiated for each retry):

Sorry but the code for SqlExceptionUtil.IsSqlServerErrorType() could not be included (overly custom and many layers).

static public T ExecuteRetryable<T>(
    Func<T> function,
    out int actualAttempts,
    string actionDescriptionForException = "SQL",
    int maxTries = 3,
    int pauseMaxMillis = 1000,
    int pauseMinMillis = 0,
    bool alsoPauseBeforeFirstAttempt = false,
    bool allowRetryOnTimeout = false)
{
    Exception mostRecentException = null;

    for (int i = 0; i < maxTries; i++)
    {
        // Pause at the beginning of the loop rather than end to catch the case when many servers
        // start due to inrush of requests (likely).  Use a random factor to try and avoid deadlock 
        // in the first place.
        //
        if (i > 0 || alsoPauseBeforeFirstAttempt)
            Thread.Sleep(new Random
            (
                // Default Initializer was just based on time, help the random to differ when called at same instant in different threads.
                (Int32)((DateTime.Now.Ticks + Thread.CurrentThread.GetHashCode() + Thread.CurrentThread.ManagedThreadId) % Int32.MaxValue)
            )
            .Next(pauseMinMillis, pauseMaxMillis));

        actualAttempts = i + 1;

        try
        {
            T returnValue = function();
            return returnValue;
        }
        catch (Exception ex)
        {
            // The exception hierarchy may not be consistent so search all inner exceptions.
            // Currently it is DbUpdateException -> UpdateException -> SqlException 
            //
            if (!SqlExceptionUtil.IsSqlServerErrorType(ex, SqlServerErrorType.Deadlock) &&                    
                (!allowRetryOnTimeout || !SqlExceptionUtil.IsSqlServerErrorType(ex, SqlServerErrorType.Timeout)))
                throw;

            mostRecentException = ex;
        }
    }

    throw new Exception(
        "Unable to perform action '" + actionDescriptionForException + "' after " + maxTries +
        " tries with pauses of [" + pauseMinMillis + "," + pauseMaxMillis + "]ms due to multiple exceptions.",
        mostRecentException);
}

Usage:

List<SomeTableEntity> result = DatabaseHelpers.ExecuteRetryable<List<SomeTableEntity>>(() =>
    {           
        using (EfCtx ctx = new EfCtx())
        {
            return ctx.SomeTable.Where(...).ToList()
        }
    }, out int actualAttempts, allowRetryOnTimeout: true);

It would be nice if someone would show how to disguise the wrapping code behind a custom Linq construct like: WithRetry(...).