search

craftcms / cms

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

[5.x]: Deadlock found when trying to get lock on entries_authors table #15768

Open yannkost opened 1 day ago

yannkost commented 1 day ago

What happened?

Description

We have an import job for a website that sometimes fails due to a deadlock issue. (This happens like 1 times out of 3). 


2024-09-19 11:06:00 [queue.INFO] [nystudio107\seomatic\services\MetaContainers::invalidateContainerCacheByPath] Meta container cache cleared:  / 1 {"memory":172092664} 
2024-09-19 11:06:00 [queue.INFO] [nystudio107\seomatic\services\MetaBundles::updateMetaBundle] Meta bundle updated: section id: 6 from siteId: 1 {"memory":172132856} 
2024-09-19 11:06:00 [queue.WARNING] [craft\db\mysql\Schema::rollBackSavepoint] Tried to roll back a savepoint, but it does not exist: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT LEVEL3 does not exist
The SQL being executed was: ROLLBACK TO SAVEPOINT LEVEL3 {"memory":172866048} 
2024-09-19 11:06:00 [queue.WARNING] [craft\db\mysql\Schema::rollBackSavepoint] Tried to roll back a savepoint, but it does not exist: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT LEVEL2 does not exist
The SQL being executed was: ROLLBACK TO SAVEPOINT LEVEL2 {"memory":172852288} 
2024-09-19 11:06:00 [queue.WARNING] [craft\db\mysql\Schema::rollBackSavepoint] Tried to roll back a savepoint, but it does not exist: SQLSTATE[42000]: Syntax error or access violation: 1305 SAVEPOINT LEVEL1 does not exist
The SQL being executed was: ROLLBACK TO SAVEPOINT LEVEL1 {"memory":172846720} 
2024-09-19 11:06:00 [queue.ERROR] [import_log] An error occurred during the variations creation procedure. Terminating job. SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The SQL being executed was: INSERT INTO `entries_authors` (`entryId`, `authorId`, `sortOrder`) VALUES (20157, 1, 1) {"memory":172809296} 
2024-09-19 11:06:00 [queue.ERROR] [application] SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The SQL being executed was: INSERT INTO `entries_authors` (`entryId`, `authorId`, `sortOrder`) VALUES (20157, 1, 1) {"memory":172809512} 
2024-09-19 11:06:00 [queue.ERROR] [craft\queue\QueueLogBehavior::afterError]  [17372] Create Variations from bikes (attempt: 3, pid: 2522969) - Error (time: 15.876s): SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
The SQL being executed was: INSERT INTO `entries_authors` (`entryId`, `authorId`, `sortOrder`) VALUES (20157, 1, 1) {"memory":169541600} 
2024-09-19 11:06:00 [queue.ERROR] [yii\db\Exception] PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction in /home/dahivabi/public_html/yoo/dev/vendor/yiisoft/yii2/db/Command.php:1320
Stack trace:
#0 /home/dahivabi/public_html/yoo/dev/vendor/yiisoft/yii2/db/Command.php(1320): PDOStatement->execute()
#1 /home/dahivabi/public_html/yoo/dev/vendor/yiisoft/yii2/db/Command.php(1120): yii\db\Command->internalExecute('INSERT INTO `en...')
#2 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/helpers/Db.php(1037): yii\db\Command->execute()
#3 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/elements/Entry.php(2613): craft\helpers\Db::batchInsert('{{%entries_auth...', Array, Array)
#4 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/elements/Entry.php(2517): craft\elements\Entry->_saveAuthors()
#5 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(3790): craft\elements\Entry->afterSave(true)
#6 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(1221): craft\services\Elements->craft\services\{closure}()
#7 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(3595): craft\services\Elements->ensureBulkOp(Object(Closure))
#8 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(1890): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), false, false, NULL, Array, false, false, true)
#9 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(1221): craft\services\Elements->craft\services\{closure}()
#10 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(1877): craft\services\Elements->ensureBulkOp(Object(Closure))
#11 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Revisions.php(164): craft\services\Elements->duplicateElement(Object(craft\elements\Entry), Array)
#12 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/elements/Entry.php(2663): craft\services\Revisions->createRevision(Object(craft\elements\Entry), 1, NULL)
#13 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(3835): craft\elements\Entry->afterPropagate(false)
#14 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(1221): craft\services\Elements->craft\services\{closure}()
#15 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(3595): craft\services\Elements->ensureBulkOp(Object(Closure))
#16 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/services/Elements.php(1307): craft\services\Elements->_saveElementInternal(Object(craft\elements\Entry), true, false, NULL, Array, false, false, false)
#17 /home/dahivabi/public_html/yoo/dev/vendor/yoo/project-extension/src/services/ProjectExtensionService.php(59): craft\services\Elements->saveElement(Object(craft\elements\Entry))
#18 /home/dahivabi/public_html/yoo/dev/vendor/yoo/project-extension/src/jobs/Variations.php(144): yoo\projectextension\services\ProjectExtensionService::validateAndSafe(Object(craft\elements\Entry), 'Bike')
#19 /home/dahivabi/public_html/yoo/dev/vendor/yiisoft/yii2-queue/src/Queue.php(243): yoo\projectextension\jobs\Variations->execute(Object(craft\queue\Queue))
#20 /home/dahivabi/public_html/yoo/dev/vendor/yiisoft/yii2-queue/src/cli/Queue.php(147): yii\queue\Queue->handleMessage(17372, 'O:36:"yoo\\proje...', 300, 3)
#21 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/queue/Queue.php(191): yii\queue\cli\Queue->handleMessage(17372, 'O:36:"yoo\\proje...', 300, 3)
#22 /home/dahivabi/public_html/yoo/dev/vendor/craftcms/cms/src/queue/Queue.php(166): craft\queue\Queue->executeJob()```

### Steps to reproduce

1.Start a queue job that is inserting/updating a big number of entries (+500). Note: The same entries can be updated multiple times in the same queue. (We have to iterate 3 times over the entries because of the complexity of the different updates. The deadlock can happen at any of the 3 iteration steps, also the first one.)

### Expected behavior

The queue should run without getting a deadlock.

### Actual behavior

On random moments the queue fails due to a deadlock on the  entries_authors table.

### Craft CMS version

5.4.4

### PHP version

8.2

### Operating system and version

_No response_

### Database type and version

_No response_

### Image driver and version

_No response_

### Installed plugins and versions

- Seomatic
- Retour
- Formie
- Timber
angrybrad commented 16 hours ago

@yannkost Are you using the default web-based queue runner, or do you have an alternative setup (CLI/cron/daemon)?

If the latter, do you have one queue runner set up or multiple?