v13.0.0: A Concurrency update occurred in table 'umbracoCommerceOrder' for primary key value(s)

umbracotrd commented 9 months ago

System

CMS: 13.0.3
Commerce: 13.0.0
Umbraco.Commerce.PaymentProviders.Quickpay: 13.0.0
SQL Server 2022

Step to reproduce

Make a succeed payment using Quickpay provider.
Observe that payment is captured on Quickpay dashboard and the webhook is called.
Expect

Order status in backoffice should be Capture

Actual
Order status in backoffice stucks at Authorize

There's DbConcurrencyException in the log:

[18:45:10 ERR] Error processing Umbraco Commerce callback for URL http://localhost:54506/umbraco/commerce/payment/callback/quickpay-v10-checkout/28bb7ffd-bd4c-4a56-946f-018cf32fe4ee/ORDER-01470-42285-QRW4X/12ebe54f2fa7c367be2fed0932fe4cfbf50e7871
System.Data.DBConcurrencyException: A Concurrency update occurred in table 'umbracoCommerceOrder' for primary key value(s) = '28bb7ffd-bd4c-4a56-946f-018cf32fe4ee' and version = '10'
at Umbraco.Commerce.Infrastructure.Resiliency.PollyExecutionStrategyBase.ExecuteAsync[TResult](Func`2 operation, Func`2 verifySucceeded, CancellationToken cancellationToken)
at Umbraco.Commerce.Core.UmbracoCommerceUnitOfWorkProvider.ExecuteAsync[T](IUnitOfWorkOptions options, Func`3 action, CancellationToken cancellationToken)
at Umbraco.Commerce.Cms.Web.Controllers.UmbracoCommercePaymentController.Callback(IPaymentProvider paymentProvider, PaymentProviderContext ctx)
at Umbraco.Commerce.Cms.Web.Controllers.UmbracoCommercePaymentController.HandleRequestWithOrder(String requestType, String paymentProviderAlias, Guid orderId, String orderNumber, String hash, Func`3 handler)
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
[18:45:10 ERR] An unhandled exception has occurred while executing the request.
System.Data.DBConcurrencyException: A Concurrency update occurred in table 'umbracoCommerceOrder' for primary key value(s) = '28bb7ffd-bd4c-4a56-946f-018cf32fe4ee' and version = '10'
at Umbraco.Commerce.Infrastructure.Resiliency.PollyExecutionStrategyBase.ExecuteAsync[TResult](Func`2 operation, Func`2 verifySucceeded, CancellationToken cancellationToken)
at Umbraco.Commerce.Core.UmbracoCommerceUnitOfWorkProvider.ExecuteAsync[T](IUnitOfWorkOptions options, Func`3 action, CancellationToken cancellationToken)
at Umbraco.Commerce.Cms.Web.Controllers.UmbracoCommercePaymentController.Callback(IPaymentProvider paymentProvider, PaymentProviderContext ctx)
at Umbraco.Commerce.Cms.Web.Controllers.UmbracoCommercePaymentController.HandleRequestWithOrder(String requestType, String paymentProviderAlias, Guid orderId, String orderNumber, String hash, Func`3 handler)
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
at Umbraco.Cms.Web.Common.Middleware.BasicAuthenticationMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at Umbraco.Cms.Web.BackOffice.Middleware.BackOfficeExternalLoginProviderErrorMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at StackExchange.Profiling.MiniProfilerMiddleware.Invoke(HttpContext context) in C:\projects\dotnet\src\MiniProfiler.AspNetCore\MiniProfilerMiddleware.cs:line 112
at Umbraco.Cms.Web.Common.Middleware.UmbracoRequestMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
at Umbraco.Cms.Web.Common.Middleware.UmbracoRequestMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at Umbraco.Cms.Web.Common.Middleware.PreviewAuthenticationMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at Umbraco.Cms.Web.Common.Middleware.UmbracoRequestLoggingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at SixLabors.ImageSharp.Web.Middleware.ImageSharpMiddleware.Invoke(HttpContext httpContext, Boolean retry)
at Umbraco.Commerce.Cms.Web.Mvc.UmbracoCommerceRequestBufferingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at Umbraco.Commerce.DemoStore.Web.Startup.<>c.<<Configure>b__4_0>d.MoveNext() in C:\personal\umbraco\DemoStore\Umbraco.Commerce.DemoStore_v13\src\Umbraco.Commerce.DemoStore.Web\Startup.cs:line 69
--- End of stack trace from previous location ---
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

