What is good C# coding style for catching SQLException and retrying
I'd change the exception handling to only retry on certain errors:
- 1204, 1205 deadlocks
- -2 timeout
- -1 connection broken
These are the basic "retryable" errors
catch (SqlException ex)
{
if !(ex.Number == 1205 || ex.Number == 1204 || ... )
{
throw
}
retryCount++;
if (retryCount > MAX_RETRY) throw;
}
Edit, I clean forgot about waits so you don't hammer the SQL box:
- Add a 500 ms wait on deadlock
- Add a 5 sec delay on timeout
Edit 2:
I'm a Developer DBA, don't do much C#. My answer was to correct exception processing for the calls...
Thanks for all the feedback. I'm answering this myself so I can incorporate elements from the answers given. Please let me know if I've missed something. My method becomes:
var results = new List<UserSummaryDto>();
Retry<UsersDataContext>(ctx => results = ctx.SearchPhoneList(value, maxRows)
.Select(user => user.ToDto())
.ToList());
return results;
And I've refactored the original method for reuse. Still lots of levels of nesting. It also relies on there being a default constructor for the data context which may be too restrictive. @Martin, I considered including your PreserveStackTrace method but in this case I don't think it really adds enough value - good to know for future reference thanks:
private const int MAX_RETRY = 2;
private const double LONG_WAIT_SECONDS = 5;
private const double SHORT_WAIT_SECONDS = 0.5;
private static readonly TimeSpan longWait = TimeSpan.FromSeconds(LONG_WAIT_SECONDS);
private static readonly TimeSpan shortWait = TimeSpan.FromSeconds(SHORT_WAIT_SECONDS);
private enum RetryableSqlErrors
{
Timeout = -2,
NoLock = 1204,
Deadlock = 1205,
WordbreakerTimeout = 30053,
}
private void Retry<T>(Action<T> retryAction) where T : DataContext, new()
{
var retryCount = 0;
using (var ctx = new T())
{
for (;;)
{
try
{
retryAction(ctx);
break;
}
catch (SqlException ex)
when (ex.Number == (int) RetryableSqlErrors.Timeout &&
retryCount < MAX_RETRY)
{
Thread.Sleep(longWait);
}
catch (SqlException ex)
when (Enum.IsDefined(typeof(RetryableSqlErrors), ex.Number) &&
retryCount < MAX_RETRY)
{
Thread.Sleep(shortWait);
}
retryCount++;
}
}
}
My enum of retryables for sql looks like this:
SqlConnectionBroken = -1,
SqlTimeout = -2,
SqlOutOfMemory = 701,
SqlOutOfLocks = 1204,
SqlDeadlockVictim = 1205,
SqlLockRequestTimeout = 1222,
SqlTimeoutWaitingForMemoryResource = 8645,
SqlLowMemoryCondition = 8651,
SqlWordbreakerTimeout = 30053