search

craftcms / cms

Build bespoke content experiences with Craft.
https://craftcms.com
Other
3.21k stars 624 forks source link

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

Closed white-lukas closed 1 month ago

white-lukas commented 1 month ago

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

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 commented 1 month ago

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

white-lukas commented 1 month ago

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 commented 1 month ago

We now also get it without running any imports.

List of plugins:

brandonkelly commented 1 month ago

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 commented 1 month ago

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