why execution strategy 'SqlServerRetryingExecutionStrategy' does not support user initiated transactions?

Question

execution strategy 'SqlServerRetryingExecutionStrategy' does not support user initiated transactions. Use the execution strategy returned

Hello the Full Error says like this:

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.

Here is the Code I use Execution Strategy Twice inside one Function: This is on the top

using(var posDbCtx = new PosDbContext())
                        {
                            var strategy = posDbCtx.Database.CreateExecutionStrategy();

                            strategy.Execute(
                            () =>
                            {
                                using (var transaction = posDbCtx.Database.BeginTransaction())
                                {
                                    _posDbContext.PrintJobs.Add(_printJobs);
                                    _posDbContext.SaveChanges();

                                    transaction.Commit();
                                }
                            });
                        }

this is after code:

using (var posDbCtx = new PosDbContext())
                    {
                        var strategy = posDbCtx.Database.CreateExecutionStrategy();

                        strategy.Execute(
                        () =>
                        {

                            using (var transaction = posDbCtx.Database.BeginTransaction())
                            {
                                int lineindex = 0;
                                foreach (var jline in _printjoblines)
                                {
                                    var pjobline = new PrintJobLine();
                                    pjobline.PrintLineId = Guid.NewGuid();
                                    pjobline.PrintJobId = jline.PrintJobId;
                                    pjobline.LineIndex = lineindex;
                                    pjobline.Text = jline.Text;
                                    pjobline.RightText = jline.RightText;
                                    pjobline.LeftText = jline.LeftText;
                                    pjobline.LineLength = jline.LineLength;
                                    pjobline.PrintOptions = jline.PrintOptions;
                                    pjobline.ItemName = jline.ItemName;
                                    pjobline.Quantity = jline.Quantity;
                                    pjobline.Flags = jline.Flags;
                                    pjobline.BergPlu = jline.BergPlu;
                                    pjobline.ActualPrice = jline.ActualPrice;
                                    pjobline.BasePrice = jline.BasePrice;
                                    pjobline.Vducolor = jline.Vducolor;
                                    pjobline.ParentIndex = jline.ParentIndex;
                                    pjobline.Peplu = jline.Peplu;
                                    pjobline.SaleItemId = jline.SaleItemId;
                                    pjobline.SaleTenderId = jline.SaleTenderId;
                                    pjobline.SignatureId = jline.SignatureId;
                                    //pjobline.Language = 0;
                                    _posDbContext.PrintJobLines.Add(pjobline);
                                    _posDbContext.SaveChanges();

                                    lineindex++;   
                                    
                                }

                                transaction.Commit();
                            }
                        });
                    }

my explanation upon doing this on my local im using SQL Server 2019 Latest Version 15.0.18386.0 and the database version on which i'd deployed it is SQL Server 2014 Version 12.0.6164.21

i think due to the version the reason why it gives this error is there another for the codes above be revised for the old version SQL Server 2014 to be able to run it without the error?

user_3302585 · Answer

The issue is your EF Core configuration, not the version of SQL Server you are using. When you add EnableRetryOnFailure in the UseSqlServer configuration method, explicit transactions need to be handled in a database scope.

This is because when an database modification call fails, EF Core does not know if there were side effects and if that call is safe to automatically retry.

When using an execution strategy to execute the code, the entire body is retried. That means that the explicit transaction would be rolled back through any error handling (or just the using going out of scope and the explicit transaction rolling back on dispose) and then the entire block retried.

It ensures that the database is consistent and explicit transactions are always respected. With implicit transactions, the failed database modification call is rolled back before the retry is made.

why execution strategy 'SqlServerRetryingExecutionStrategy' does not support user initiated transactions?

1 Answers