[5.x]: Deadlocks on elements_bulkops when saving entries

[white-lukas]

What happened?

Description

When working with multiple people in the backend, almost on every save I'm getting internal servers errors.

stacktrace:

PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction in /var/www/html/vendor/yiisoft/yii2/db/Command.php:1320
Stack trace:
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1320): PDOStatement->execute()
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute(NULL)
#2 /var/www/html/vendor/craftcms/cms/src/helpers/Db.php(1168): yii\db\Command->execute()
#3 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1137): craft\helpers\Db::delete('{{%elements_bul...', Array)
#4 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1173): craft\services\Elements->endBulkOp('yugpysczdu')
#5 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(3498): craft\services\Elements->ensureBulkOp(Object(Closure))
#6 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1251): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, true, NULL, Array, false, true, true)
#7 /var/www/html/vendor/craftcms/cms/src/controllers/ElementsController.php(1695): craft\services\Elements->saveElement(Object(craft\elements\Entry), true, true, NULL, false, true)
#8 [internal function]: craft\controllers\ElementsController->actionApplyDraft()
#9 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#10 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#11 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('apply-draft', Array)
#12 /var/www/html/vendor/craftcms/cms/src/web/Application.php(349): yii\base\Module->runAction('elements/apply-...', Array)
#13 /var/www/html/vendor/craftcms/cms/src/web/Application.php(650): craft\web\Application->runAction('elements/apply-...', Array)
#14 /var/www/html/vendor/craftcms/cms/src/web/Application.php(311): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#15 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#16 /var/www/html/web/index.php(13): yii\base\Application->run()
#17 {main}

Next yii\db\Exception: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The SQL being executed was: DELETE FROM `elements_bulkops` WHERE `key`='yugpysczdu' in /var/www/html/vendor/yiisoft/yii2/db/Schema.php:676
Stack trace:
#0 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1325): yii\db\Schema->convertException(Object(PDOException), 'DELETE FROM `el...')
#1 /var/www/html/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute('DELETE FROM `el...')
#2 /var/www/html/vendor/craftcms/cms/src/helpers/Db.php(1168): yii\db\Command->execute()
#3 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1137): craft\helpers\Db::delete('{{%elements_bul...', Array)
#4 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1173): craft\services\Elements->endBulkOp('yugpysczdu')
#5 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(3498): craft\services\Elements->ensureBulkOp(Object(Closure))
#6 /var/www/html/vendor/craftcms/cms/src/services/Elements.php(1251): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, true, NULL, Array, false, true, true)
#7 /var/www/html/vendor/craftcms/cms/src/controllers/ElementsController.php(1695): craft\services\Elements->saveElement(Object(craft\elements\Entry), true, true, NULL, false, true)
#8 [internal function]: craft\controllers\ElementsController->actionApplyDraft()
#9 /var/www/html/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#10 /var/www/html/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#11 /var/www/html/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('apply-draft', Array)
#12 /var/www/html/vendor/craftcms/cms/src/web/Application.php(349): yii\base\Module->runAction('elements/apply-...', Array)
#13 /var/www/html/vendor/craftcms/cms/src/web/Application.php(650): craft\web\Application->runAction('elements/apply-...', Array)
#14 /var/www/html/vendor/craftcms/cms/src/web/Application.php(311): craft\web\Application->_processActionRequest(Object(craft\web\Request))
#15 /var/www/html/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#16 /var/www/html/web/index.php(13): yii\base\Application->run()
#17 {main}
Additional Information:
Array
(
    [0] => 40001
    [1] => 1213
    [2] => Deadlock found when trying to get lock; try restarting transaction
)

Steps to reproduce

1. work with multiple authors in the backend
2. save an entry
3. get an internal server error

Expected behavior

No internal server error 😉

Actual behavior

internal server error and unable to save your changes

Craft CMS version

5.2.5

PHP version

8.3.8

Operating system and version

Ubuntu 24.04

Database type and version

mysql 8.0

Image driver and version

No response

Installed plugins and versions

[i-just]

Hi, thanks for getting in touch. Do you have multiple queue runners? Could you also share a list of plugins you use?

[white-lukas]

Hi @i-just ,
No we just have 1 queue runner running.

We do have some custom imports, do we need to wrap those inside the new bulkOps?

[white-lukas]

We now also get it without running any imports.

List of plugins:

• amazon S3: 2.21
• blitz: 4.5.0
• craft commerce: 5.0.12.2
• gift voucher: 4.0.1
• hyper: 2.0.2
• imager-x: 5.0.1
• navigatoin: 3.0.2
• retour: 5.0.2
• seomatic: 5.1.0
• sprig: 3.1.0
• stripe: 5.0.1.1
• table maker: 5.0.0

[brandonkelly]

Just created a PR with a potential fix for this: #15384.

Would you mind testing and letting me know if it solves the issue for you?

"craftcms/cms": "dev-feature/db2 as 5.3.0-alpha",

[white-lukas]

It's a bit less frequent, but still there, both on back and front-end when for example editing addresses on account/commerce cart
That's with 4-5 people actively working in the backend