Don't miss out Virtual Happy Hour today (April 26).

Try our conversational search powered by Generative AI!

Sql Deadlock on EPiServer.Commerce.Order.IOrderRepository SaveAsPurchaseOrder method. Version number EpiServer 11.20.17, EPiServer Commerce 13.33.0.0

Vote:
 

Version number EpiServer 11.20.17, EPiServer Commerce 13.33.0.0

Has anyone expereniece a SQL deadlock error with the IOrderRepository.SaveAsPurchaseOrder method? The stack track is below:

System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 330) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at EPiServer.Data.Providers.SqlTransientErrorsRetryPolicy.Execute[TResult](Func`1 method)
   at Mediachase.Data.Provider.SqlDataProvider.ExecuteNonExec(DataCommand command)
   at Mediachase.MetaDataPlus.Common.DBHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, Int32 commandTimeout, DataParameter[] commandParameters)
   at Mediachase.MetaDataPlus.Configurator.MetaDataPlusDatabase.StoredProcedure.ExecuteNonQuery(MetaDataContext context)
   at Mediachase.MetaDataPlus.Configurator.MetaDataPlusDatabase.UpdateMetaObject(MetaDataContext context, MetaClass metaClass, Int32 metaObjectId, String creatorId, DateTime created, String modifierId, DateTime modified, IEnumerable`1 metaParameters)
   at Mediachase.MetaDataPlus.MetaObject.AcceptChanges(MetaDataContext context)
   at Mediachase.Commerce.Storage.MetaStorageBase.AcceptChanges(MetaDataContext context, Boolean saveSystem)
   at Mediachase.Commerce.Orders.Payment.AcceptChanges()
   at Mediachase.Commerce.Storage.MetaStorageCollectionBase`1.AcceptChanges()
   at Mediachase.Commerce.Orders.OrderForm.AcceptChanges()
   at Mediachase.Commerce.Storage.MetaStorageCollectionBase`1.AcceptChanges()
   at Mediachase.Commerce.Orders.OrderGroup.AcceptChanges()
   at Mediachase.Commerce.Orders.PurchaseOrder.AcceptChanges()
   at EPiServer.Commerce.Order.Internal.PurchaseOrderProvider.Save(IPurchaseOrder purchaseOrder)
   at EPiServer.Commerce.Order.Internal.SerializableCartProvider.SaveAsPurchaseOrder(ICart cart)
   at EPiServer.Commerce.Order.Internal.DefaultOrderRepository.SaveAsPurchaseOrder(IOrderGroup cart)
   at [private namespace].Services.FinalizeOrderService.FinalizeOrder(CustomerContact customer) in [private directory]\Services\Order\FinalizeOrderService.cs:line 32
   at [private namespace].Controllers.CardConnectPaymentController.<OriginalProcessPayment>d__23.MoveNext() in [private directory]\Controllers\CardConnectPaymentController.cs:line 589

#299840
Edited, Apr 10, 2023 16:00
Vote:
 

Can you show us some code? mainly the calls in FInalizeOrderService and PaymentController.

Are these methods async?

#299881
Apr 10, 2023 23:00
Vote:
 

Hi @Surjit Bharath

Below is the code that calls the SaveAsPurchaseOrder method.

The code is not asyncronous 

public class FinalizeOrderService : IFinalizeOrderService
    {
        private readonly ICartService _cartService;
        private readonly IOrderRepository _orderRepository;
        private static readonly ILogger _logger = LogManager.GetLogger();

        public FinalizeOrderService(
            ICartService cartService,
            IOrderRepository orderRepository)
        {
            _cartService = cartService;
            _orderRepository = orderRepository;
        }

        public IPurchaseOrder FinalizeOrder(CustomerContact customer = null)
        {
            // Make sure to execute within transaction
            using (var scope = new Mediachase.Data.Provider.TransactionScope())
            {
                var cart = _cartService.LoadCart(_cartService.DefaultCartName);
                cart.CustomerId = EPiServer.Security.PrincipalInfo.CurrentPrincipal.GetContactId();

                var orderReference = _orderRepository.SaveAsPurchaseOrder(cart);
                IPurchaseOrder po = _orderRepository.Load<IPurchaseOrder>(orderReference.OrderGroupId);

                if (customer != null)
                {
                    customer.LastOrder = po.Created;
                    customer.SaveChanges();
                }

                // Remove old cart               
                _orderRepository.Delete(cart.OrderLink);

                // Commit changes
                scope.Complete();               

                return po;
            }
        }       
    }

