dereuromark / cakephp-queue

Queue plugin for CakePHP - simple, pure PHP and without dependencies.
MIT License
306 stars 137 forks source link

PDOException: Deadlock found when trying to get lock #313

Closed kinolan closed 2 years ago

kinolan commented 2 years ago

version of CakePHP: 4.3.3 version of dereuromark/cakephp-queue: 6.2.1

I get a deadlock while running a worker. Is there any way to solve this?

Exception: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
In [/var/www/batch/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php, line 39]

2022-01-06 17:10:10 error: [PDOException] SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction in /var/www/batch/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php on line 39

Stack Trace:
- /var/www/batch/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php:39
- /var/www/batch/vendor/cakephp/cakephp/src/Database/Connection.php:337
- /var/www/batch/vendor/cakephp/cakephp/src/Core/Retry/CommandRetry.php:70
- /var/www/batch/vendor/cakephp/cakephp/src/Database/Connection.php:340
- /var/www/batch/vendor/cakephp/cakephp/src/Database/Query.php:250
- /var/www/batch/vendor/cakephp/cakephp/src/ORM/Query.php:1148
- /var/www/batch/vendor/cakephp/cakephp/src/Datasource/QueryTrait.php:287
- /var/www/batch/vendor/cakephp/cakephp/src/ORM/Query.php:1096
- /var/www/batch/vendor/cakephp/cakephp/src/Datasource/QueryTrait.php:494
- /var/www/batch/vendor/dereuromark/cakephp-queue/src/Model/Table/QueuedJobsTable.php:602
- /var/www/batch/vendor/cakephp/cakephp/src/Database/Connection.php:686
- /var/www/batch/vendor/dereuromark/cakephp-queue/src/Model/Table/QueuedJobsTable.php:617
- /var/www/batch/vendor/dereuromark/cakephp-queue/src/Queue/Processor.php:139
- /var/www/batch/vendor/dereuromark/cakephp-queue/src/Command/RunCommand.php:96
- /var/www/batch/vendor/cakephp/cakephp/src/Console/BaseCommand.php:179
- /var/www/batch/vendor/cakephp/cakephp/src/Console/CommandRunner.php:334
- /var/www/batch/vendor/cakephp/cakephp/src/Console/CommandRunner.php:172
- /var/www/batch/bin/cake.php:12
More Information ##### crontab ```bash */10 * * * * /var/www/batch/bin/cake queue run -q > /dev/null ``` ##### worker ```bash $ /var/www/batch/bin/cake queue worker Please use with [action] [PID] added. Actions are: - end: Gracefully end a worker/process, use "all"/"server" for all - kill: Kill a worker/process, use "all"/"server" for all - clean: Last jobs are: - [7557] batch.example.jp:231c36e4e0bf6c03dbc5ddf2773fa47d50b0b133 (running) Last run: 2022/01/06 17:12 - [6089] batch.example.jp:dc29d6cad4a72abf5af0d255ef5fc63f92822e35 (running) Last run: 2022/01/06 17:10 - [4745] batch.example.jp:6954d61ce0908a0de9fbb06fd4bceb950eb09509 (running) Last run: 2022/01/06 16:52 - [30707] batch.example.jp:1f322e402a5c29677c4e09214c2b0004a68b3bfa (running) Last run: 2022/01/06 16:21 ``` ##### info ```bash $ /var/www/batch/bin/cake queue info 13 tasks available: * JobTaskB [addable via CLI] * UpdateProjectPlanSearch * JobTaskA [addable via CLI] * Queue.CostsExample [addable via CLI] * Queue.Example [addable via CLI] * Queue.RetryExample [addable via CLI] * Queue.Email [addable via CLI] * Queue.MonitorExample [addable via CLI] * Queue.UniqueExample [addable via CLI] * Queue.Execute [addable via CLI] * Queue.ExceptionExample [addable via CLI] * Queue.SuperExample [addable via CLI] * Queue.ProgressExample [addable via CLI] ------------------------------------------------------------------------------- Current Settings: * defaultworkertimeout: 3600 * workermaxruntime: 900 * maxworkers: 4 * sleeptime: 30 * defaultworkerretries: 1 * workertimeout: * multiserver: no * cleanuptimeout: 2592000 ------------------------------------------------------------------------------- Total unfinished jobs: 4 Current running workers: 4 Last run: 2022/01/06 17:15 Server name: batch.example.jp ------------------------------------------------------------------------------- Jobs currently in the queue: - JobTaskA : 0 - JobTaskB : 4 ------------------------------------------------------------------------------- Finished job statistics: - JobTaskA: - Finished Jobs in Database: 1948 - Average Job existence : 5s - Average Execution delay : 5s - Average Execution time : 0s - JobTaskB: - Finished Jobs in Database: 216863 - Average Job existence : 25s - Average Execution delay : 25s - Average Execution time : 0s ```

Thanks.

dereuromark commented 2 years ago

Did you do an update in between? Serialization failure usually means that the data is corrupt and probably the objects/classes changed in a way that it cannot be restored. You might have to rebuild those jobs.

In general it is usually safer to store json_encode()d data.

kinolan commented 2 years ago

Thanks for your reply.

It's been a while since then and the Deadlock notifications have stopped. I didn't change any settings or update anything, so it looks like there was a problem with the task or the execution environment. I did a lot of research and couldn't find the cause. I'll close the issue and see how it goes for a while.