angrybrad
commented
6 years ago Is this MySQL or PostgreSQL? Any chance you could send a database dump over to support@craftmcs.com and point us to some entries we could save to try and reproduce?
Closed timkelty closed 5 years ago
angrybrad
commented
6 years ago Is this MySQL or PostgreSQL? Any chance you could send a database dump over to support@craftmcs.com and point us to some entries we could save to try and reproduce?
timkelty
commented
6 years ago It's MySQL (on AWS RDS).
Yep, I'll send it over.
timkelty
commented
6 years ago @angrybrad sent a DB dump and step to reproduce!
larsboldt
commented
6 years ago Any updates on this? We are also experiencing deadlock issues related to "INSERT INTO searchindex".
timkelty
commented
6 years ago @larsboldt nothing yet, I pinged @angrybrad a couple days ago and he said it was till on his plate, though.
ccchapman
commented
5 years ago I receive a similar issue on some requests when rebuilding the search index.
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The SQL being executed was: INSERT INTO `craft_searchindex` (`keywords`, `elementId`, `attribute`, `fieldId`, `siteId`) VALUES (' alternative energy ', 10991, 'slug', 0, 1) ON DUPLICATE KEY UPDATE `keywords`=' alternative energy '@ccchapman a few months back I was working with @angrybrad and he was able to reproduce it with my environment.
After you it happens once on a particular entry, if you save that same entry again, does it still occur? I think in our case, once the error occurred it wouldn't again for that same entry.
@angrybrad gave me some Mysql settings to play with to see if I could recreate, which I haven't tried yet. I'll try and do that next week.
timkelty
commented
5 years ago @ccchapman I haven't had any success, but I can confirm that for me at least, this only seems to happen once per entry.
After the exception is thrown, if I resave the same entry, all is well.
ccchapman
commented
5 years ago @timkelty For me it occurred when rebuilding the search index. Happened 50 times during a clear (through the ajax requests.) Not sure if expected.
jsunsawyer
commented
5 years ago I'm also experiencing this issue semi-regularly in a multi-site setup.
angrybrad
commented
5 years ago Have a potential fix for this here if someone who is able to reproduce would like to test.
You can change your composer.json to this to pull it in, then composer update:
"require": {
"craftcms/cms": "dev-develop#79334a98a648681155c385e3adb8568127d32bde as 3.1.21",
"...": "..."
}@angrybrad I was able to consistently reproduce the issue by rebuilding the search index and attempting to re-save an entry.
Updating and testing the change seems to resolve the issue for me, though my error had not been a result of specifically rebuilding search indexes.
timkelty
commented
5 years ago @jsunsawyer, et. al –
Brad's fix remedied this for me! Haven't seen a deadlock error where I could consistently reproduce before. 🤞
angrybrad
commented
5 years ago Glad to hear!
my2ter
commented
5 years ago @brandonkelly / @angrybrad
Could we get the change in the 3.1 branch, please? Not willing to update to a beta / major version to get this fixed just yet.
Cheers
brandonkelly
commented
5 years ago @my2ter 79334a98a648681155c385e3adb8568127d32bde made it into Craft 3.1.22
my2ter
commented
5 years ago @brandonkelly Sorry my bad, I must have missed it.
Unfortunately, that means this bug is not fixed... I am testing 3.1.31 with commerce 2.1.2 and running 50VUs to load test my app and create orders end up with dead locks.
Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The SQL being executed was: INSERT INTO `craft_searchindex` (`elementId`, `attribute`, `fieldId`, `siteId`, `keywords`) VALUES (80638, 'slug', 0, 1, '')I've narrowed it down to https://github.com/craftcms/cms/blob/3.1.31/src/services/Search.php#L113 after disabling most of my fields to be indexed in the search.
Commenting out line https://github.com/craftcms/cms/blob/3.1.31/src/services/Search.php#L113 and https://github.com/craftcms/cms/blob/3.1.31/src/services/Search.php#L116 will completely remove the dead lock issue.
Reason I'm mentioning commerce is because it would throw the deadlock for fields you cannot edit in the CMS and we had to implement an event hook to change the searched attributes:
Event::on(
Element::class,
Element::EVENT_REGISTER_SEARCHABLE_ATTRIBUTES,
function (RegisterElementSearchableAttributesEvent $event) {
if (in_array('sku', $event->attributes) || in_array('billingFirstName', $event->attributes)) {
$event->attributes = [];
}
});and running a load test for a commerce app is more than common :)
@angrybrad FYI
my2ter
commented
5 years ago Hi,
Any news with this, please?
This dead lock issue is becoming really critical now for us. Even without considering a high load, this is now being triggered when two tasks run simultaneously...
Cheers
Craft 3.1.31
angrybrad
commented
5 years ago @my2ter Craft 3.2.0 RC3 offloaded updating search indexes on an element save to a job in the queue (https://github.com/craftcms/cms/blob/3.2/CHANGELOG-v3.md#320-rc3---2019-07-02) along with several other performance improvements in the element saving pipeline and 3.2 will likely be GA in the next week or two. Any chance you could try to reproduce on the 3.2 branch?
To test, change your craftcms/cms requirement in composer.json to:
"require": {
"craftcms/cms": "^3.2.0-RC3",
"...": "..."
}Then run composer update.
Working on an app and consistently getting Deadlock errors on element save. I can reproduce this at will by opening up 3 or more entries and saving them all.
Past deadlock issues: https://github.com/craftcms/cms/issues/3003
Potentially relevant factors:
Here's the trace: