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 methods:

  • OnRetry(): This is called between retry attempts. I’ll have it log the retry details to the console.
  • Execute(): Calls such as SaveChanges() are executed within this Execute() method (that’s how it controls the retry attempts). I’ll have it wrap the call to base.Execute() in a try/catch and log how many attempts it took to succeed.

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 (and they all call the base constructor).

using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;

public class SqlServerRetryWithLogging : SqlServerRetryingExecutionStrategy
{
	protected override void OnRetry()
	{
		Console.WriteLine($"Retry #{ExceptionsEncountered.Count} due to error: {ExceptionsEncountered.Last().Message}");
		base.OnRetry();
	}

	public override TResult Execute<TState, TResult>(
			TState state,
			Func<DbContext, TState, TResult> operation,
			Func<DbContext, TState, ExecutionResult<TResult>>? verifySucceeded)
	{
		try
		{
			var result = base.Execute(state, operation, verifySucceeded);

			if (ExceptionsEncountered.Any())
			{
				Console.WriteLine($"Succeeded on attempt {ExceptionsEncountered.Count + 1}");
			}
			else
			{
				Console.WriteLine("Succeeded with no errors");
			}
			return result;
		}
		catch
		{
			throw;
		}
	}

	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)
	{
	}
}
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. The logic in Execute() is simply wrapping the call to base.Execute() in a try/catch so it can log how many retry attempts it took to 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, 927 });
			});
		});
	}
	//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 a temporary error that can be recovered from quickly is the following:

  • Set the database offline. This results in error 927.
  • Run the EF Core code and wait for it retry the operation.
  • Set the database online.
  • The next attempt will succeed.

Note: I suggest using SSMS to set the database offline and online.

This approach allows you to see it do retries and then recover from the error successfully.

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 does a few retries and is able to recover from the error:

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'.
Succeeded on attempt 3Code language: plaintext (plaintext)

It retried twice and succeeded on the third overall attempt. If it exceeds the retry limits, it will log the retry attempts and a RetryLimitExceededException:

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)

6 thoughts on “How to do retries in EF Core”

  1. Hi,
    Very good article. Thank you very much.

    What I am missing here is:
    Inject ILogger, who uses Console.WriteLine?
    EF Core version and NET version. As correctly pointed a lot can be changed.
    Full working sample 🙂

    Ps. Is it possible to get retry information after context.SaveChanges(); ? Like saved on 3rd attempt.

    Best regards

    Reply
    • Hi Leszek,

      You’re welcome, I’m glad to hear you found this helpful.

      To answer your last question, to do something like logging “saved on 3rd attempt”:

      1. Subclass SqlServerRetryingExecutionStrategy
      2. Override Execute()
      3. Call base.Execute() in a try/catch. If it doesn’t throw an error, check the number of attempts and log it.

      I liked this idea, so I updated the article to show the code for how to do this. See this section: Execute your own logic between retries

      Note: The reason you can’t really do that in optionsBuilder.LogTo() is because the “SavedChangesCompleted” event data does not keep track of the attempts or anything like that. The only thing that keeps track of the attempts is ExecutionStrategy.ExceptionsEncountered. This property gets cleared when base.Execute() is called, so you have to check it after you call base.Execute(). It’s not available anywhere else.

      Reply
  2. Hi,

    I have this code in my .cs file and I want to try “Log the retry attempts”, may I know how to implement services.AddDbContext(options =>
    options.UseSqlServer(GetConnectionString(services),
    sqlServerOptionsAction: sqlOptions =>
    {
    sqlOptions.EnableRetryOnFailure(maxRetryCount: 3,
    maxRetryDelay: TimeSpan.FromSeconds(10),
    errorNumbersToAdd: new List { 4060 });
    }),

    ServiceLifetime.Transient);

    Reply
    • Hi Romil,

      1. Do options.UseSqlServer().LogTo();
      2. In LogTo(), configure it to log the CoreEventId.ExecutionStrategyRetrying event.
      3. Use the (serviceProvider, options) overload AddDbContext() so you can get the logger.

      Here’s an example using your code (note: the comment system stripped out some parts in your code, so I’m using name -MyDbContext- here).

      builder.Services.AddDbContext<MyDbContext>(((serviceProvider, options) =>
      options.UseSqlServer(GetConnectionString(),
          sqlServerOptionsAction: sqlOptions =>
          {
           sqlOptions.EnableRetryOnFailure(maxRetryCount: 3,
           maxRetryDelay: TimeSpan.FromSeconds(10),
           errorNumbersToAdd: new List<int> { 4060 });
          }).LogTo(filter: (eventId, level) => eventId.Id == CoreEventId.ExecutionStrategyRetrying,
          logger: (eventData) =>
          {
           var logger = serviceProvider.GetService<ILogger<MyDbContext>>();

           var retryEventData = eventData as ExecutionStrategyEventData;
           var exceptions = retryEventData.ExceptionsEncountered;

           logger.LogInformation($"Retry #{exceptions.Count} with delay {retryEventData.Delay} due to error: {exceptions.Last().Message}");

          })), ServiceLifetime.Transient);

      Alternatively: DI the ILogger into your context class and configure everything in OnConfiguring.

      Reply

Leave a Comment