mc-magento: Deadlock with newOrder Observer

The newOrder observer randomly deadlocks on order placement when the bulk api product sync is also running. Thus, I’m assuming the bulk sync is performing a delete that does table level locking, OR READ locking on the mailchimp_ecommerce_sync_data table is used during the bulk sync.

The event used to trigger the newOrder Observer is poorly chosen as it injects Mailchimp code into core magento place order transaction, thus causing rollback and other issues on failure. From looking at the code, it really seems sales_order_invoice_pay or sales_order_save_after with some code checks would be more appropriate and a bit safer.

Preconditions

Magento 1.9.4.5 PHP 7.2 MySQL 5.7 Latest MailChimp extension

Confirmed all indexes are on the tables, so if this is an INDEX issue, it’s due to one that never existed, but at this point I’m not sure it is an INDEX issue.

Disabling Ecommerce Sync makes the issue go away, so its definitely the newOrder observer paired with the bulk sync cron causing this. Also, the */5 bulk sync cron seems a bit excessive, don’t you think?

Steps to reproduce

This is difficult as it happens to random checkouts, but the gist is as follows…

1. When a customer is checking out with an item that is also being synced (or deleted) by the bulk sync cronjob, deadlock occurs.
2. So basically start a bulk sync with a particular item, and checkout with that item simultaneously.

Expected result

1. Deadlocks does not occur, payment processes, and order is saved.
2. The event sales_order_place_after is a poor choice as it injects your code into the main transaction, so a failure will lead to gateway payments without orders in magento due to transaction rollback. At the very least, don’t use this event.

Actual result

1. Deadlock occurs
2. Place order transaction rolls back leaving a charge in payment provider with no order in Magento.
3. Observers depending on this order existing, now all throw errors due to its rollback.

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction in /var/www/html/my-store.com/lib/Zend/Db/Statement/Pdo.php:228
Stack trace:
.
.
.
#7 /var/www/html/my-store.com/app/code/core/Mage/Core/Model/Resource/Db/Abstract.php(433): Zend_Db_Adapter_Abstract->update('mailchimp_ecomm...', Array, '(id='254447')')
#8 /var/www/html/my-store.com/app/code/core/Mage/Core/Model/Abstract.php(318): Mage_Core_Model_Resource_Db_Abstract->save(Object(Ebizmarts_MailChimp_Model_Ecommercesyncdata))
#9 /var/www/html/my-store.com/app/code/community/Ebizmarts/MailChimp/Model/Ecommercesyncdata.php(65): Mage_Core_Model_Abstract->save()
#10 /var/www/html/my-store.com/app/code/community/Ebizmarts/MailChimp/Model/Api/ItemSynchronizer.php(106): Ebizmarts_MailChimp_Model_Ecommercesyncdata->saveEcommerceSyncData('18360', 'PRO', 'f99cbba60c37cfd...', NULL, NULL, 1, NULL, NULL, NULL, false, NULL, true)
#11 /var/www/html/my-store.com/app/code/community/Ebizmarts/MailChimp/Model/Api/ItemSynchronizer.php(180): Ebizmarts_MailChimp_Model_Api_ItemSynchronizer->_updateSyncData('18360', NULL, NULL, 1)
#12 /var/www/html/my-store.com/app/code/community/Ebizmarts/MailChimp/Model/Api/Products.php(523): Ebizmarts_MailChimp_Model_Api_ItemSynchronizer->markSyncDataAsModified('18360')
#13 /var/www/html/my-store.com/app/code/community/Ebizmarts/MailChimp/Model/Observer.php(462): Ebizmarts_MailChimp_Model_Api_Products->update('18040')
#14 /var/www/html/my-store.com/app/code/core/Mage/Core/Model/App.php(1374): Ebizmarts_MailChimp_Model_Observer->newOrder(Object(Varien_Event_Observer))
#15 /var/www/html/my-store.com/app/code/core/Mage/Core/Model/App.php(1353): Mage_Core_Model_App->_callObserverMethod(Object(Ebizmarts_MailChimp_Model_Observer), 'newOrder', Object(Varien_Event_Observer))
#16 /var/www/html/my-store.com/app/Mage.php(451): Mage_Core_Model_App->dispatchEvent('sales_order_pla...', Array)
#17 /var/www/html/my-store.com/app/code/core/Mage/Sales/Model/Order.php(1115): Mage::dispatchEvent('sales_order_pla...', Array)
#18 /var/www/html/my-store.com/app/code/core/Mage/Core/Model/Resource/Transaction.php(105): Mage_Sales_Model_Order->place()
.
.
.