[5.x]: Deadlock found when trying to get lock on entries_authors table #15768
Comments
|
@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? |
|
@angrybrad I do have the default queue runner that comes with the cms. |
|
@yannkost are the web servers in a load-balanced environment? |
|
@angrybrad I can't tell as I don't know the setup of the hosting. |
|
@angrybrad But it sometimes also happens on localhost, but more rarely. |
|
@angrybrad We ran in to the same issue. In our case it is a load-balanced webserver pointing to a single database.
|
|
Hi @angrybrad, just in case you need more examples. We are also running into deadlocks and we are running on a load balanced environment with 2 web servers and 2 workers, where we're using one daemon per worker. |
|
You say this happens locally for you (even if it is more rarely). From the stack trace, I’d audit these two bits of custom code to ensure that whatever database resources they are grabbing are released as quickly as possible and to see if adding mutexes around critical database queries would prevent multiple queries from accessing the same bits of information simultaneously on your logic. Added multiple queue runners (like using the default web-based ones) in a load-balanced environment can exacerbate the deadlock issue if the database is the point of resource contention. @MGxpwr Assuming you’re using the default web-based queue runner, this applies:
Try getting it down to a single, dedicated queue runner and see if the behavior changes. https://craftcms.com/docs/5.x/system/queue.html#queue-runners Same - try getting it down to a single, dedicated queue worker and see if the behavior changes |
I'm seeing this locally too @angrybrad . I've disabled all third party plugins too. I only have Craft's Amazon S3 2.2.1 and CKEditor 4.3.1.
All I'm trying to do is go to Entries -> All Entries. I hit "New Entry" in upper right and pick our blog section. And I get the stacktrace I included at the bottom (not every time, maybe 2nd to 5th try) This flow does a post with the section and siteId to /index.php?p=cms%2Factions%2Fentries%2Fcreate&site=en_us&v=1730410887394 Then it redirects to /cms/entries/blogEntry/new?site=en_us with the PDOException for deadlock found... So I believe the issue is the flow of creating a new entry from All Entries. Nothing with the request stands out when comparing the POSTs but the one from All Entries can lead to exceptions. It is NOT an issue if I go to Entries -> Blog and then just hit "New Entry". There is no deadlock exception and the URL becomes something like this: /entries/blogEntry/4326244?site=en_us&draftId=23831&fresh=1 But... Entries -> All Entries -> New Entry (pick a section) leads to this (usually within 5 tries): Here is the deadlock info from MySQL LATEST DETECTED DEADLOCK2024-10-31 15:07:35 132277298267712 *** (1) HOLDS THE LOCK(S): *** (1) WAITING FOR THIS LOCK TO BE GRANTED: *** (2) TRANSACTION: *** (2) HOLDS THE LOCK(S): *** (2) WAITING FOR THIS LOCK TO BE GRANTED: *** WE ROLL BACK TRANSACTION (2) |
|
@joshmcoffman Any chance that's on an environment we could get Craft control panel and SSH access to? If so, could you send to [email protected] and reference this issue? |
@angrybrad I emailed support. Will set you up on an isolated cloned environment. |
|
I am seeing the same deadlock errors on a Craft Cloud environment as well with Feed Me imports/entry saves. Example Feed Me log error: Full stacktrace logged by Sentry for another deadlock error: |
|
@angrybrad If it helps as Craft Cloud environment, you can potentially use our environment to help inspect. We have regular Feed Me feeds running which is more likely to trigger this. |
|
We have a similar problem with one of our projects. Its self hosted with the following versions: Application Info ------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-03-05 13:00:05 0xe77927097040
*** (1) TRANSACTION:
TRANSACTION 13933821, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 16 lock struct(s), heap size 1128, 7 row lock(s), undo log entries 3
MariaDB thread id 621446, OS thread handle 254507531989056, query id 9514775 localhost 127.0.0.1 ukr Update
INSERT INTO `craft_entries_authors` (`entryId`, `authorId`, `sortOrder`) VALUES (9679, 8, 1)
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 893 page no 13 n bits 496 index PRIMARY of table `projectname_cms_prod`.`craft_entries_authors` trx id 13933821 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** CONFLICTING WITH:
RECORD LOCKS space id 893 page no 13 n bits 496 index PRIMARY of table `projectname_cms_prod`.`craft_entries_authors` trx id 13933820 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
RECORD LOCKS space id 893 page no 13 n bits 496 index PRIMARY of table `projectname_cms_prod`.`craft_entries_authors` trx id 13933821 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 13933820, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 16 lock struct(s), heap size 1128, 7 row lock(s), undo log entries 3
MariaDB thread id 621445, OS thread handle 254507543949376, query id 9514772 localhost 127.0.0.1 ukr Update
INSERT INTO `craft_entries_authors` (`entryId`, `authorId`, `sortOrder`) VALUES (9678, 8, 1)
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 893 page no 13 n bits 496 index PRIMARY of table `projectname_cms_prod`.`craft_entries_authors` trx id 13933820 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** CONFLICTING WITH:
RECORD LOCKS space id 893 page no 13 n bits 496 index PRIMARY of table `projectname_cms_prod`.`craft_entries_authors` trx id 13933820 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
RECORD LOCKS space id 893 page no 13 n bits 496 index PRIMARY of table `projectname_cms_prod`.`craft_entries_authors` trx id 13933821 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2) |
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).
The text was updated successfully, but these errors were encountered: