Price re-index causes DB deadlocks during checkout

Question

I'm experiencing an issue where I believe the Product Price re-indexing process is causing a deadlock exception in the checkout process.

I caught this exception in the checkout process:

Order conversion exception: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

Unfortunately I don't have a full stack trace because of where the exception was caught, but checking the INNODB status I was able to track down the deadlock:

SELECT `si`.*, `p`.`type_id` FROM `cataloginventory_stock_item` AS `si` 
INNER JOIN `catalog_product_entity` AS `p` ON p.entity_id=si.product_id     
WHERE (stock_id=1) 
AND (product_id IN(47447, 56678)) FOR UPDATE

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 329624 n bits 352 index 
`PRIMARY` of table `xxxx`.`catalog_product_entity` 

The SQL requesting table lock is ultimately generated from Mage_CatalogInventory_Model_Stock::registerProductsSale() when it is trying to get the current inventory count in order to decrement it.

At the time the deadlock occurred, the Product Price re-index process was running and I'm assuming it had a read lock on the catalog_product_entity table which caused the deadlock. If I'm understanding the deadlock correctly any read lock will cause a deadlock, but the product price re-index holds the lock for a fair time as the site has ~50,000 products.

Unfortunately, by this point in the checkout code flow the customer's credit card had been charged (via a custom payment module), and the creation of the corresponding order object failed.

My questions are:

• Is the custom payment module logic faulty? i.e. Is there an accepted flow for ensuring that Magento can convert the quote to an order exception free before committing the charge to the payment method (credit card)?

