Connection resiliency and retries
Cloud databases sometimes drop a connection for a moment during failovers or updates. This lesson shows EF Core's retrying execution strategy, which errors it retries, how it affects transactions, and the timeouts and pooling settings that keep apps stable.
- StartQuery sentDatabase is failing over
- Step 2Transient errorError 40613: database unavailable
- Step 3WaitDelay grows with each attempt
- Step 4RetryUp to the configured maximum
- EndSuccessThe user never sees the blip
Transient errors
Azure SQL and other cloud databases have short outages during failovers, scaling and maintenance. These transient errors usually clear in seconds, so the right response is to wait briefly and try again rather than show the user an error page.
EF Core's SQL Server provider knows which error numbers are transient (for example 40613, database unavailable, and 1205, deadlock victim).
Turning on retries
builder.Services.AddDbContextPool<ShopContext>(o => o.UseSqlServer(
builder.Configuration.GetConnectionString("Shop"),
sql =>
{
sql.EnableRetryOnFailure(
maxRetryCount: 5,
maxRetryDelay: TimeSpan.FromSeconds(10),
errorNumbersToAdd: null);
sql.CommandTimeout(30);
}));Retries and transactions
With retries on, EF Core cannot retry part of a manual transaction. Wrap the whole unit of work in the execution strategy so it is replayed from the start.
var strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
await using var tx = await db.Database.BeginTransactionAsync();
db.Orders.Add(order);
await db.SaveChangesAsync();
await db.Database.ExecuteSqlAsync($"EXEC dbo.ReserveStock {order.Id}");
await tx.CommitAsync();
});Other settings that help
- AddDbContextPool reuses DbContext instances and cuts allocation per request.
- Connection pooling in SqlClient is on by default; always dispose contexts (DI does it for you).
- Set a sensible CommandTimeout; the default is 30 seconds.
- Make operations idempotent where possible, because a retry may repeat a command whose result was lost.
- Log retries so frequent failures are noticed.
Interview question
Why must manual transactions be wrapped in an execution strategy when retries are enabled?
The retrying strategy can only safely retry a complete unit of work. If a transient error happens halfway through a user-started transaction, the earlier statements were rolled back with the connection, so retrying just the failed statement would be wrong. EF Core therefore throws unless the whole transaction runs inside strategy.ExecuteAsync, which replays it from the beginning.
Want the full details? Read the official Microsoft documentation for this topic.