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: 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