framework: Laravel 5. PDOException. QUEUE_DRIVER=database 1213 Deadlock

I use QUEUE_DRIVER=database When I run more than 10 workers, I get the following error:

'PDOException' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

[2015-01-19 16:45:00] production.ERROR: exception 'PDOException' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction' in /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:380
Stack trace:
#0 /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(380): PDOStatement->execute(Array)
#1 /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(606): Illuminate\Database\Connection->Illuminate\Database\{closure}(Object(Illuminate\Database\MySqlConnection), 'update `jobs` s...', Array)
#2 /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(570): Illuminate\Database\Connection->runQueryCallback('update `jobs` s...', Array, Object(Closure))
#3 /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(383): Illuminate\Database\Connection->run('update `jobs` s...', Array, Object(Closure))
#4 /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(328): Illuminate\Database\Connection->affectingStatement('update `jobs` s...', Array)
#5 /home/www/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(1747): Illuminate\Database\Connection->update('update `jobs` s...', Array)
#6 /home/www/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(181): Illuminate\Database\Query\Builder->update(Array)
#7 /home/www/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(146): Illuminate\Queue\DatabaseQueue->releaseJobsThatHaveBeenReservedTooLong('queuename1')
#8 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(180): Illuminate\Queue\DatabaseQueue->pop('queuename1')
#9 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(150): Illuminate\Queue\Worker->getNextJob(Object(Illuminate\Queue\DatabaseQueue), 'queuename1')
#10 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(113): Illuminate\Queue\Worker->pop(NULL, 'queuename1', '0', '3', '3')
#11 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(85): Illuminate\Queue\Worker->runNextJobForDaemon(NULL, 'queuename1', '0', '3', '3')
#12 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(101): Illuminate\Queue\Worker->daemon(NULL, 'queuename1', '0', '256', '3', '3')
#13 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(67): Illuminate\Queue\Console\WorkCommand->runWorker(NULL, 'queuename1', '0', '256', true)
#14 [internal function]: Illuminate\Queue\Console\WorkCommand->fire()
#15 /home/www/vendor/laravel/framework/src/Illuminate/Container/Container.php(523): call_user_func_array(Array, Array)
#16 /home/www/vendor/laravel/framework/src/Illuminate/Console/Command.php(114): Illuminate\Container\Container->call(Array)
#17 /home/www/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(253): Illuminate\Console\Command->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /home/www/vendor/laravel/framework/src/Illuminate/Console/Command.php(100): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 /home/www/vendor/symfony/console/Symfony/Component/Console/Application.php(874): Illuminate\Console\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#20 /home/www/vendor/symfony/console/Symfony/Component/Console/Application.php(195): Symfony\Component\Console\Application->doRunCommand(Object(Illuminate\Queue\Console\WorkCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#21 /home/www/vendor/symfony/console/Symfony/Component/Console/Application.php(126): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#22 /home/www/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(91): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#23 /home/www/artisan(34): Illuminate\Foundation\Console\Kernel->handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#24 {main}

Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: update `jobs` set `reserved` = 0, `reserved_at` = , `attempts` = attempts + 1 where `queue` = queuename1 and `reserved` = 1 and `reserved_at` <= 1421664300)' in /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php:614
Stack trace:
#0 /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(570): Illuminate\Database\Connection->runQueryCallback('update `jobs` s...', Array, Object(Closure))
#1 /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(383): Illuminate\Database\Connection->run('update `jobs` s...', Array, Object(Closure))
#2 /home/www/vendor/laravel/framework/src/Illuminate/Database/Connection.php(328): Illuminate\Database\Connection->affectingStatement('update `jobs` s...', Array)
#3 /home/www/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(1747): Illuminate\Database\Connection->update('update `jobs` s...', Array)
#4 /home/www/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(181): Illuminate\Database\Query\Builder->update(Array)
#5 /home/www/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(146): Illuminate\Queue\DatabaseQueue->releaseJobsThatHaveBeenReservedTooLong('queuename1')
#6 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(180): Illuminate\Queue\DatabaseQueue->pop('queuename1')
#7 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(150): Illuminate\Queue\Worker->getNextJob(Object(Illuminate\Queue\DatabaseQueue), 'queuename1')
#8 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(113): Illuminate\Queue\Worker->pop(NULL, 'queuename1', '0', '3', '3')
#9 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(85): Illuminate\Queue\Worker->runNextJobForDaemon(NULL, 'queuename1', '0', '3', '3')
#10 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(101): Illuminate\Queue\Worker->daemon(NULL, 'queuename1', '0', '256', '3', '3')
#11 /home/www/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(67): Illuminate\Queue\Console\WorkCommand->runWorker(NULL, 'queuename1', '0', '256', true)
#12 [internal function]: Illuminate\Queue\Console\WorkCommand->fire()
#13 /home/www/vendor/laravel/framework/src/Illuminate/Container/Container.php(523): call_user_func_array(Array, Array)
#14 /home/www/vendor/laravel/framework/src/Illuminate/Console/Command.php(114): Illuminate\Container\Container->call(Array)
#15 /home/www/vendor/symfony/console/Symfony/Component/Console/Command/Command.php(253): Illuminate\Console\Command->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#16 /home/www/vendor/laravel/framework/src/Illuminate/Console/Command.php(100): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /home/www/vendor/symfony/console/Symfony/Component/Console/Application.php(874): Illuminate\Console\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /home/www/vendor/symfony/console/Symfony/Component/Console/Application.php(195): Symfony\Component\Console\Application->doRunCommand(Object(Illuminate\Queue\Console\WorkCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 /home/www/vendor/symfony/console/Symfony/Component/Console/Application.php(126): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#20 /home/www/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(91): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#21 /home/www/artisan(34): Illuminate\Foundation\Console\Kernel->handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#22 {main}

