Avoiding database deadlocks in Magento 1.7

README.md

After having many deadlocks due to a high order volumne I've applied the these fixes to the core. Some can be found in a Magento forum. Before the fixes we could only process 1 order every 5-10 secs. Updating to Magento 1.8 is currently not an option but in 1-2 months.

1st Deadlock

Mage_Sales_Model_Abstract::_afterSave must be removed and replaced with afterCommitCallback. This is important to move the grid update (of sales_flat_order_grid) out of the transaction.

2nd Deadlock

Rewrite the method of the Mage_CatalogInventory_Model_Observer::reindexQuoteInventory() to remove the price reindexing from the transaction. That index process will also be fired in event sales_model_service_quote_submit_success.

In our case there are no price changing rules so simply commented out the reindexProductIds() method and reindex the prices during the night. To be more reliable you can write the product ids into a queue table and process them later.

3rd Deadlock

After applying these changes we can now process min 5 orders per second. There is still a deadlock poping up in subtractQuoteInventory() but that one comes only really seldom. So no furhter investigations for the moment.

Test ran with that JMeter setup:

zAbstract.php

<?php
/**
 * Magento
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to [email protected] so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade Magento to newer
 * versions in the future. If you wish to customize Magento for your
 * needs please refer to http://www.magentocommerce.com for more information.
 *
 * @category    Mage
 * @package     Mage_Sales
 * @copyright   Copyright (c) 2012 Magento Inc. (http://www.magentocommerce.com)
 * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 */

/**
 * Sales abstract model
 * Provide date processing functionality
 *
 * @author      Magento Core Team <[email protected]>
 */
abstract class Mage_Sales_Model_Abstract extends Mage_Core_Model_Abstract
{
    /**
     * Get object store identifier
     *
     * @return int | string | Mage_Core_Model_Store
     */
    abstract public function getStore();

    /**
     * Changed from afterSave to afterCommitCallback
     *
     * Callback function which called after transaction commit in resource model
     *
     * @return Mage_Core_Model_Abstract
     */
    public function afterCommitCallback()
    {
        if (!$this->getForceUpdateGridRecords()) {
            $this->_getResource()->updateGridRecords($this->getId());
        }
        return parent::afterCommitCallback();
    }

    /**
     * Get object created at date affected current active store timezone
     *
     * @return Zend_Date
     */
    public function getCreatedAtDate()
    {
        return Mage::app()->getLocale()->date(
            Varien_Date::toTimestamp($this->getCreatedAt()),
            null,
            null,
            true
        );
    }

    /**
     * Get object created at date affected with object store timezone
     *
     * @return Zend_Date
     */
    public function getCreatedAtStoreDate()
    {
        return Mage::app()->getLocale()->storeDate(
            $this->getStore(),
            Varien_Date::toTimestamp($this->getCreatedAt()),
            true
        );
    }
}

zObserver.php

<?php

class Zookal_CatalogInventory_Model_Observer extends Mage_CatalogInventory_Model_Observer
{

    /**
     * Refresh stock index for specific stock items after succesful order placement
     * @fire sales_model_service_quote_submit_success
     * @fire checkout_submit_all_after
     *
     * @param Varien_Event_Observer $observer
     *
     * @return $this
     */
    public function reindexQuoteInventory($observer)
    {
        // Reindex quote ids
        $quote      = $observer->getEvent()->getQuote();
        $productIds = array();
        foreach ($quote->getAllItems() as $item) {
            $productIds[$item->getProductId()] = $item->getProductId();
            $children                          = $item->getChildrenItems();
            if ($children) {
                foreach ($children as $childItem) {
                    $productIds[$childItem->getProductId()] = $childItem->getProductId();
                }
            }
        }

        if (count($productIds) > 0) {
            // @todo MAYBE write $productIds into a table queue and reindex them later
            Mage::getResourceSingleton('cataloginventory/indexer_stock')->reindexProducts($productIds);
        }

        $productIds = array();
        foreach ($this->_itemsForReindex as $item) {
            $item->save();
            $productIds[] = $item->getProductId();
        }

        /**
         * avoid deadlocks during checkout when multiple checkouts take place per second
         * In our case there are no prices changes.
         */
        // Mage::getResourceSingleton('catalog/product_indexer_price')->reindexProductIds($productIds);
        // @todo write the product entity ids into a table queue to reindex them later

        $this->_itemsForReindex = array(); // Clear list of remembered items - we don't need it anymore

        return $this;
    }
}

Replacing Mage_Sales_Model_Abstract::_afterSave with afterCommitCallback will take the inventory reindexing out of the transaction which may lead to a bug in the inventory counts. Did you apply this patch to magento 1.7?