3

We have created a Magento cron that is being run through cron.php. This cron updates certain orders to the 'canceled' order status:

    public function abandonedOrderCleanup() {
        $statusArray('custom_status');
        $collection = Mage::getResourceModel('sales/order_collection')->addFieldToFilter('status', array('in', $statusArray));
        Mage::getSingleton('core/resource_iterator')->walk($collection->getSelect(), array(array($this, 'cancelOrder')));
    }

    public function cancelOrder($orderData) {
      $order = Mage::getModel('sales/order')->setData($orderData['row']);
      if (!$order->getReadyToCancel()) {
              continue;
            }
            $order->cancel();
            $order->save();
    }

While it is running deadlocks are occasionally reported in exception.log that come from customers trying to place orders using the one page checkout:

2014-03-07T15:07:50+00:00 ERR (3):
exception 'PDOException' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction' in /var/www/magento1702/lib/Zend/Db/Statemen\
t/Pdo.php:228
Stack trace:
#0 /var/www/magento1702/lib/Zend/Db/Statement/Pdo.php(228): PDOStatement->execute(Array)
#1 /var/www/magento1702/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#2 /var/www/magento1702/lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#3 /var/www/magento1702/lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#4 /var/www/magento1702/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT INTO `sa...', Array)
#5 /var/www/magento1702/lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('INSERT INTO `sa...', Array)
#6 /var/www/magento1702/app/code/core/Mage/Sales/Model/Resource/Order/Abstract.php(177): Varien_Db_Adapter_Pdo_Mysql->query('INSERT INTO `sa...')
#7 /var/www/magento1702/app/code/core/Mage/Sales/Model/Abstract.php(51): Mage_Sales_Model_Resource_Order_Abstract->updateGridRecords('196465')
#8 /var/www/magento1702/app/code/core/Mage/Sales/Model/Order.php(2155): Mage_Sales_Model_Abstract->_afterSave()
#9 /var/www/magento1702/app/code/core/Mage/Core/Model/Abstract.php(319): Mage_Sales_Model_Order->_afterSave()
#10 /var/www/magento1702/app/code/core/Mage/Core/Model/Resource/Transaction.php(151): Mage_Core_Model_Abstract->save()
#11 /var/www/magento1702/app/code/core/Mage/Sales/Model/Service/Quote.php(189): Mage_Core_Model_Resource_Transaction->save()
#12 /var/www/magento1702/app/code/core/Mage/Sales/Model/Service/Quote.php(249): Mage_Sales_Model_Service_Quote->submitOrder()
#13 /var/www/magento1702/app/code/core/Mage/Checkout/Model/Type/Onepage.php(774): Mage_Sales_Model_Service_Quote->submitAll()
#14 /var/www/magento1702/app/code/core/Mage/Checkout/controllers/OnepageController.php(511): Mage_Checkout_Model_Type_Onepage->saveOrder()
#15 /var/www/magento1702/app/code/core/Mage/Core/Controller/Varien/Action.php(419): Mage_Checkout_OnepageController->saveOrderAction()
#16 /var/www/magento1702/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('saveOrder')
#17 /var/www/magento1702/app/code/core/Mage/Core/Controller/Varien/Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#18 /var/www/magento1702/app/code/core/Mage/Core/Model/App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
#19 /var/www/magento1702/app/Mage.php(683): Mage_Core_Model_App->run(Array)
#20 /var/www/magento1702/index.php(87): Mage::run('eur', 'website')
#21 {main}

We have tried to find a way to reliably replicate the issue so we can better address it. Even by adding 100k orders to a stage system and manually running the cron we have only once been able to experience the deadlock when manually placing payments through the checkout. In case it is of any consequence, this is how we have been generating the test orders:

for($i = 1; $i setProductId(1)->setQtyOrdered(3);
    $payment = Mage::getModel('sales/order_payment')->setMethod('payment_method_code');
    $order = Mage::getModel('sales/order')->setState('processing')->setStatus('custom_status')->addItem($item);
    $order->setPayment($payment);
    $order->save();
}

We need to run this cron on stores running CE 1.7.0.0 - 1.8.1.0.

What can we do to stop the deadlock from occurring?

Edit: A second question. How can we reliably replicate this deadlock?

I have created a second script that makes repeated POSTs using cURL to checkout/onepage/saveOrder to add c. 3 orders a second. I run this script while the cron is running (to update 100k orders). I thought this would cause a deadlock but so far it hasn't. (We're trying to replicate the problem consistently so we can properly test any solution.)

