… or at least, your website performance!
Recently I worked on two support cases from our customers as they see SQL Server errors, such as “System.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint “FK_ShipmentEx_Shipment”. The conflict occurred in database “dbCommerce”, table “dbo.Shipment”, column ‘ShipmentId’“, or “System.Data.SqlClient.SqlException (0x80131904): The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.”
These errors happened randomly, during the high load times – it seems to be affected by the concurrency level.
What was wrong? and why?
It took me a good amount of time, and good amount of hairs, too. The actual error is another one, and the one above is just the “by product”.
The cart system in Episerver Commerce suffers from a design flaw: it shares (almost) everything with the purchase orders. ShoppingCart is just another metaclass extended OrderGroup, so it’ll use the same OrderGroup, OrderForm, Shipment, LineItem and OrderAddress tables in the database, like PurchaseOrder and PaymentPlan. At first, it seems to be reasonable approach. But when you have hundreds, or thousands of customers visiting your website (and you would be happy to see that ;)) – problems start to appear.
Previously, you were told – at least by the sample code – that you should call
var cartHelper = new CartHelper();
to load a cart when a customer visits the site. This will load the existing cart, and will create a new one if it does not exist. And as the cart is unique per customer (of course, I don’t want you to see what’s in my cart), each customer visit will create a cart, even if they are visiting only (aka they won’t buy anything).
And things gets a bit worse as when the customer starts adding items to cart. And you are supposed to save the cart each, then run the “CartValidate” workflow, then save the cart again.
And the worst thing happens when the customer places an order (on the business perspective, it’s not that bad, it’s even a good thing!), and you are supposed to save the cart as a purchase order, delete the cart, add certain information to the purchase order, and save it again. And this is when the the issue happens. Because the cart is deleted, there will be at least two operations. This will of course perform badly if you have hundreds, or thousands of visitors at the same time, continuously adding items to carts and checking out. In such cases, deadlocks or timeouts will likely to happens, and because of the retry policies , the operations will be tried again, and this time it caused the errors as we see above.
So what you can do?
First and foremost, you can try to do a throughout review of your code – any calls to Cart.AcceptChanges() should be checked if they are really necessary. It’s important to note that a call to .AcceptChanges() on an OrderGroup will save everything inside, including OrderForm, Shipment, LineItem, etc. So one call to Cart.AcceptChanges should be enough, any subsequent calls on its children should not be necessary. In ideal scenarios, you should only need to call Cart.AcceptChanges once per request.
It’s important to note that you won’t have to call CartHelper.Cart.AcceptChanges() after calling CartHelper.AddEntry – it’s already done inside that. And it’s worth noting, CartHelper belongs to Mediachase.Commerce.Website, which is open sourced by Episerver, and you are free to download and modify CartHelper, if you want to.
One smart solution is to postpone the creation of cart until the customer actually adds something to it. You can refer to CartService.cs in Quicksilver 1.x for reference of how to cheat the APIs and have one less save.
If you are using Commerce 9+, it would make senses to upgrade to 9.24.1 – the last version of Commerce 9 (Commerce 10.0.0 is CMS-10 compatible and contains some small breaking changes). Commerce 9.24.1 contains various bug fixes and performance improvements. This is particularly important if your website is a B2B one where there might be 100-200, or even more items in cart. On one of the implementations, we got the report that the performance is 10 times better. Of course, if your carts are usually at small size (less than 5 items) the the performance gain might be insignificant.
The world might be not enough
What if you want the extreme performance and push your site to the limit, and all of the above are still not enough?
Then welcome you to the new cart system.
New cart system is a new feature in Commerce 10.2 (10.2 can be infamous for it breaking change, but other than that it’s pretty big release). Instead of sharing the tables with the orders in a formalized state, the new cart system store carts in a separated table, where you will have a big fat column to stored serialized data (can you guess? it’s JSON!). Free of all the sharing tables, the new cart system can yield a performance of 3-10x times faster compared to the old one.
Too good to be true?
It’s true. But it does not come free (even though it’s not expensive, neither). Here’s the list to consider before jumping right in the new cart system:
- You will have to upgrade to Commerce 10.2, which means CMS 10+. Commerce 10 was a small breaking change release and it’s likely you won’t have to change your code, but CMS 10 is a different beast. But if you are already up-to-date (and you should), this is less than a problem.
- You must be using the new abstraction APIs. At least the cart handling part must. And of course you should. If you still want to work with CartHelper even after using the new abstraction APIs, then no candies for you! If you still have code with CartHelper, then consider to move to the new abstraction APIs as soon as you get approval from your boss. And if you moved to the new abstraction APIs already (congratulations!), make sure to use IOrderRepository to work with carts (the old one, IOrderFactory, is obsoleted anyway).
- You will need to update your payment gateways/shipping methods to implement IPaymentPlugin (and/or ISplitPaymentPlugin) and IShippingPlugin, respectively.
- You will need to add a flag in ecf.app.config – to turn it on
<Features>
<add feature="WorkflowsVNext" state="Enabled" type="Mediachase.Commerce.Core.Features.WorkflowsVNext, Mediachase.Commerce" />
<add feature="SerializedCarts" state="Disabled" type="Mediachase.Commerce.Core.Features.SerializedCarts, Mediachase.Commerce" />
</Features>- And now the least fun part – migration. The carts in the old system will be migrated to the new one, and this might take hours to complete – depending on the number of carts you have.
And when you migrated all the carts – then let’s the fun begins. Now you can enjoy (much) better performance, (much) better code, and you won’t have to (almost) care about the save will kill your site performance. The errors above will very unlikely have a chance to bother you.
Time for beer(s) then!
