Batch job got 1205 "Lock wait timeout exceeded" error

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']
        );
    }