Improve this question
1
  • I would just try to raise the timeout. Because every order is canceled by themself, this should solve the problem in a okish way. Beside this, run the cron when there is no traffic on the shop as always :-)
    – Fabian Blechschmidt
    Mar 10, 2014 at 9:39

3 Answers 3

Reset to default
3

Adding a sleep(3) to allow the db to settle in between cancel transactions could be a good workaround.

Improve this answer
3
  • Forcing a 3 second timeout to every customer avoids the real problem at hand, and imposes the penalty on those trying to place orders. I also am skeptical as to whether this would actually work.
    – philwinkle
    Mar 10, 2014 at 15:46
  • This is a cron, so sleep() isn't that bad. And gives room for the shop to do its work... And workaround != real solution :-)
    – Fabian Blechschmidt
    Mar 10, 2014 at 16:26
  • I wasn't the downvoter... I don't disagree with you; I read it as the sleep being forced on all orders?
    – philwinkle
    Mar 10, 2014 at 16:30
2

Disclaimer: I'm not a DBA.

Aside from asking why this is written in an overcomplicated way (maybe I'm missing the point?) you could target only the orders that you know that need cancellation.

Rather than getting the select statement for the collection, iterating over it, loading each order in succession (a multitude of calls to the db, I might add) you could do the following:

$orders = Mage::getModel('sales/order')
    ->getCollection()
    ->addFieldToFilter('status','custom_status')
    ->addFieldToFilter('ready_to_cancel',true);

So now we have $orders which, if I follow your logic correctly, has only orders that are ready to cancel. Iterate over it with a simple foreach, marking the orders as ready to cancel:

foreach($orders as $order){
    $order->cancel()->save();
}

For what it's worth this is how Magento themselves do it in the admin with the mass action cancellation.

If you still have issues you can call explicit locking with a transaction, then commit the transaction all at once:

$transaction = Mage::getModel('core/resource_transaction');
foreach($orders as $order){
    $order->cancel();
    $transaction->addObject($order);
}
$transaction->save();

This should acquire the lock and force a lock wait instead of multiple items contending for a lock. TBH I've never tried this as a means of avoiding serialized insert/updates to the db, so this would be an interesting route to take. Be warned: any exception raised by any object save would roll back all saves in this transaction. Maybe that's preferable, however.

Other thoughts

Another question might be what version of MySQL you're on, if your sales_flat_order table is InnoDB still (I've seen these tables switched to MyISAM!!) and, if you're running 1.7 or so it is known to be deadlock prone; an upgrade to 1.8.1 may be preferable.

Some talk has been made on the Magento forums of a hotfix patch that will patch a 1.7 to the 1.8 deadlock-proof methods - but I haven't seen that patch. Maybe sniff around on the internet for it.

On the other hand:

Avoiding deadlocks when they occur can be handled by a module which can sense when a deadlock occurs and retries the previous transaction again. This has been floating around on the Magento Forums since 2010. I packaged these changes into a module, but it's more improved which includes an exponential backoff strategy, which spaces out the retries.

Check it out:

https://github.com/philwinkle/Philwinkle_DeadlockRetry

/shameless plug

Improve this answer
1
  • The original version of the cron did just do $orders = Mage::getModel('sales/order')->getCollection();foreach($orders as $order) { } as you suggest but we had issues with the collection exceeding PHP's memory limits (this was loading an unrealistically high number of tests orders though - we added 100k to stress test the cron and replicate the deadlock). The use of Mage_Core_Model_Resource_Transaction sounds interesting. I'll check that (and your other suggestions) out. Do you know how we could replicate the deadlock reliably (see my edit) so we can properly test a fix? Thanks.
    – Charli
    Mar 10, 2014 at 16:54
1

You need to post the output of "SHOW ENGINE INNODB STATUS;" to really get a clear picture of what is happening.

Inserts (customer placing new order) need "gap" locks. Updates only take gap locks if they are not able to effectively use an index when taking the lock. So in your cron job code instead of:

SELECT entity_id FROM sales_flat_order WHERE status = ? FOR UPDATE

You should instead do something like:

SELECT entity_id FROM sales_flat_order WHERE status = ?
SELECT entity_id FROM sales_flat_order WHERE entity_id IN (?) FOR UPDATE

That may be oversimplified, but the gist is to make sure your updates aren't requesting gap locks by ensuring that the query that actually takes the lock uses the primary key index rather than some other index or no index.

Another possibility is a lock contention on the eav_entity_store table when the credit memo increment id is generated. Makes sure an index exists on the entity_type_id column and the store_id column.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.