[bug-report] MySQL deadlocks when used with Feed Me

[tomdavies]

Hey @ostark,

I'm seeing frequent MySQL deadlocks when using this plugin to run Feed Me feeds in our dev environments.

If the Feed Me import config creates relationships to other elements, and I have ASYNC_QUEUE_CONCURRENCY set to anything > 1, I get a bunch of errors like so:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction The SQL being executed was: INSERT INTO relations (fieldId, sourceId, sourceSiteId, targetId, sortOrder, dateCreated, dateUpdated, uid) VALUES (104, 246236, NULL, 31317, 1, '2019-11-11 14:47:58', '2019-11-11 14:47:58', '6ade060b-2f5c-4f6c-9f11-26d2f8afe40e'), (104, 246236, NULL, 19343, 2, '2019-11-11 14:47:58', '2019-11-11 14:47:58', '39c20ea7-b126-443d-93d9-e32d4dd6a489') - Schema.php: 664.

Craft CMS: 3.3.13 with the default (DB) queue driver MySQL 5.7 craft-async-queue: 2.0.0

Happy to provide further details / reduced test case on request

Not currently seeing this in production so not a massive deal, but if you've any ideas I'm all ears :)

[ostark]

Thanks for your report @tomdavies!

This is the first time someone reported a Deadlock issue here. However, after a bit of research I discovered this: https://github.com/craftcms/feed-me/issues/355

I did not further investigations so far. Not sure if it is possible to fix feedme.

Maybe ASYNC_QUEUE_CONCURRENCY=1 would be a better default than 2. 🤔

[tomdavies]

I actually saw that feedme issue too - happy report there if you think it's more likely a Feed Me issue?

ASYNC_QUEUE_CONCURRENCY=1 does seem a safer default overall, though outside of this use case we've found the concurrency works great

[tomdavies]

I've now confirmed this is happening in the production env too (we have a bunch of feeds running overnight triggered by cron, and one overran, so that another started before the first ended. Going to raise it as a Feed Me issue and will report back

[ostark]

Please give 2.1 a try https://github.com/ostark/craft-async-queue/commit/f53f835f446afa7dbb5af8d42b0482ba3fbafbce (not sure if it solves the feedme issue)

[gixxy22]

this is still an issue when using feedme. in fact, it might not be a good idea to let feedme use multi concurrency due to it adding/updating entries. for example if 2 feeds are running at the same time and they both find the same entry to update/add, it all goes wrong, and we end up with deadlocks on updates and duplicate entries on add.

is there a way to disable feedme from using multiple threads?