efcore: Delay first retry in Transient Error Handling with Azure SQL

@MNF this is what we’re using now - I removed all the extra logic to close and reopen the connection since it turned out to be unnecessary. The only things that this strategy does that’s different from the built-in is to use the DecorrelatedJitterBackoffV2 method (in the Polly package) and treat SQL Timeout (-2) errors and IOException and SocketException as transients that should be retried.

/// <summary>
/// A custom sql execution strategy for EntityFramework that utilizes the DecorrelatedJitterBackoffV2 method from Polly
/// to calculate the delay before next retry.
/// </summary>
public class CustomEfCoreSqlExecutionStrategy : SqlServerRetryingExecutionStrategy
{
    private readonly IEnumerable<TimeSpan> _backoffDelays;

    public CustomEfCoreSqlExecutionStrategy(
        ExecutionStrategyDependencies dependencies,
        int maxRetryCount,
        TimeSpan maxRetryDelay,
        ICollection<int> errorNumbersToAdd
    ) : base(dependencies, maxRetryCount, maxRetryDelay, errorNumbersToAdd)
    {
        _backoffDelays = Backoff.DecorrelatedJitterBackoffV2(TimeSpan.FromSeconds(5), maxRetryCount);
    }

    /// <summary>
    /// Get the delay before the next retry using Polly's DecorrelatedJitterV2 implementation
    /// </summary>
    /// <param name="lastException"></param>
    /// <returns></returns>
    protected override TimeSpan? GetNextDelay(Exception lastException)
    {
        var currentRetryCount = ExceptionsEncountered.Count - 1;
        if (currentRetryCount < MaxRetryCount)
            return _backoffDelays.ElementAt(currentRetryCount);

        return null;
    }

    /// <summary>
    /// Experience has shown that we frequently encounter SqlExceptions that have an unknown error number but have an
    /// IOException and/or SocketException as the inner exception. So we need to treat those as transient.
    ///
    /// Also, the default EF Core retry strategy explicitly excludes SQL Timeout errors (-2), but those errors are the most frequent
    /// that we see in the wild. 
    /// </summary>
    /// <param name="exception"></param>
    /// <returns></returns>
    protected override bool ShouldRetryOn(Exception exception)
    {
        // all i/o exceptions are considered transient
        // error code -2 (timeout) is also transient, even though the base implementation says otherwise
        var shouldRetry =
            exception is SqlException {InnerException: IOException or SocketException}
                or SqlException {Number: -2} || base.ShouldRetryOn(exception);

        return shouldRetry;
    }
}

@stevendarby We’ll probably add an Azure-specific execution strategy that the user needs to choose explicitly

@AndriySvyryd Thanks for the reply. Since my original post, I’ve implemented a custom ExecutionStrategy that explicitly closes the connection, waits 5 seconds and then re-opens it (via an override of OnRetry()) - seems to be working fine but if I don’t need to go to all that trouble, I’m definitely interested in changing my implementation.

In most cases a retriable exception puts the connection in a Broken state, so it will be closed and reopened

Can you point me to the code that sets the connection state to Broken when a retriable exception occurs? I dug through the EFCore repo and couldn’t find anything obvious. I’d like to confirm exactly what happens on an exception and modify my code accordingly.

@AndriySvyryd can I just check my reading of the changes around -2 is correct: it hasn’t been added to the default list of codes to retry, however if the user has added that as an additional code, it will understand that as throttling error and applies different delays?

That’s correct, -2 is still not retried by default.

The advice highlighted in this issue seems to be specifically about Azure SQL; is there an existing mechanism EF can use to detect when it’s connecting to Azure SQL and not on-premise SQL Server?

Not currently. It is recommended to use different Execution strategies depending on the target database.

This article says timeout exceptions can be due to connection or query issues and provides a way to differentiate. Perhaps EF needs a mechanism for determining if -2 should be retried.

Adding this would be out of scope for this issue. I’ve opened https://github.com/dotnet/efcore/issues/30023 to track it.