Database Deadlock problems on element save

[VitaliiTsilnyk]

Description

We have a project on Craft 3 with a pretty big database (~1G), filled mostly with Entry elements (almost 100k). It's a multisite setup with 10 sites. The problem is sometimes we get database deadlock errors. It's really hard to catch and properly debug those, most of the time they occur when we're trying to update big amount of entries in batch one after one.

We've noticed that most of the time (almost 100%) the problem occurs while Craft is trying to delete elements for non-existent sites after successfully saving an element here.

We already tried to change the order in which the elements are being processed, to wrap every operation in transaction… Nothing helps. Right now the only solution that works for us is a retry logic.

As we understand, deletion of content for non-existent sites is not critical to be executed after each element save. Deletion of a site is not a usual operation, elements are being saved on far much bigger rate. Is it possible to move this logic out in a separate background job that would be executed only when a site is being deleted?

Example of an error message we get:

Exception 'yii\db\Exception' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The SQL being executed was: DELETE FROM `elements_sites` WHERE (`elementId`='49289') AND (NOT (`siteId` IN ('1', '6', '2', '5', '3', '4', '7', '8', '9', '10')))'

Steps to reproduce

1. Have 100k of Entry elements in the database, some of them are related to each other (through the Entries field)
2. Start a process to simply resave 1000 of them in a loop, the job usually dies at around 100th element.

Additional info

• Craft version: Craft Pro 3.0.10.3, but we had this problem since early beta releases
• PHP version: 7.1.13
• Database driver & version: MySQL 5.7.21
• Plugins & versions: not relevant

[brandonkelly]

If a site is deleted, any element content associated with it will also be deleted immediately. That logic there is more for when an element goes from supporting a site to not supporting it (for example, if you have a section that is enabled for 2 sites, and then you edit the section to deselect one of those sites).

That said, I agree it’s not necessary to do this on every save. We should at least be able to detect whether there are any rows that need to be deleted, before running the delete query.

[brandonkelly]

As of the next release, Craft will ensure that there are any rows to delete before executing the DELETE query, which should help avoid these deadlock errors.