Magento – Price re-index causes DB deadlocks during checkout

checkoutdatabaseindexingordersquote

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

Best Answer

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.

Related Topic