#299883
Apr 10, 2023 23:33
Vote:
 

Investigating deadlocks is always a complicated mater. I'd suggest to contact developer support service. You might need to provide the XDL file capturing the deadlock. 

#299887
Edited, Apr 11, 2023 6:43
Vote:
 

Remove the transactionscope wrapper. Use a try/catch instead and move everything after the load purchase order out of the try catch.

#299890
Apr 11, 2023 7:36
Quan Mai - Apr 11, 2023 9:16
but why?
Surjit Bharath - Apr 11, 2023 9:22
OP is experiencing a deadlock. Remove some complexity, get it working then he/she can start working the original technical requirement back in.

Since he's implemented a transaction scope, and I sure there's transactions scope deeper down when calling 'saveaspurchaseorder' and SaveChanges() then nested transaction scopes are most likely his problem. Given the information we've given. As you said, complicated matter.
Quan Mai - Apr 11, 2023 9:39
the usage of transactionscope here is valid as it ensures that the order is created and the cart is deleted in one transaction, I would guess the code was taken or at least inspired by the official sample code - and that code was used in countless number of times so it should not be a problem. I don't think removing the outer transaction scope will help. as you can see the deadlock is caused when saving payment, so it might have something to do with that, even though it is not clear whether this is consistent or just randomly. we need the deadlock graph to understand first
Surjit Bharath - Apr 11, 2023 9:45
OP also didn't include the code CardConnectPaymentController. Since the stacktrace shows a MoveNext(), either this is working in an iteration or its async. He's stated its not async so FinalizeOrder method must be executing as part of an iteration, i.e. more than one. Maybe the problem lies here.
Vote:
 

@Surjit Bharath @Quan Mai

My apologies the cartcontroller code calling this is async. The line in red is were the call is being made to the FinalizeOrderService

