UseSQlServer->EnableRetryOnFailure with Atomic Operations | Unsupported execution strategy

[kostas-kapasakis]

SUMMARY

Hello ,

I am working in a jsonapi.net .net 8 api in which we use EFcore and SQL Server as our DB server.
We have already defined the application db context as below without the EnableRetryOnFailure configuration.

    `            .AddDbContext<ApplicationDbContext>((sp, options) =>
                 {
                     options.UseSqlServer(configuration.GetApplicationConnectionString(), o =>
                     {
                          o.UseCompatibilityLevel(120);
                     });
                 })
        
       `

We added atomic operations controller as described in the documentation and we are able to have the functionality we wanted.

Recently we encountered some transient errors during some api tests and thought to use the EnableRetryOnFailure configuration in order to remove some false negatives and as a consequence our services.AddDbContext was modified as below

    `            .AddDbContext<ApplicationDbContext>((sp, options) =>
                 {
                     options.UseSqlServer(configuration.GetApplicationConnectionString(), o =>
                     {
                          o.UseCompatibilityLevel(120);
                          o.EnableRetryOnFailure(
                            maxRetryCount: 5,
                            maxRetryDelay: TimeSpan.FromSeconds(30),
                            errorNumbersToAdd: null);
                          });
                 })
        
       `

Our API tests were stabilized and we did decrease the false negatives but our atomic operations are now fail with the following error.

"detail":"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.",
"source":{"pointer":"/atomic:operations[0]"}}]}

Is there a way to tweak the execution strategy used by the atomic operations ?

VERSIONS USED

• JsonApiDotNetCore version:5.6.0
• ASP.NET Core version: 8
• Entity Framework Core version: 8.0.1
• Database provider: SQL Server

[bkoelman]

Perhaps, I don't know, this hasn't been asked before. But it sounds like a simple question with a potentially unsolvable generic solution. You could try to change the transaction management in OperationsProcessor, but the consequences of doing so may be undesirable. See https://learn.microsoft.com/en-us/ef/core/miscellaneous/connection-resiliency#transaction-commit-failure-and-the-idempotency-issue for details. It requires JADNC and all existing user code to support running multiple times (resetting any cached intermediate state in fields, for example).

An easier solution would be to resend the API request on failure. Can you do that from tests? Or throttle, so it doesn't flood the database when tests run concurrently.

I looked into idempotency support for JSON:API a while ago. There's an open PR (#1132), but nobody seemed to care so I didn't pursue it further.

[kostas-kapasakis]

Thanks for the reply @bkoelman .

In that case I agree probably we will move the retry to the tests side .

Thanks again.