Laravel Queue deadlock when deleting from jobs table

Question

I've just got Laravel Queue working with my project. I have Supervisor enabled so everything is automated but when jobs are being deleted from the jobs table, deadlocks are occurring. I am using MySQL as the driver.

How can I stop these deadlocks?

Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction (SQL: delete from `jobs` where `id` = 813)

[program:laravel-worker]
process_name=%(program_name)s_%(process_num)02d
command=php /home/controlapi/public_html/total-control-api/artisan queue:work database --sleep=3 --tries=3
autostart=true
autorestart=true
user=controlapi
numprocs=10
redirect_stderr=true
stdout_logfile=/home/controlapi/public_html/total-control-api/storage/logs/worker.log

Edit: Below is my query that is running with each job

DB::transaction(function() use ($decode, $status, $c_decode, $m_decode, $d_decode) {

    try {
        $query = DB::connection('mysql2')->statement('INSERT INTO transactions (json, uuid, status, divisionName, divisionUuid, merchantName, merchantUuid, channelName, channelUuid, channelLogin, channelPwd)
                                                        VALUES (:transaction_1, :uuid, :status_1, :divisionName, :divisionUuid, :merchantName, :merchantUuid, :channelName, :channelUuid, :channelLogin, :channelPwd)
                                                        ON DUPLICATE KEY UPDATE json = :transaction_2, status = :status_2',
                                                        array('transaction_1' => $this->transaction,
                                                            'transaction_2' => $this->transaction,
                                                            'uuid' => $decode->payload->id,
                                                            'status_1' => $status->status,
                                                            'status_2' => $status->status,
                                                            'divisionName' => $d_decode->divisionInfo->name,
                                                            'divisionUuid' => $d_decode->divisionInfo->id,
                                                            'merchantName' => $m_decode->merchantInfo->name,
                                                            'merchantUuid' => $m_decode->merchantInfo->id,
                                                            'channelName' => $c_decode->channelInfo->name,
                                                            'channelUuid' => $c_decode->channelInfo->channel,
                                                            'channelLogin' => $c_decode->channelInfo->login,
                                                            'channelPwd' => $c_decode->channelInfo->pwd));
    }
    catch(Exception $e) {
        Storage::prepend('transactions_insert_errors.txt', Carbon::now('UTC')->toDateTimeString()."\nUUID:".$decode->payload->id."\n".$e->getMessage()."\n\n");
    }
}, 5);

Has nothing to do with the jobs settings, more likely how you interact with the database, can we see that. Especially where you delete from the db — mrhn, Jun 16 '19 at 21:28
Hi @MartinHenriksenm I'm not deleting anything from the database. I'm doing an upsert. Have added the query to my original post. I don't think think this is what is causing the issue. — Dally, Jun 17 '19 at 04:54
How many jobs do you run at once? this is the database failing to obtain a db lock — mrhn, Jun 18 '19 at 08:27
Well I have 10 process running at the moment. At any given moment, a webhook can pass over 200-600 responses in seconds which my app has to then process. — Dally, Jun 18 '19 at 10:15

score 2 · Answer 1 · answered Apr 07 '20 at 13:41

Unfortunately, a "database" as a driver is not a good solution for large queues that use multiple processes. If possible, try reducing the number of "numprocs" in the supervisor configuration (ideally to 1). Upgrading to MySQL 8 may also help.

Otherwise, use a more suitable queue driver: for example Redis.

See a discussion of this issue: https://github.com/laravel/framework/issues/7046

Laravel Queue deadlock when deleting from jobs table

1 Answers1