Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
xyz1123581321 opened this issue Nov 18, 2022 · 5 comments

Comments

@xyz1123581321
Copy link
Contributor

xyz1123581321 commented Nov 18, 2022

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
Copy link
Contributor Author

@damsfx any idea?

@damsfx
Copy link
Contributor

damsfx commented Nov 21, 2022

@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
Copy link
Contributor Author

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
Copy link
Member

@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:

protected function setOrderNumber()

@xyz1123581321
Copy link
Contributor Author

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants