Entity framework transient failure and command timeout

Question

I have some sporadic transient errors when connecting from my docker container to the hosting machine database.

For this reason, I configure my DB context to retry on failure. This solved the problem partially because the client application still fails since the retry of the request can take up to 2 minutes (on average 30 seconds). This is too long and represents a bad user experience. I was trying to understand why it takes so long but the only thing I can think of is that the timeout until declaring the connection as failure is too long. I thought of making the command timeout smaller. (By default it is 30 seconds if I am not wrong) Maybe put it 2-3 seconds. (most of my queries take less than 30ms) but I don't know if this would create other problems.

When checking my logs I discovered that the problem doesn't rely on the retry logic because it retries straight after the failure but what takes so long is the failure response.

This is my current configuration.

builder.Services.AddDbContext<AuthDbContext>(options =>
{
    options.UseNpgsql(EnvironmentVariables.GetEnvironmentVariable(EnvironmentVariables.DB_AUTH), conf =>
    {
        conf.EnableRetryOnFailure(5, TimeSpan.FromSeconds(5), new List<string> { "4060" });
        conf.CommandTimeout(2); //This is the command timeout that I want to add.
    });
    options.LogTo(
        filter: (eventId, level) => eventId.Id == CoreEventId.ExecutionStrategyRetrying,
        logger: (eventData) =>
        {
            var retryEventData = eventData as ExecutionStrategyEventData;
            var exceptions = retryEventData.ExceptionsEncountered;
            Log.Information("TRANSIENT ERROR Retry #{attemptNumber} with delay {delayMs} due to error: {errorMessage}", exceptions.Count, retryEventData.Delay, exceptions.Last().Message);
        });
});

Answer 1

There are a few points that you may consider

1. I am not sure which of 2 timeouts you experienced What is the difference between SqlCommand.CommandTimeout and SqlConnection.ConnectionTimeout?
2. Setting connection timeout to 3 seconds will make errors more often, I suggest do not make it less than 15 sec.
3. I recently learned that EF Core doesn’t consider timeouts as transient errors, and EnableRetryOnFailure doesn’t retry in case of timeout. You can used custom strategy if you want to retry timeout, eg. as https://github.com/dotnet/efcore/issues/27826#issuecomment-1177641624