Batch job got 1205 "Lock wait timeout exceeded" error

150
January 26, 2022, at 12:50 PM

I have a batch with many jobs almost over 9,000 and the first of them consumed by workers always get 1205 "Lock wait timeout exceeded" errors. I have submitted an issue on GitHub but it was closed so I'm asking for help here.

I have pasted some details below from that issue for convenience.

  • Laravel Version: 8.78.1
  • PHP Version: 8.0.13
  • Database Driver & Version: Mysql Ver 8.0.27
  • Queue Driver: SQS

When a batch has many jobs like over 10,000 jobs and each job can be finished in several seconds, the batch may take several minutes to dispatch all jobs to SQS, during that time some of the dispatched jobs that have been consumed by workers will be waiting MySQL lock to decrement the pending jobs number in queued_job_batches table but the MySQL lock is still being occupied by the dispatching process.
After waiting for 50 seconds which is the MySQL innodb_lock_wait_timeout value but the dispatching still has not finished yet, an "SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded" error will be thrown in the worker process.

When dispatching, the below step may take a long time to finish and the MySQL transaction will lock the batch row for a long time. https://github.com/laravel/framework/blob/8.x/src/Illuminate/Bus/Batch.php#L186-L194

    $this->repository->transaction(function () use ($jobs, $count) {
            $this->repository->incrementTotalJobs($this->id, $count);
            $this->queue->connection($this->options['connection'] ?? null)->bulk(
                $jobs->all(),
                $data = '',
                $this->options['queue'] ?? null
            );
        });

Some of the dispatched jobs that have been consumed by workers are trying to decrement pending jobs but will have to wait for the transaction above to commit. https://github.com/laravel/framework/blob/8.x/src/Illuminate/Bus/DatabaseBatchRepository.php#L138-L152

    public function decrementPendingJobs(string $batchId, string $jobId)
    {
        $values = $this->updateAtomicValues($batchId, function ($batch) use ($jobId) {
            return [
                'pending_jobs' => $batch->pending_jobs - 1,
                'failed_jobs' => $batch->failed_jobs,
                'failed_job_ids' => json_encode(array_values(array_diff(json_decode($batch->failed_job_ids, true), [$jobId]))),
            ];
        });
        return new UpdatedBatchJobCounts(
            $values['pending_jobs'],
            $values['failed_jobs']
        );
    }
READ ALSO
Receive messages/events from SQS and map them to Dynamodb

Receive messages/events from SQS and map them to Dynamodb

I am trying to figure out a way where I can read my SQS events and map them to DynamoDB using JavaI have a SQS in AWS which will get some messages having some information e

105
Why does jQuery or a DOM method such as getElementById not find the element?

Why does jQuery or a DOM method such as getElementById not find the element?

What are the possible reasons for documentgetElementById, $("#id") or any other DOM method / jQuery selector not finding the elements?

41
How to remove multiple children in one call - Realtime Firebase

How to remove multiple children in one call - Realtime Firebase

I wan to delete multiple children with known keyI searched all the related posts but it doesn't seem to work

90