Magento 1.7 MySQL – Deadlock on CE Checkout

databasemagento-1.7MySQL

I'm facing a 'thousand years of pain' Magento deadlock issue on the last step of the checkout. Here's the exception:

exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction' in /var/www/app/code/community/Zend/Db/Statement/Pdo.php:258
Stack trace:
#0 /var/www/app/code/community/Zend/Db/Statement/Pdo.php(258): PDOStatement->execute(Array)
#1 /var/www/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /var/www/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `ma...', Array)
#5 /var/www/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `ma...', Array)
#6 /var/www/app/code/core/Mage/Sales/Model/Resource/Order/Abstract.php(177): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `ma...')
#7 /var/www/app/code/local/Mage/Sales/Model/Abstract.php(51): Mage_Sales_Model_Resource_Order_Abstract->updateGridRecords('300151')
#8 [internal function]: Mage_Sales_Model_Abstract->afterCommitCallback()
#9 /var/www/app/code/core/Mage/Core/Model/Resource/Abstract.php(110): call_user_func(Array)
#10 /var/www/app/code/core/Mage/Core/Model/Resource/Transaction.php(79): Mage_Core_Model_Resource_Abstract->commit()
#11 /var/www/app/code/core/Mage/Core/Model/Resource/Transaction.php(169): Mage_Core_Model_Resource_Transaction->_commitTransaction()
#12 /var/www/app/code/core/Mage/Sales/Model/Service/Quote.php(189): Mage_Core_Model_Resource_Transaction->save()
#13 /var/www/app/code/core/Mage/Sales/Model/Service/Quote.php(249): Mage_Sales_Model_Service_Quote->submitOrder()
#14 /var/www/app/code/core/Mage/Checkout/Model/Type/Onepage.php(774): Mage_Sales_Model_Service_Quote->submitAll()
#15 /var/www/app/code/core/Mage/Checkout/controllers/OnepageController.php(512): Mage_Checkout_Model_Type_Onepage->saveOrder()
#16 /var/www/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Checkout_OnepageController->saveOrderAction()
#17 /var/www/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('saveOrder')
#18 /var/www/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#19 /var/www/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#20 /var/www/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#21 /var/www/index.php(87): Mage::run('', 'store')

I have tried:

with no success.

Best Answer

Bear in mind that a query that hits a lock timeout is not the same as a deadlocked query, the two are different and as such, the diagnosis tools are different.

To review deadlocks

Just open a MySQL CLI prompt and enter,

show innodb status

It will print the SQL query for the last deadlock encountered.

If you are using Percona MySQL, you can also use their toolkit to log deadlocks.

To review lock wait timeouts

Use,

show process list

During the time that you are placing the order in the checkout, and you will be able to observe the current query holding the lock and trace the cause from there.

You could also enable the slow query log, set the timeout lower than the lock wait timeout, and you'll have a log of queries blocking the others.

Related Topic