TheCodeBuzz

Error: Configured execution strategy SqlServerRetryingExecutionStrategy does not support user initiated transactions

Issue Description

Entity framework operation gives below runtime error,

 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. 

This issue is more visible while performing EFCore operation in a shared environment like Cloud etc. and but might also exist in IIS server-based applications.

Resolution

A common cause of such issues are failures like transient errors or network or hardware issues often visible in-application communication, especially in the cloud environment is more sensitive to transient faults.

This error also indicates that if the application is configured with Default Execution strategies like RetryonFailure Pattern or Custom execution strategies are not sufficient to overcome the issue.

Database operation defined using Transactions for Example- defined with BeginTransaction() needs to be addressed differently i.e manually invoking the Execution strategy with a delegate. Such an execution strategy should encapsulate everything that needs to be executed.

Execution Pattern for Transaction

Define the Execution strategy using CreateExecutionStrategy() with a delegate encapsulating all DB operation that needs to be executed as mentioned below.

For the transaction below is the execution pattern,

 using (var db = new EmployeeContext())
            {
                var strategy = db.Database.CreateExecutionStrategy();

                strategy.Execute(() =>
                {
                    using (var context = new EmployeeContext())
                    {
                        //BeginTransaction
                        using (var transaction = context.Database.BeginTransaction())
                        {

                            //First Update
                            context.EmployeeDb.Add(new EmployeeDb { FirstName = "thecodebuzz", LastName = "buzz" });
                            context.SaveChanges();

                            //SecondUpdate
                            context.EmployeeDb.Add(new EmployeeDb { FirstName = "thecodebuzz1", LastName = "buzz2" });
                            context.SaveChanges();

                            //End Transaction 
                            transaction.Commit();
                        }
                    }
                });
            }

The above is in addition to the RetryEnableOnFailure method which you might already be having added to the Startup.cs or OnConfiguring method within the given DBContext.

Additionally, if needed Entity Framework provides an extension method ExecuteInTransaction() to perform state verification of any database operation which is discussed already in the article Connection Resiliency for Transaction in EFCore- Part II

For more details please visit below the post,

• Connection Resiliency for Transaction in EFCore- Part II

• Connection Resiliency Using EnableRetry – Part I

Did I miss anything else in these resolution steps?

That’s all! Happy coding!

Does this help you fix your issue?

Do you have any better solutions or suggestions? Please sound off your comments below.

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.