About this issue

  • Original URL
  • State: closed
  • Created 9 years ago
  • Reactions: 1
  • Comments: 71 (49 by maintainers)

Commits related to this issue

Most upvoted comments

Took a few days attempting debugging before coming across this post; may be worth mentioning in the documentation to only use one worker with the database queue? Would have definitely saved me a lot of time.

+58
20TRIES on Apr 7, 2016

Whilst I appreciate that now, I think myself and many of the other commenters here weren’t aware that this would be such an issue, especially not at low volume (3 workers). It would be great if the documentation should note that the DB driver isn’t good for queuing at any kind of volume.

I’ve now switched to Redis, which isn’t as nice for testing it’ll be better than sticking with the MySQL workers and falling into these kind of issues.

+16
williamjulianvicary on Feb 6, 2017

You shouldn’t be limited to 1 worker with database driver as @GrahamCampbell said. Don’t know why he said that.

Anyways, hopefully we can figure this out. I’ve seen it before myself when running quite a few database workers.

+13
taylorotwell on May 17, 2016

So far I have processed 3,500 jobs with 8 workers running at the same time and have had no deadlock issues with this logic.

+11
taylorotwell on May 17, 2016

I get deadlocks even with 3 workers running for different queues each 😕

You can only use 1 worker if you want to use the database driver. If you need anything more, you should probably check out a real solution that’s designed for queuing like beanstalkd, or something that supports first on, first off, like redis. Both are fine choices for queue drivers.

+8
GrahamCampbell on Sep 24, 2015

I made some benchmarks on Amazon c4.large of the Laravel queueing methods (redis, beanstalkd, database):

I also implemented optimistic locking strategy for database driver (no deadlocks), blogpost + repo:

+7
ph4r05 on Dec 23, 2017

Also getting deadlocks with multiple database workers. Seems strange that the documentation suggests creating 8 processes within the supervisor config - yet it’s recommended to only use one (or am I missing the point here?).

+7
bbthorntz on Apr 11, 2016

I’ve came across this issue in 5.4 but sadly I don’t have the luxury of moving away from the DB driver.

I’m getting a deadlock on the delete query but I don’t understand why. By logging to file at the start and end of job processing, I can see that only one queue worker is picking up the job . Using show engine innodb status I can see that there is a lock on the row (as expected) and not on the table (also as expected) so I’m at a loss as to why the deadlock is occurring as there doesn’t appear to be anything conflicting with the delete statement.

When running queue:work its been very easy to reproduce an issue where the deadlock occurs then the queue hangs on 3 of the 4 workers. The other worker continues processing the queue, but when cancelled 1 of the 3 others becomes active and carries on processing. Likewise, when you cancel the new active worker, another inactive worker becomes active.

I’ve been able to work around this issue by adding retries to the deleteReserved() function:

    public function deleteReserved($queue, $id)
    {
        $this->database->transaction(function() use ($queue, $id) {
            if ($this->database->table($this->table)->lockForUpdate()->find($id)) {
                $this->database->table($this->table)->where('id', $id)->delete();
            }
        }, 5);
    }

