MySQL Deadlock Situation in Magento

What is a Deadlock Situation?

A deadlock is a situation where two or more competing actions are each waiting for the other to finish, and thus neither ever does. (More on deadlocks:  http://en.wikipedia.org/wiki/Deadlock)

Is this article for you?

This KB article focuses on resolving the deadlock situation that occurs with Magento Community versions prior to 1.7.x and Enterprise Version 1.12.x running together with MageRewards. The solution provided below is included in Magento latest versions (Magento Community versions 1.7.x+ and Enterprise Version 1.12.x+) by default. The solution below addresses the deadlock that occurs between "sales_flat_quote" table and "sales_flat_quote_item".

A brief explanation of the problem

Mage_Sales_Model_Resource_Quote->markQuotesRecollectOnCatalogRules(). This will generate the query

UPDATE `sales_flat_quote` SET `trigger_recollect` = '1' WHERE (entity_id IN((SELECT DISTINCT `sales_flat_quote_item`.`quote_id` FROM `sales_flat_quote_item` WHERE (product_id IN((SELECT DISTINCT `catalogrule_product_price`.`product_id` FROM `catalogrule_product_price`))))))

According to our research, when a deadlock happens, MageRewards has yet to do any processing.  It turns out that when running queries that involve "IN", MySQL always makes the outermost table leading. So, the above query will have to scan each row in "sales_flat_quote" table and check it against "sales_flat_quote_item", which will then have to check each record against "catalogrule_product_price" table. 

Why all this matters? It seems that this is actually a bug in MySQL 5, where using a SELECT within a WHERE IN performs poorly ( http://bugs.mysql.com/bug.php?id=42259 ). 

The Workaround

To fix this temporarily (until you upgrade Magento to a latest version ), you should overwrite the Quote resource model class in app/code/local directory and change markQuotesRecollectOnCatalogRules() method to something like the following:

public function markQuotesRecollectOnCatalogRules() 
{ 
	$tableQuote = $this->getTable('sales/quote'); 
	$subSelect = $this->_getReadAdapter() 
		->select() 
		->from(array('t2' => $this->getTable('sales/quote_item')), array('entity_id' => 'quote_id')) 
		->from(array('t3' => $this->getTable('catalogrule/rule_product_price')), array()) 
		->where('t2.product_id = t3.product_id') 
		->group('quote_id');
	$select = $this->_getReadAdapter()->select()->join( 
		array('t2' => $subSelect), 
		't1.entity_id = t2.entity_id', 
		array('trigger_recollect' => new Zend_Db_Expr('1')) 
	);
	$updateQuery = $select->crossUpdateFromSelect(array('t1' => $tableQuote)); 
	$this->_getWriteAdapter()->query($updateQuery);
	return $this; 
}	

Still need help? Get Help Get Help