[C #] How to introduce retry logic in LINQ to SQL to solve timeouts?

Question

I need to find ways to add a 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 correct code or any complications.

It would be nice to throw an exception after MAX_RETRIES is reached, but I don’t know how and where to throw it :-)

Any help appreciated.

Answer 1

If you get a timeout from your database, it is not very likely that it will respond in a timely manner a few milliseconds later.

Repeated attempt in a compressed cycle, in your opinion, is likely to make the situation worse , because you will put excessive load on the database server, as well as bind the thread in the calling code.It would be safer to enter a timeout between each attempt.

For more complex scenarios, you might think of a progressive wait pattern in which you try again more often at the beginning, and then at longer and longer intervals if you still have timeouts.

You can also look into the Circuit Breaker design template from Release It! , as well as many other patterns and anti-patterns described in this book.

The status template is suitable for implementing a circuit breaker.

Answer 2

Personally, I would use recursion here. This makes the code cleaner since you only have one “extra code” - this is a parameter to the function. For instance:

 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 a perfect example of recursion, I think. Everything that causes this should not touch the loop, and it makes MUCH cleaner code.

Answer 3

As Mark Semann correctly mentioned, it is not recommended to use a retry policy to deal with timeouts. However, given the delay, this might be a good idea. To implement it, you can use a custom action invoker that executes your action method and takes care of retries in case of SQL exception. This way, you don’t have to care about the retry policy in every action method code.

We do not have a database timeout on our system, but I use the same technique to handle sql-read deadlocks in a general way.