I was able to process 200k jobs across 6 workers in 1 hour without any problems, whereas previously i’d receive lock issues with 4 workers and that would make the queues hang after only a couple of seconds.

@taylorotwell do you think its worth me putting this into a PR?

+5
micmania1 on Nov 9, 2017

For those following this issue; @ph4r05 is a fantastic provider of debugging information. This is the first time someone has checked where the deadlock actually occurs. In his case it’s a gap lock on the jobs_queue_reserved_at_index index. This index was removed in the default 5.5 jobs table in https://github.com/laravel/framework/commit/acd933fd6b793b0c79a2ba4eb462892cd6c460e3

So, if anyone still have deadlock issues, check your show engine innodb status, check it is a gap lock on the index, and if so, try removing it.

I should probably say something like “make sure you know your database before randomly removing indexes” or “take backups before drinking” or something. This is an internet forum, I’m talking about changes in important system tables. You know the deal. 😉

+4
sisve on Dec 12, 2017

If you’re running 10 workers, than the database driver isn’t right for you. I’d suggesting using a driver actually designed for queuing like beanstalk. The database driver is there for people who want a simple quick queuing setup for a low number of jobs.

+4
GrahamCampbell on Jan 19, 2015

Hey guys,

I’ve managed to test running 500,000 jobs by 15 workers without any deadlocks with the PR (https://github.com/laravel/framework/pull/31287). thoughts please 😃

+3
themsaid on Jan 29, 2020

As @rabbitfang wondered could the solution (if works) be backported in 5.1?

+3
ipa1981 on Nov 21, 2016

Will some (or all) of the fixes be backported to 5.1?

+3
rabbitfang on May 18, 2016

Laravel 8 with MySQL 5.7. 100 jobs with 8 workers and still run into same error. However, i noticed that job/worker ratio is crucial here. When i had 10 jobs on average with 8 workers, i had more deadlock errors. Thought it might help

+1
anon-pr on Nov 10, 2021

We have two different transactions going on.

  1. Fetch a new job (DatabaseQueue::pop)

    SELECT * FROM jobs WHERE queue = ? AND (…) ORDER BY id ASC LIMIT 1 FOR UPDATE UPDATE jobs SET reserved_at = ? WHERE id = ?

  2. Delete a job after it is processed (DatabaseQueue::deleteReserved)

    SELECT * FROM jobs WHERE id = ? FOR UPDATE DELETE FROM jobs WHERE id = ?

I’ll be guessing more than usual from here on.

It seems like the select that fetches the jobs issues locks on both the jobs_queue_index and the primary key (guessing that it does it in that order), while the select that exists in deleteReserved only locks the primary key. This allows the fetch-select to grab half (the index) of the locks they require and block waiting for the primary key lock, while the deletion transaction has grabbed the primary key lock and is now blocking on the index lock.

So, while I am still guessing wildly, can we trick the database to issue index locks? Can we modify the locking in deleteReserved so it’s based on SELECT * FROM jobs WHERE queue = ? AND id = ? FOR UPDATE which would hopefully issue identical locks as the fetch-select.

All this is extremely dependent on the table structure. Any other indexes would probably also add the the chaos and madness. Mmm madness…

+1
sisve on Dec 15, 2017

I just faced it with Laravel 5.3.26 having 8 workers and database driver. We are switching to Redis to remove even a possibility of such thing on our production server.

I am keeping an eye on this thread to know if this problem was\will be resolved for database driver.

+1
lezhnev74 on Jan 10, 2017

Duplicate primary key entry when inserting; genuine error in the job.

PDOException·SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘1525452’ for key ‘order_addresses_order_id_unique’

+1
20TRIES on May 18, 2016

@GrahamCampbell Maybe you just need to put a process ID to jobs table, for example, and read its value from artisan:worker to avoid dead locking?

+1
alexglue on Sep 25, 2015

I get deadlocks even with 3 workers running for different queues each 😕

+1
bequadrat on Sep 24, 2015

@GrahamCampbell I’ve got deadlock with just 5 workers. That’s too much for db-driver too?

+1
alexglue on Sep 14, 2015
Read more comments on GitHub
← framework: Laravel 10.x / PHP 8.2.x / PHP Fatal error: Declaration of Monolog ?
framework: Laravel 5.3 Job fails without calling failed() method →