{"id":8535,"date":"2017-03-26T17:37:32","date_gmt":"2017-03-27T01:37:32","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/cesardelatorre\/?p=8535"},"modified":"2019-03-18T14:38:58","modified_gmt":"2019-03-18T21:38:58","slug":"using-resilient-entity-framework-core-sql-connections-and-transactions-retries-with-exponential-backoff","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/using-resilient-entity-framework-core-sql-connections-and-transactions-retries-with-exponential-backoff\/","title":{"rendered":"Using Resilient Entity Framework Core Sql Connections and Transactions: Retries with Exponential Backoff"},"content":{"rendered":"

\"image\"<\/a><\/p>\n

There are many possible approaches to implement retries logic with exponential backoff<\/a> also depending on the context\/protocol, etc. (Database connections, HTTP requests, etc.)<\/p>\n

Retries with exponential backoff is a technique that assumes failure by nature and attempts to retry the operation, with an exponentially increasing wait time, until a maximum retry count has been reached. This technique embraces the fact that intermittently, cloud resources may be unavailable more than a few seconds, for any reason out of your control.<\/p>\n

An important case is about SQL Azure Databases<\/strong> that may be moved to another server at any time for load balancing reasons, causing the database from being unavailable for a few seconds.<\/p>\n

Another possibility could be if you have SQL Server deployed as a container<\/strong> and for some reason (like when spinning up the containers with docker-compose up) the SQL Server container is still not ready for other microservices\/containers, like we do at the eShopOnContainers reference application<\/a> for the dev\/test environment.<\/p>\n

Resilient Entity Framework Core Sql Connections<\/h2>\n

In regards the Azure SQL DB case, Entity Framework Core already provides internal database connection resiliency and retry logic, but you need to enable your desired execution strategy per DbContext connection if you want to have resilient EF Core connections<\/a>.<\/p>\n

For instance, the following code at the EF Core connection level is enabling resilient SQL connections that will re-try if it gets any temporal failure like it is possible when using Azure SQL Database or SQL Server deployed as a container if it is not ready.<\/p>\n\n\n\n
\/\/Startup.cs from any ASP.NET Core Web API<\/p>\n

public class Startup<\/p>\n

{<\/p>\n

\/\/Other code\u2026<\/p>\n

\/\/…<\/p>\n

public IServiceProvider ConfigureServices(IServiceCollection services)<\/p>\n

{<\/p>\n

\/\/…<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 services.AddDbContext<<\/b>OrderingContext<\/b>>(options =><\/b><\/p>\n

{<\/p>\n

options.UseSqlServer<\/strong>(Configuration[“ConnectionString”],<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sqlServerOptionsAction: sqlOptions =><\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 { <\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sqlOptions.<\/b>EnableRetryOnFailure<\/b>(maxRetryCount: 5,<\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 maxRetryDelay: <\/b>TimeSpan<\/b>.FromSeconds(30), <\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 errorNumbersToAdd: <\/b>null<\/b>);<\/b><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }); <\/b><\/p>\n

});<\/p>\n

\/\/…<\/p>\n

}<\/p>\n

}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Execution strategies and explicit transactions using BeginTransaction()<\/h2>\n

When retries are enabled in EF Core connections, each operation you perform via EF Core becomes its own retriable operation, i.e. each query and each call to SaveChanges() will be retried as a unit if a transient failure occurs.<\/p>\n

However, if your code initiates a transaction using BeginTransaction()<\/i> you are defining your own group of operations that need to be treated as a unit, i.e. everything inside the transaction would need to be played back shall a failure occur. You will receive an exception like the following if you attempt to do this when using an execution strategy.<\/p>\n

System.InvalidOperationException: The configured execution strategy ‘SqlServerRetryingExecutionStrategy’ does not support user initiated transactions. Use the execution strategy returned by ‘DbContext.Database.CreateExecutionStrategy()’ to execute all the operations in the transaction as a retriable unit.<\/i><\/p>\n

The solution, is to manually invoke the execution strategy with a delegate representing everything that needs to be executed. If a transient failure occurs, the execution strategy will invoke the delegate again, as in the following code implemented in eShopOnContainers<\/i> when using two multiple DbContexts related to the CatalogContext<\/i> and the IntegrationEventLogContext<\/i> when updating a product and saving the ProductPriceChanged integration event that needs to use a different DbContext.<\/p>\n

 <\/p>\n\n\n\n
public async Task<IActionResult> UpdateProduct([FromBody]CatalogItem productToUpdate)<\/p>\n

{<\/p>\n

\/\/Other code\u2026<\/p>\n

\/\/Update current product<\/p>\n

catalogItem = productToUpdate;<\/p>\n

\/\/ Use of an EF Core resiliency strategy when using multiple DbContexts<\/p>\n

\/\/ within an explicit BeginTransaction():<\/p>\n

\/\/ See: https:\/\/docs.microsoft.com\/en-us\/ef\/core\/miscellaneous\/connection-resiliency<\/a><\/p>\n

var strategy =\u00a0 <\/strong>_catalogContext.Database.CreateExecutionStrategy();<\/strong><\/p>\n

\u00a0\u00a0\u00a0 await strategy.ExecuteAsync(async () =><\/strong><\/p>\n

{<\/p>\n

\/\/ Achieving atomicity between original Catalog database operation and the<\/p>\n

\/\/ IntegrationEventLog thanks to a local transaction<\/p>\n

using (var transaction = _catalogContext.Database.BeginTransaction())<\/strong><\/p>\n

{<\/p>\n

_catalogContext.CatalogItems.Update(catalogItem);<\/p>\n

await _catalogContext.SaveChangesAsync();<\/p>\n

\/\/Save to EventLog only if product price changed<\/p>\n

if (raiseProductPriceChangedEvent)<\/p>\n

await _integrationEventLogService.SaveEventAsync(priceChangedEvent);<\/p>\n

transaction.Commit();<\/strong><\/p>\n

}<\/p>\n

});<\/p>\n

\/\/Other code<\/p>\n

}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

The code above is actual code we\u2019re implementing in the eShopOnContainers reference application<\/a>, so you can see it in action across a microservice + Docker containers application here:<\/p>\n

https:\/\/github.com\/dotnet\/eShopOnContainers<\/a>\u00a0\u00a0\u00a0 (The code above, currently in the DEV branch, will be merged to the MASTER branch pretty soon).<\/p>\n

 <\/p>\n

There are other scenarios with Retries with Exponential Backoff related to protocols like HTTP. I\u2019ll blog about it in upcoming posts. \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"

There are many possible approaches to implement retries logic with exponential backoff also depending on the context\/protocol, etc. (Database connections, HTTP requests, etc.) Retries with exponential backoff is a technique that assumes failure by nature and attempts to retry the operation, with an exponentially increasing wait time, until a maximum retry count has been reached. […]<\/p>\n","protected":false},"author":362,"featured_media":12806,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-8535","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cesardelatorre"],"acf":[],"blog_post_summary":"

There are many possible approaches to implement retries logic with exponential backoff also depending on the context\/protocol, etc. (Database connections, HTTP requests, etc.) Retries with exponential backoff is a technique that assumes failure by nature and attempts to retry the operation, with an exponentially increasing wait time, until a maximum retry count has been reached. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/posts\/8535","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/users\/362"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/comments?post=8535"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/posts\/8535\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/media\/12806"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/media?parent=8535"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/categories?post=8535"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/cesardelatorre\/wp-json\/wp\/v2\/tags?post=8535"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}