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

Question

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; //denote 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.

Answer 1

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.

Answer 2

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.