swws23 commented 8 months ago

We have the same issue. Any updates?

System.Data.DBConcurrencyException: A Concurrency update occurred in table 'umbracoCommerceOrder' for primary key value(s) = '96d9f773-54e3-4878-8a75-018d40ead72e' and version = '9'
   at Umbraco.Commerce.Infrastructure.Resiliency.PollyExecutionStrategyBase.ExecuteAsync[TResult](Func`2 operation, Func`2 verifySucceeded, CancellationToken cancellationToken)
   at Umbraco.Commerce.Core.UmbracoCommerceUnitOfWorkProvider.ExecuteAsync[T](IUnitOfWorkOptions options, Func`3 action, CancellationToken cancellationToken)
   at Umbraco.Commerce.Cms.Web.Controllers.UmbracoCommercePaymentController.Callback(IPaymentProvider paymentProvider, PaymentProviderContext ctx)
   at Umbraco.Commerce.Cms.Web.Controllers.UmbracoCommercePaymentController.HandleRequestWithOrder(String requestType, String paymentProviderAlias, Guid orderId, String orderNumber, String hash, Func`3 handler)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Umbraco.Cms.Web.Common.Middleware.BasicAuthenticationMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Umbraco.Cms.Web.BackOffice.Middleware.BackOfficeExternalLoginProviderErrorMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Skybrud.Umbraco.Redirects.Middleware.RedirectsMiddleware.InvokeAsync(HttpContext context)
   at StackExchange.Profiling.MiniProfilerMiddleware.Invoke(HttpContext context) in C:\projects\dotnet\src\MiniProfiler.AspNetCore\MiniProfilerMiddleware.cs:line 112
   at Umbraco.Cms.Web.Common.Middleware.UmbracoRequestMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Umbraco.Cms.Web.Common.Middleware.UmbracoRequestMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Umbraco.Cms.Web.Common.Middleware.PreviewAuthenticationMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Umbraco.Cms.Web.Common.Middleware.UmbracoRequestLoggingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Ecreo.Essentials.Files.CssMiddleware.InvokeAsync(HttpContext context)
   at SixLabors.ImageSharp.Web.Middleware.ImageSharpMiddleware.Invoke(HttpContext httpContext, Boolean retry)
   at Umbraco.Forms.Web.HttpModules.ProtectFormUploadRequestsMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Umbraco.Commerce.Cms.Web.Mvc.UmbracoCommerceRequestBufferingMiddleware.InvokeAsync(HttpContext context, RequestDelegate next)
   at Microsoft.AspNetCore.Builder.UseMiddlewareExtensions.InterfaceMiddlewareBinder.<>c__DisplayClass2_0.<<CreateMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddlewareImpl.<Invoke>g__Awaited|10_0(ExceptionHandlerMiddlewareImpl middleware, HttpContext context, Task task)

umbracotrd commented 8 months ago

@swws23:

The cause

authorize and capture event are fired nearly at the same time and in erratic orders.

The fix

Add checks so that we only move payment status to Authorized if the current status is Initialized.
Return a bad request response when the webhook callback try to set payment status to Captured and the current payment status is not Authorized. This will tell Quickpay to retry that callback in about 1 minute later according to my tests.

What do you think about that solution? Does returning a bad request for capture webhook callback and 1 minute delay for the retry callback cause any side effect to you?

The PR for the hotfix is here: #5

umbracotrd commented 8 months ago

Fixed in 13.0.1 and 12.0.1. Those releases are pushed to nuget (it may take few hours for them to be searchable on nuget).

The exception is no longer thrown. The payment status should be updated from Authorized to Captured in the second webhook callback, which is about 1 minute after the order is placed.

umbraco / Umbraco.Commerce.PaymentProviders.Quickpay