Price re-index causes DB deadlocks during checkout

41

10

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

Roscius

Posted 2013-01-27T06:51:19.013

Reputation: 706

1

Possible duplicate of What's the reason for running a price reindex after placing an order?

– Teja bhagavan Kollepara – 2016-09-27T10:00:43.730

@Roscius did you get this resolved? Dealing with this now myself. Hoping that either EE 1.13 or philwinkle's deadlock fix may do the trick. – kalenjordan – 2014-03-19T17:12:22.570

1@kalenjordan The indexing improvments in 1.13 and a re-try scheme like philwinkle's below have largely mitigated the issue for me. – Roscius – 2014-03-21T18:22:54.400

1@Roscius roughly how much have they mitigated it? I'm seeing DB failures of some sort (connection timeout, lock wait timeout, deadlock) affect about 0.2% of my orders. Very rare but I really want to get it fully resolved. – kalenjordan – 2014-03-21T18:34:00.897

@kalenjordan Have had no deadlocks since I made the modifications, but they're still theoretically possible. Making sure your authorize -> convertquote -> capture code flow is correct is also important. I'm dealing with potentially multiple payment gateways on the same order, so it was complicated when one of them fails (or the order deadlocks). – Roscius – 2014-03-22T20:46:30.747

We have been battling a similar problem recently, what payment extension are you using? – Peter O'Callaghan – 2013-01-28T13:03:45.737

Since this doesn't seem to be resolved: 1.8.0 Alpha mentions MySQL database deadlock issues were resolved. in the changelog. http://www.magentocommerce.com/knowledge-base/entry/ce-18-later-release-notes

– B00MER – 2013-04-25T07:56:57.433

It's a custom coded extension – Roscius – 2013-01-29T04:35:00.700

Answers

16

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.

Ivan Chepurnyi

Posted 2013-01-27T06:51:19.013

Reputation: 2 476

1Thanks, it looks like the custom payment module logic is off a little. It looks however still like an indexing process will block the checkout by causing an exception in registerProductsSale() (understanding that with the fixes to the custom payment module will remove the problem of having customer's card charged). – Roscius – 2013-01-28T00:23:19.233

8

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.

philwinkle

Posted 2013-01-27T06:51:19.013

Reputation: 32 220

I think you forgot to pass $tries to this function sleep($this-&gt;getDelay()); – Tahir Yasin – 2016-02-05T13:03:38.457

1@Mage::getModel('core/resource')@ should create a new connection. I don't understand how it can change the current transaction isolation level. – giftnuss – 2013-11-27T11:59:48.517

@giftnuss fair enough. Should be singleton for sure. Feel free to contribute this on my deadlock module over on github – philwinkle – 2013-11-30T05:50:38.847

@philwinkle thanks for this man. I'm trying to figure out whether an EE 1.13 upgrade will solve my woes or whether I should look into this as well. I know that 1.13 does indexing asynchronously which is great but if the same underlying queries are involved, I have a hard time understanding how async alone would prevent deadlocks from ever happening. – kalenjordan – 2014-03-19T17:18:50.047

1@kalenjordan it is a combination of async and the updated varien db changes in 1.8/1.13 that decreases the likelihood of deadlocks. – philwinkle – 2014-03-19T17:33:27.333

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.

Kenny

Posted 2013-01-27T06:51:19.013

Reputation: 1 303

2I worry about editing the underlying framework files, it seems that the retry should happen up at the Magento code level instead. – Roscius – 2013-01-28T00:33:51.007

We have tried the suggested fix, and it did indeed prevent this particular deadlock from causing issues. We were also receiving deadlocks on saves to the sales_flat_order_grid, with this fix in place, they instead throw integrity contraint violations, which is obviously not good. – Peter O'Callaghan – 2013-01-28T13:07:28.440

1

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)

Kimberely Thomas

Posted 2013-01-27T06:51:19.013

Reputation: 354

If a product goes out of stock and out of stock products are not supposed to show in the catalog, I believe they are hidden by merit of having no price index records which end up excluding them from the product collection when the pricing is joined onto it. – davidalger – 2013-01-29T02:13:58.840

This doesn't answer the question. It looks like you're trying to add additional information to the original question. Perhaps this information would be better as a comment on the original question. – Luke Mills – 2013-01-29T03:56:09.600

I'm with you, Kim. I've had the same ticket open since 11/2011. – philwinkle – 2013-01-30T22:55:29.027

0

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.

Chris Rosenau

Posted 2013-01-27T06:51:19.013

Reputation: 70

This repo is no longer supported, but luckily it recommends it's replacement https://github.com/AOEpeople/Aoe_DbRetry.

– Goose – 2018-02-27T16:25:30.290

0

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).

fr0x

Posted 2013-01-27T06:51:19.013

Reputation: 1

-1

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.

phanvugiap

Posted 2013-01-27T06:51:19.013

Reputation: 654