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.

Table of Contents

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