Deadlocks - Serialization failure: 1213 Deadlock found when trying to get lock;

[xyz1123581321]

What could cause deadlocks when inserting a bunch of orders concurrently? After 100 orders per minute deadlocks start appearing. I'm trying to trace it down, but I'm not sure, when this is comment out there are no problems. Possibly decreasing product stock might be a problem, but also creating new order products

$cart ->products ->each(function (CartProduct $entry) use ($order) { $entry->moveToOrder($order); });

Did someone experience this?

[xyz1123581321]

@damsfx any idea?

[damsfx]

@damsfx any idea?

100 orders per minute? Normal users orders or generated ones?

The CartProduct->moveToOrder() method make use of transaction inside the Oder->fromCart() method witch use them too. I think the lock on the offline_mall_order_products is not release yet when a new moveToOrder() appears.

Maybe you can try to see where the most recent deadlock happened : SHOW ENGINE INNODB STATUS \G and search for a section nmaed LATEST DETECTED DEADLOCK.
It can give you a more detailed reason why the error occurs.

@tobias-kuendig any thoughts?

[xyz1123581321]

Currently I'm testing with a jmeter script, but very soon with real users. I tried but I couldn't find anything yet except that when commenting out "moveToOrder" method works fine so I guess something in there is causing the deadlocks.

[tobias-kuendig]

@xyzqtc Does it work if you remove this transaction?

https://github.com/OFFLINE-GmbH/oc-mall-plugin/blob/develop/models/Order.php#L154

It could be because of nested transactions.

Another hairy bit is the order number generation:

https://github.com/OFFLINE-GmbH/oc-mall-plugin/blob/c993a885d860b13cae5b2a6670545405559cba30/models/Order.php#L263

[xyz1123581321]

We dumped that method because we were getting a lot of deadlocks in there. So what we did is https://github.com/OFFLINE-GmbH/oc-mall-plugin/blob/develop/models/Order.php#L205

$order->save();
$order = $order->fresh();

and we created a trigger:

CREATE TRIGGER offline_mall_order_number BEFORE INSERT ON offline_mall_orders
FOR EACH ROW 
  SET new.order_number = (IFNULL((SELECT MAX(order_number)+1 FROM offline_mall_orders), 1));

But there are still some deadlocks, I tried removing transaction from the Order and I was getting more deadlocks, I just tried with removing transaction on the CartProduct moveToOrder method and we started getting better results. But still..

p.s. we are sending big numbers of samples like 500/1s ramp up ... 500/60s ramp up