Got a lock wait timeout originating in Mage_Sales_Model_Order::_afterSave(): https://github.com/LokeyCoding/magento-mirror/blob/magento-1.8/app/code/core/Mage/Sales/Model/Order.php#L2152

Looks like it's due to a reporting query that was running for a very long time and keeping the lock for a long time:

INSERT INTO `sales_order_aggregated_updated` (`period`, `store_id`, `order_status`, `orders_count`, `total_qty_ordered`, `total_qty_invoiced`, `total_income_amount`, `total_revenue_amount`, `total_profit_amount`, `total_invoiced_amount`, `total_canceled_amount`, `total_paid_amount`, `total_refunded_amount`, `total_tax_amount`, `total_tax_amount_actual`, `total_shipping_amount`, `total_shipping_amount_actual`, `total_discount_amount`, `total_discount_amount_actual`, `exchanges`, `gifts`)

Haven't dug into it much yet, was thinking I should post up a question before diving in. My inclination is to just disable the report aggregation crons entirely and rely on simple order queries (a la Aheadworks Advanced Reports) for reporting.

  • 2
    Analytics, large data storing and search, Magento just are not geared for. The reporting is not very useful from an owners perspective. If necessary (like your instance) I'd have no trouble pulling the reporting modules and just rely on a 3rd party analytics/reporting mechanism. FYI: Aheadworks stats is a local (magentos db) data store as well.
    – B00MER
    Mar 22 '14 at 1:34
  • 2
    It seems like Phil has a solution for this Kalen. We had this issue with another client and Phils module solved the problem Jun 3 '14 at 22:22
  • 1
    In theory, this shouldn't happen post 1.6, so I'm very interested in both queries that should be deadlocking here. Also see if this applies: magento.stackexchange.com/a/263/4351
    – user4351
    Jun 19 '14 at 20:18
  • Thanks Melvyn - how should this be prevented post-1.6? If you happen to have a long running insert query, what way is there around the lock wait timeout? Jun 23 '14 at 14:38
  • @kalenjordan, you found a definitive solution? Sep 11 '14 at 10:42

Magento doesn't properly encapsulate transactions in MySQL, leading to errors such as these. Try setting the session transaction isolation level in MySQL to SERIALIZABLE.

  • 2
    Hey thanks for the feedback. It would be interesting to know a little bit more context behind your answer. What would serializable accomplish and how would that resolve the issue? I've worked around the issue for now by avoiding core reports, so it's not an immediate priority for me to dig into this further myself. Jun 23 '14 at 14:39

What I ended up doing was just disabling these reporting crons. Using something like Aheadworks Advanced Reports for reporting or Custom Reports is a lot more manageable in my case.

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.