public static async Task <int> SqlExecuteNonQueryWithRetryAsync(SqlConnection connection, SqlCommand sqlCmd,
Func <RetryCheckParameter, bool> canRetry, bool ignoreInsertPKException = false)
{
var retryParamenter = new RetryCheckParameter()
{
EndRetryTime = DateTime.UtcNow, RetryCount = 0
};
sqlCmd.Connection = connection;
while (true)
{
try
{
await OpenConnectionAsync(connection);
return(await sqlCmd.ExecuteNonQueryAsync());
}
catch (SqlException e)
{
// if specified, ignore primary key violations
if (IsInsertPKException(e, ignoreInsertPKException))
{
// ignoreInsertPKException = insert && newItem
return(-1);
}
retryParamenter.Exception = e;
if (!canRetry(retryParamenter))
{
throw;
}
}
}
}
public static async Task <SqlDataReader> SqlExecuteReaderWithRetryAsync(SqlConnection connection, SqlCommand sqlCmd, Func <RetryCheckParameter, bool> canRetry,
CommandBehavior cmdBehavior = CommandBehavior.Default)
{
var retryParamenter = new RetryCheckParameter()
{
EndRetryTime = DateTime.UtcNow, RetryCount = 0
};
sqlCmd.Connection = connection;
while (true)
{
try
{
await OpenConnectionAsync(connection);
return(await sqlCmd.ExecuteReaderAsync(cmdBehavior));
}
catch (SqlException e)
{
retryParamenter.Exception = e;
if (!canRetry(retryParamenter))
{
throw;
}
}
}
}
private bool CanRetry(RetryCheckParameter parameter)
{
if (parameter.RetryCount >= _maxRetryNum ||
!ShouldUseInMemoryTableRetry(parameter.Exception))
{
return(false);
}
// this actually may sleep up to 15ms
// but it's better than spinning CPU
Thread.Sleep(_retryIntervalMilSec);
parameter.RetryCount++;
return(true);
}
private bool CanRetry(RetryCheckParameter parameter)
{
if (_retryIntervalMilSec <= 0 ||
!SqlSessionStateRepositoryUtil.IsFatalSqlException(parameter.Exception) ||
parameter.RetryCount >= _maxRetryNum)
{
return(false);
}
// sleep the specified time and allow retry
Thread.Sleep(_retryIntervalMilSec);
parameter.RetryCount++;
return(true);
}
