How to do retries in EF Core

EF Core has built-in retry functionality. To use it, you can call options.EnableRetryOnFailure(), like this:

optionsBuilder.UseSqlServer(ConnectionString, options => { options.EnableRetryOnFailure( maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: new List<int> { 4060 }); //additional error codes to treat as transient });
Code language: C# (cs)

The retry logic is contained in execution strategy classes. The above code is using the default execution strategy class (SqlServerRetryingExecutionStrategy).

When you execute a query, it goes through the execution strategy class. It executes the query and checks for transient errors. If there’s a transient error, it’ll delay for a little bit and then retry the query. It does this in a loop and will only retry a set number of times before giving up and throwing an exception.

In this article, I’ll go into more details about how the default execution strategy works, how to log the retry attempts, and how to customize the execution strategy to execute your own logic between retries.

Log the retry attempts

You may want to log the retry attempts to better understand what’s going on. The simplest way to do this is by calling optionsBuilder.LogTo() and providing a filtering function and logging function, like this:

optionsBuilder.UseSqlServer(ConnectionString, options => { options.EnableRetryOnFailure( maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: new List<int> { 4060 }); }); optionsBuilder.LogTo( filter: (eventId, level) => eventId.Id == CoreEventId.ExecutionStrategyRetrying, logger: (eventData) => { var retryEventData = eventData as ExecutionStrategyEventData; var exceptions = retryEventData.ExceptionsEncountered; Console.WriteLine($"Retry #{exceptions.Count} with delay {retryEventData.Delay} due to error: {exceptions.Last().Message}"); });
Code language: C# (cs)

Note: You could also call optionsBuilder.LogTo(Console.WriteLine), but it’s way too verbose when all you want to see is the retry attempts.

This outputs the following retry information:

Retry #1 with delay 00:00:00 due to error: Cannot open database "FakeDBName" requested by the login. The login failed. Login failed for user 'makolyte'. Retry #2 with delay 00:00:01.0398489 due to error: Cannot open database "FakeDBName" requested by the login. The login failed. Login failed for user 'makolyte'. Retry #3 with delay 00:00:03.2980159 due to error: Cannot open database "FakeDBName" requested by the login. The login failed. Login failed for user 'makolyte'. Unhandled exception. Microsoft.EntityFrameworkCore.Storage.RetryLimitExceededException: The maximum number of retries (3) was exceeded while executing database operations with 'SqlServerRetryingExecutionStrategy'
Code language: plaintext (plaintext)

Default execution strategy

To make the execution strategy use all of the default settings, you can call EnableRetryOnFailure() with no parameters, like this:

optionsBuilder.UseSqlServer(GetConnectionString(), options => { options.EnableRetryOnFailure(); });
Code language: C# (cs)

Here is a brief summary of four important default behaviors and settings:

Setting / BehaviorDefault
Max retries6
Max delay in seconds30
Delay calculation methodExponential backoff with jitter
Transient error codesThere are 23 error codes it considers to be transient. See the Default transient SQL error codes section below.

Note: These are the defaults at the time of this writing. They will probably change in the future. The code is open source, so when in doubt, you should check the source code.

Now I’ll go into more details about these two default behaviors:

  • The default retry delay calculation.
  • The default list of transient SQL error codes.

If you need to know more details about an aspect not covered here, you may be able to dig deeper by looking in the EF Core source code.

Delay calculation

The default execution strategy uses an exponential backoff with jitter. This means the delay will get longer with more retry attempts. The purpose of adding a random number (jitter) is that if you have multiple requests going on, it’ll spread out their retry attempts instead of clustering them (which is important because the bigger the clusters, the higher the chances of running into a transient error).

Here’s the code:

protected virtual TimeSpan? GetNextDelay([NotNull] Exception lastException) { var currentRetryCount = ExceptionsEncountered.Count - 1; if (currentRetryCount < MaxRetryCount) { var delta = (Math.Pow(DefaultExponentialBase, currentRetryCount) - 1.0) * (1.0 + Random.NextDouble() * (DefaultRandomFactor - 1.0)); var delay = Math.Min( _defaultCoefficient.TotalMilliseconds * delta, MaxRetryDelay.TotalMilliseconds); return TimeSpan.FromMilliseconds(delay); } return null; }
Code language: C# (cs)

Source: EF Core ExecutionStrategy GetNextDelay().

Default transient SQL error codes

The following is the list of SQL error codes that the default execution strategy considers to be transient errors. Check this list to determine if you need to supply additional error codes.

Error codeDescription
49920Cannot process request. Too many operations in progress.
49919Cannot process create or update request. Too many create or update operations in progress
49918Cannot process request. Not enough resources to process request.
41839Transaction exceeded the maximum number of commit dependencies.
41325The current transaction failed to commit due to a serializable validation failure.
41305The current transaction failed to commit due to a repeatable read validation failure.
41302The current transaction attempted to update a record that has been updated since the transaction started.
41301Dependency failure: a dependency was taken on another transaction that later failed to commit.
40613Database XXXX on server YYYY is not currently available.
40501The service is currently busy. Retry the request after 10 seconds.
40197The service has encountered an error processing your request.
10936Request limit has been reached.
10929Server is too busy.
10928Resource limit has been reached.
10060A network-related or instance-specific error occurred while establishing a connection to SQL Server.
10054A transport-level error has occurred when sending the request to the server.
10053A transport-level error has occurred when receiving results from the server.
1205Deadlock.
233The client was unable to establish a connection because of an error during connection initialization process before login.
121The semaphore timeout period has expired.
64A connection was successfully established with the server, but then an error occurred during the login process.
20The instance of SQL Server you attempted to connect to does not support encryption.
-2Timeout expired.

Source: EF Core SqlServerTransientExceptionDetector list of SQL error codes.

How to execute your own logic between retries

The execution strategy code was designed to be highly customizable. Besides specifying the retry settings, you can customize the retry behavior by subclassing the execution strategy class, overriding virtual methods, and passing it into the options.ExecutionStrategy() factory method.

In this section, I’ll show a step-by-step example of customizing the execution strategy logic by overriding the OnRetry() method. This method is called between retry attempts. I’ll have it log retry details to the console.

Note: You can subclass ExecutionStrategy (the base abstract class) if you want, but I’d suggest using SqlServerRetryingExecutionStrategy as a starting point instead, especially if you’re using SQL Server.

Step 1 – Subclass SqlServerRetryingExecutionStrategy

First, subclass SqlServerRetryingExecutionStrategy, and then override the OnRetry() method with whatever custom logic you want.

When you subclass this, you have to provide several constructors.

using Microsoft.EntityFrameworkCore.Storage; public class SqlServerRetryWithLogging : SqlServerRetryingExecutionStrategy { private int retryCount = 0; protected override void OnRetry() { retryCount++; Console.WriteLine($"Retry #{retryCount} due to error: {ExceptionsEncountered.Last().Message}"); base.OnRetry(); } #region Required constructors public SqlServerRetryWithLogging(DbContext context) : base(context) { } public SqlServerRetryWithLogging(ExecutionStrategyDependencies dependencies) : base(dependencies) { } public SqlServerRetryWithLogging(DbContext context, int maxRetryCount) : base(context, maxRetryCount) { } public SqlServerRetryWithLogging(ExecutionStrategyDependencies dependencies, int maxRetryCount) : base(dependencies, maxRetryCount) { } public SqlServerRetryWithLogging(DbContext context, int maxRetryCount, TimeSpan maxRetryDelay, ICollection<int> errorNumbersToAdd) : base(context, maxRetryCount, maxRetryDelay, errorNumbersToAdd) { } public SqlServerRetryWithLogging(ExecutionStrategyDependencies dependencies, int maxRetryCount, TimeSpan maxRetryDelay, ICollection<int> errorNumbersToAdd) : base(dependencies, maxRetryCount, maxRetryDelay, errorNumbersToAdd) { } #endregion }
Code language: C# (cs)

The custom logic in OnRetry() is simply writing retry info to the console. In a more realistic scenario, you may to react to specific error codes so that you can try to make the retry attempt succeed.

Step 2 – Pass in the custom execution strategy class to options.ExecutionStrategy()

options.ExecutionStrategy() is a factory method. You need to pass in a lambda that returns your custom execution strategy class.

In this example, I’m using a custom context class called StreamingServiceContext, so I’m wiring up the execution strategy in the OnConfiguring() method.

public class StreamingServiceContext : DbContext { private readonly string ConnectionString; public StreamingServiceContext(string connectionString) { ConnectionString = connectionString; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(ConnectionString, options => { options.ExecutionStrategy((dependencies) => { return new SqlServerRetryWithLogging(dependencies, maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(5), errorNumbersToAdd: new List<int> { 4060 }); }); }); } //rest of class }
Code language: C# (cs)

Note: When you’re passing in your own execution strategy class, the settings passed to options.EnableRetryOnFailure() will be ignored. Pass them into your custom execution strategy class instead (like the code above is doing).

Step 3 – Cause an error to see the retries in action

The simplest way to cause an error is to supply the wrong database name in the connection string. This will result in a 4060 error code.

For example, the real database name is StreamingService. To cause this error, I set it to StreamingServiceTEST in the connection string:

{ "ConnectionStrings": { "Default": "Server=DB_SERVER;Database=StreamingServiceTEST;Integrated Security=true" } }
Code language: JSON / JSON with Comments (json)

Step 4 – Run the code to see the retries in action

Run the code. For example, this is inserting a record into the movies table using the StreamingServiceContext:

using (var context = new StreamingServiceContext(connectionString)) { context.Movies.Add(new Movie() { Id = 20, Name = "Godzilla", Description = "Nuclear lizard fights monsters", Director = "Gareth Edwards", YearOfRelease = 2014, BoxOfficeRevenue = 529_000_000.00m }); context.SaveChanges(); }
Code language: C# (cs)

Here’s what it outputs when it runs:

Retry #1 due to error: Cannot open database "StreamingServiceTEST" requested by the login. The login failed. Login failed for user 'makolyte'. Retry #2 due to error: Cannot open database "StreamingServiceTEST" requested by the login. The login failed. Login failed for user 'makolyte'. Retry #3 due to error: Cannot open database "StreamingServiceTEST" requested by the login. The login failed. Login failed for user 'makolyte'. Unhandled exception. Microsoft.EntityFrameworkCore.Storage.RetryLimitExceededException: The maximum number of retries (3) was exceeded while executing database operations with 'SqlServerRetryWithLogging'.
Code language: plaintext (plaintext)

It retried 3 times as expected, and then threw an exception since it exceeded the retry limit.

Leave a Comment