Wednesday 24 September 2014

Magento Deadlock error on huge load.

Not sure how many of us faced DB deadlock during peak load time.

We've faced deadlock while updating times used of salesrule table during our Black Friday sale . Since we were loosing money at that time, for time being we've removed the salesrule update by overriding Mage_SalesRule_Model_Observer :: sales_order_afterPlace.

Then  we have re-enabled the same after sometime and once we did that, I've realized its happening daily. But It will happen only twice or thrice per thousand orders.

Because of this we were collecting money from the customer and the order was not placed.

After hitting my head in the wall for a long time :). I got a clue about the issue that its because of the order of promo got applied. Here is my theory and reproduce steps.

==============
Steps to Replicate
==============
Promo creation
---------------
i) Create Two "No coupon shopping cart rule" one for simple product and one for virtual product. ( To use below I am naming promos as "Virtual Promo" & "Simple Promo")
ii) You can specify individual skus of simple & virtual product.

Then lets take a scenario

=====================
Customer 1 ::
=====================
i) Adding virtual product to cart.
ii) Virtual Promo will be applied automatically.
iii) Then he is Adding a simple product
iv) Simple Promo will be applied automatically.
v) Up on order placement. Magento will update times_used of Virtual & Simple Promo respectively.

=====================
Customer 2 ::
=====================
i) Adding simple product to cart.
ii) Simple Promo will be applied automatically.
iii) Then he is Adding a virtual product
iv) Virtual Promo will be applied automatically.
v) Up on order placement. Magento will update times_used of Simple & Virtual Promo respectively.

You can see how rule ids have saved in DB from the below screen cast.

http://screencast.com/t/KeYImQgyoW
http://screencast.com/t/BSHabO0d

After order placement magento updating times used in the same order. And it will leads to deadlock on huge load. Because the query will run like the below ( Just an example )

Customer 1

Begin transaction;
Update salesrule set times_used = 3 where rule_id = "1";

Lets say in the mean time second customer's transaction also started

Begin transaction;
Update salesrule set times_used = 3 where rule_id = "2";

Now First customer's  second update ( Update salesrule set times_used = 3 where rule_id = "2"; ) will wait for second customer's update to complete and second customer's second update will wait for the first one to complete. So whichever commits first will go and the second one will be rolled back.

You can easily fix this deadlock just by ordering the update.

Override Mage_SalesRule_Model_Observer in you local module and copy sales_order_afterPlace function to your own observer file and add a sort / natsort here ( http://screencast.com/t/8mFuo2tq ).

 Here is the config.xml update you need to do to override an observer class.

          <salesrule>
                <rewrite>
                    <observer>Rosetta_Promotion_Model_Salesrule_Observer</observer>
                </rewrite>
            </salesrule>

I've also found a potential deadlock on http://amasty.com/multiple-coupons.html.  Please contact me if you are interesting.

GET THE DEADLOCK FIXED AND ENJOY SELLING