private async Task<ActionResult> OriginalProcessPayment(CardConnectPaymentPage currentPage, PaymentForm paymentForm)
        {
            string message = string.Empty;
          

            var gRecaptchaResponse = Request.Form["g-recaptcha-response"];
            var result = await _captchaValidator.ValidateCaptcha(gRecaptchaResponse, "credit_card_submission");

            if (!result.Success) //failure
            {
                if (User.Identity.IsAuthenticated)
                {
                  
                    _logger.Error($"---- ACCOUNT: {_currentContact.Email} failed Recaptcha Score: {result.RiskAnalysis.Score} ----");
                    ModelState.AddModelError("paymentForm.RecaptchaResponse", SiteHelper.Translate(() => FormResources.RecaptchaError));
                }
                else
                {
                    

                    _logger.Error($"---- {HttpContext.GetClientIP()} GUEST: failed Recaptcha Score: {result.RiskAnalysis.Score} ----");
                    ModelState.AddModelError("paymentForm.RecaptchaResponse", SiteHelper.Translate(() => FormResources.RecaptchaError));
                }
            }

            if (TempData.ContainsKey("pickuptype"))
            {
                paymentForm.PickUpType = TempData["pickuptype"] as string;
                TempData.Keep("pickuptype");
            }
            else
            {
                return Redirect(SiteHelper.SiteSettings.CartPage.GetUrl());
            }

            if (TempData.ContainsKey("AlternateInfo"))
            {
                var pickupperson = TempData["AlternateInfo"] as AlternatePickupPerson;
                TempData.Keep("AlternateInfo");
                paymentForm.PickUpPersonFirstName = pickupperson.Name;
                paymentForm.PickupPersonLastName = string.Empty;
            }

            if (TempData.ContainsKey("SMS"))
            {
                paymentForm.SMSPhoneNumber = TempData["SMS"] as string;
                TempData.Keep("SMS");
            }

            var shipment = Cart.GetFirstForm().Shipments.First().ShippingAddress;
            var fullName = $"{shipment?.FirstName ?? ""} {shipment?.LastName ?? ""}";
            if (!string.IsNullOrEmpty(paymentForm.ExpDate) && !Regex.IsMatch(paymentForm.ExpDate, Shoup.Common.Constants.ExpDateRegEx))
            {
                ModelState.AddModelError("paymentForm.InvalidExpDateMessage", SiteHelper.Translate(() => FormResources.InvalidExpDateText));
            }
            if (ModelState.IsValid)
            {
                if (CartIsNullOrEmpty())
                {
                    return Redirect(Site.SiteSettings.CartPage.GetUrl());
                }

               
                var cartTotal = Cart.GetTotal();
                var amount = Cart.GetFirstForm().Payments.FirstOrDefault().Amount;
                if (Cart.GetFirstForm().Payments.FirstOrDefault().Amount != cartTotal)
                {
                    return Redirect(Site.SiteSettings.CartPage.GetUrl() + "?newCartItems=true");
                }

               

                var date = paymentForm.ExpDate.Split('/');

                if (Cart.Forms.Count > 0)
                {
                    var initialPayment = Cart.GetFirstForm().Payments.FirstOrDefault();
                    if (initialPayment != null)
                    {
                        CreatePayment(fullName, paymentForm, Cart);

                        var paymentProcessingResults = Cart.ProcessPayments();

                        if (!paymentProcessingResults.Any(r => !r.IsSuccessful))
                        {
                            var cardConnectReturnValues = paymentProcessingResults.First().Message.Split('|');

                            var thanksPage = Site.SiteSettings.ThanksPage.GetUrl();
                            _ccInfo.Last4 = paymentForm.CreditCardNumber.Substring(paymentForm.CreditCardNumber.Length - 4, 4);
                            _ccInfo.Expires = Convert.ToInt32((date[0].ToString() + date[1].ToString()));
                            _ccInfo.Type = GetCardType(paymentForm.CreditCardNumber);

                            
                            Cart.ClearCCDetails(_orderRepository);

                            var customerComment = (Cart.Notes.Any()) ? Cart.Notes.First().Detail : string.Empty;

                            
                            var po = _finalizeOrderService.FinalizeOrder(_currentContact);

                            
                            var orderID = po.OrderLink.OrderGroupId.ToString();
                            var shipmethod = po.GetFirstShipment().ShippingMethodName;

                            if (!string.IsNullOrEmpty(shipmethod))
                            {
                                shipmethod = shipmethod.ToLower();
                            }

                            var customerEmail = "";
                            if (TempData.ContainsKey("guestEmail"))
                            {
                                customerEmail = TempData["guestEmail"] as string;
                            }

                            if (User.Identity.IsAuthenticated)
                            {
                                var loggedInUser = _contactRepository.Get();
                                customerEmail = loggedInUser.Email;
                                if (shipmethod == "instore" || shipmethod == "curbside")
                                {
                                   
                                    fullName = $"{loggedInUser.FirstName} {loggedInUser.LastName}";
                                }
                            }

                            _queueService.InsertNewOrder(
                                orderID,
                                po.GetTotal().Amount,
                                (int)Common.Enumeration.OrderStatus.InQueue,
                                fullName,
                                po.GetCustomerPurchaseOrderNumber(),
                                0,
                                cardConnectReturnValues[1],
                                _ccInfo.Expires.ToString(),
                                _ccInfo.Last4,
                                cardConnectReturnValues.First(),
                                cardConnectReturnValues.Last(),
                                _ccInfo.Type,
                                customerComment,
                                shipmethod,
                                customerEmail,
                                po.OrderNumber,
                                paymentForm.PickUpPersonFirstName,
                                paymentForm.PickupPersonLastName,
                                paymentForm.SMSPhoneNumber);

                            try
                            {

                                var contact = new OrderContact
                                {
                                    CustomerCode = _currentContact?.Code ?? string.Empty,
                                    Email = _currentContact?.Email ?? shipment.Email,
                                    FirstName = _currentContact?.FirstName ?? shipment.FirstName,
                                    LastName = _currentContact?.LastName ?? shipment.LastName
                                };

                                if (_currentContact != null)
                                {
                                    _currentContact.SetValue(ContactFieldNames.HasOrders, true);
                                    _currentContact.SaveChanges();
                                }

                                var localizedPage = currentPage as ILocalizable;
                                var shippingOptions = _checkoutService.GetShippingMethodsWithRates(Cart, localizedPage.Language.ToString()).Where(x => x != null).ToList();

                                var pickupType = shipmethod?.ToLower();
                                string unfriendlyName = "";
                                //use friendly name if available
                                if (shippingOptions.Any(x => String.Equals(x.ShippingMethodName, shipmethod, StringComparison.CurrentCultureIgnoreCase)))
                                {
                                    pickupType = shippingOptions
                                        .Find(x => String.Equals(x.ShippingMethodName, shipmethod, StringComparison.CurrentCultureIgnoreCase))
                                        .FriendlyName;
                                    unfriendlyName = shippingOptions
                                        .Find(x => String.Equals(x.ShippingMethodName, shipmethod, StringComparison.CurrentCultureIgnoreCase))
                                        .ShippingMethodName;
                                }

                                await Task.Run(() =>
                                {
                                    _emailService.SendNewOrderNotificationEmailToShoup(contact,
                                    po.OrderNumber,
                                    orderID,
                                    po.GetTotal().Amount,
                                    unfriendlyName,
                                    paymentForm.PickUpPersonFirstName,
                                    paymentForm.PickupPersonLastName,
                                    paymentForm.SMSPhoneNumber,
                                    pickupType
                                    );
                                    SendEmailReceipt(po, unfriendlyName);
                                }).ConfigureAwait(false);
                            }
                            catch (Exception ex)
                            {
                                _logger.Error("CardConnectPaymentController => SendNewOrderNotificationEmail " + ex.ToString());
                            }

                            thanksPage = UriUtil.AddQueryString(thanksPage, "success", "true");
                            thanksPage = UriUtil.AddQueryString(thanksPage, "ordernumber", orderID);
                            thanksPage = UriUtil.AddQueryString(thanksPage, "customerId", po.CustomerId.ToString());
                            thanksPage = UriUtil.AddQueryString(thanksPage, "POnumber", po.OrderNumber);
                            thanksPage = UriUtil.AddQueryString(thanksPage, "CartId", Cart.GetCartId().ToString());

                            ModelState.Clear();
                            await CreateHubspotInfo(shipment);

                            Session["pickupType"] = "";
                            return Redirect(thanksPage);
                        }
                        else
                        {
                            
                            Cart.ClearCCDetails(_orderRepository);
                            message = paymentProcessingResults.First().Message;
                        }
                    }
                    else
                    {
                        message = "X:NullPayment";
                    }
                }
            }
            else
            {
                message = "ModelError";
            }

            TempData["PaymentError"] = message;
            Session["pickupType"] = "";

            return RedirectToAction("Index");
        }




#299921
Apr 11, 2023 13:43
Surjit Bharath - Apr 11, 2023 23:48
You're calling FinalizeOrder inside your async method. Which means its running synchronously, so I don't think it's related to the deadlock issue.

Is OriginalProcessPayment method just being called once? Is that wrapped in a Task.Run or has an await against it?

I can see your email service you calling is written to be like a fire and forget scenario...you're not using the same technique with the OriginalProcessPayment?

I would go back to my original suggestion of temporarily removing the TransacationScope. See if there's a change in behaviour and then experiment.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.