Edit: It appears the payment module logic is indeed faulty as the call to $paymentmethod->authorize() should happen after the place where this deadlock occurs, not before (as per Ivan's answer below). However, the transaction will still be blocked by the deadlock (albeit without the errant charge to the credit card).

• This function call $stockInfo = $this->_getResource()->getProductsStock($this, array_keys($qtys), true); in Mage_CatalogInventory_Model_Stock::registerProductsSale() makes it a locking read, how dangerous would it be to make it a non-locking read?

• In searching the web for an answer a couple of places suggested not running a full re-indexing while the site is hot; hardly seems like a good solution; is the issue of indexing causing table deadlocks and lock contention a known issue in Magento, are there workarounds?

Edit: It seems the remaining question here is the one from the third question; re-indexing causing table deadlocks. Looking for workarounds for this.

Edit: The concept that deadlocks aren't in and of themselves issues, but rather the response to them should be the focus, makes a lot of sense. Investigating further to find a point in the code to catch the deadlock exception and reissue the request. Doing this at the Zend Framework DB adapter level is one approach, but am also looking for a way to do this in the Magento code to ease maintainability.

There's an interesting patch in this thread: http://www.magentocommerce.com/boards/viewthread/31666/P0/ that seems to solve a related deadlock condition (but not this one specifically).

Edit: Apparently deadlocking has been addressed to a degree in CE 1.8 Alpha. Still looking for a workaround until this version is out of Alpha

Answer 1

There is quite big probability that your payment method is processing payment wrongly.

Magento Order Save Process is quite simple:

• Prepares all data that should be transfered from quote item to order item, including prices and product information, afterwards it doesn't invoke price retrieval.
• Invoke before order submit events checkout_type_onepage_save_order and sales_model_service_quote_submit_before
  • Mage_CatalogInventory_Model_Stock::registerProductsSale() is invoked at this event observer
• Start DB transaction
• Invoke $order->place() method that processes the payment by calling $paymentMethod->authorize(), $paymentMethod->capture() or $paymentMethod->initialize() depends on its logic.
• Invoke $order->save() method that saves processed order to DB tables sales_flat_order_*.
• Commit DB transaction (At this step DB releases lock on inventory table)

So as you see, it couldn't be possible, that payment method charges money before the inventory lock and read of product prices or product info.

It is only possible in case if payment method is implemented in such a way, that it performs loading of products itself with prices, after the API call for charging operation is performed.

Hope this will help you in debugging your issue.

As for reindexing, it should be safe, if you don't have this issue with payment method. Since read operation that depend on locks are performed before money gets charged.

Answer 2

Because this is a custom extension, we can find a custom workaround (read: hack) to retrying the save without editing core files.

I have solved all of my deadlock woes with the following two methods added to a helper class. Instead of calling $product->save() I now call Mage::helper('mymodule')->saferSave($product):

/**
 * Save with a queued retry upon deadlock, set isolation level
 * @param  stdClass $obj object must have a pre-defined save() method
 * @return n/a      
 */
public function saferSave($obj)
{

    // Deadlock Workaround
    $adapter = Mage::getModel('core/resource')->getConnection('core_write');
    // Commit any existing transactions (use with caution!)
    if ($adapter->getTransactionLevel > 0) {
        $adapter->commit();
    }
    $adapter->query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');

    //begin a retry loop that will recycle should a deadlock pop up
    $tries = 0;
        do {
            $retry = false;
            try {
                $obj->save();
            } catch (Exception $e) {
                if ($tries < 10 and $e->getMessage()=='SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction') {
                    $retry = true;
                } else {
                    //we tried at least 10 times, go ahead and throw exception
                    throw new Zend_Db_Statement_Exception($e->getMessage());
                }
                sleep($this->getDelay());
                $tries++;
            }
        } while ($retry);

    //free resources
    unset($adapter);
    return;
}

public function getDelay($tries){
    return (int) pow(2, $tries);
}

This accomplishes two distinct things - it queues a retry when a deadlock is encountered, and it sets an exponentially increasing timeout for that retry. It also sets the transaction isolation level. There is a lot of information on SO and on DBA.SE for more information about MySQL's transaction isolation levels.

FWIW, I haven't encountered a deadlock since.

Answer 3

On the Magento forums they talk about editing a Zend library file: lib/Zend/Db/Statement/Pdo.php

The original _execute function:

public function _execute(array $params = null)
    {
        // begin changes
        $tries = 0;
        do {
            $retry = false;
            try {
                if ($params !== null) {
                    return $this->_stmt->execute($params);
                } else {
                    return $this->_stmt->execute();
                }
            } catch (PDOException $e) {
                #require_once 'Zend/Db/Statement/Exception.php';
                if ($tries < 10 and $e->getMessage()=='SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction') {
                    $retry = true;
                } else {
                    throw new Zend_Db_Statement_Exception($e->getMessage());
                }
                $tries++;
            }
        } while ($retry);
        // end changes
    }

After modification:

public function _execute(array $params = null)
    {
        $tries = 0;
        do {
            $retry = false;
            try {
                $this->clear_result();
                $result = $this->getConnection()->query($sql);
                $this->clear_result();
            }
            catch (Exception $e) {
                if ($tries < 10 and $e->getMessage()=='SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction') {
                    $retry = true;
                } else {
                    throw $e;
                }
                $tries++;
            }
        } while ($retry);

        return $result;
    }

As you can see the only thing that has been changed is that the $tries has been moved outside the loop.

As always it is suggested to try this out on a development/testing environment and not instantly deploy this fix on a production environment.

Answer 4

I have this same issue on a Magento 1.11 site and I have an open ticket with Magento on it since 11/12/2012. They confirmed it is an issue and are suppose to be creating a patch.

My question is why does price have to be reindexed at this time? I don't think this is needed:

#8 /var/www/html/app/code/core/Mage/CatalogInventory/Model/Observer.php(689): Mage_Catalog_Model_Resource_Product_Indexer_Price->reindexProductIds(Array)

Answer 5

We had a similar deadlock issue when certain calls were made during a re-index. For us it manifested itself mostly when a customer would be adding something to the cart. While probably not fixing the actual underlying issue, implementing asynchronous re-indexing has completely halted all deadlock calls we were previously seeing. Should work as a stop-gap until the underlying issue is fixed and pushed to EE/CE editions (we ended up purchasing an extension to do it).

Answer 6

I suggest you install Philwinkle DeadlockRetry. It worked for our database.

https://github.com/philwinkle/Philwinkle_DeadlockRetry

I would also suggest looking at your any outside programs hitting your web api. We had one that was updating the QTY for products and it was causing many deadlocks. We re-wrote that and went directly to the database.

Answer 7

I met deadlock issue last year many times I sorted it out simply by increasing memory for our server because indexing process eats all resources.

You should also us async reindex solution I used miravist

For a more stable system, you should think of separating your backend from frontend so they do not eat each other's RAM.

For my experience, it is not problem of source code.