5

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.

David Manners
  • 27,241
  • 9
  • 76
  • 220
kalenjordan
  • 3,545
  • 4
  • 28
  • 59
  • 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 01: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 – brentwpeterson Jun 03 '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: http://magento.stackexchange.com/a/263/4351 –  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? – kalenjordan Jun 23 '14 at 14:38
  • @kalenjordan, you found a definitive solution? – Denis Spalenza Sep 11 '14 at 10:42
  • @DenisSpalenza just not running that reporting any more. – kalenjordan Sep 15 '14 at 22:19

2 Answers2

1

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.

user3668514
  • 84
  • 3
  • 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. – kalenjordan Jun 23 '14 at 14:39
1

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.

kalenjordan
  • 3,545
  • 4
  • 28
  • 59