Lock wait timeout when Laravel worker processes job batches

Question

I see in my logs the following error.

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction (SQL: select * from job_batches where id = xxx limit 1 for update)

I found out that this query is made right before Laravel updates the number of processed jobs in the database. But have no clue why this timeout occurs (The code is inside the transaction, so the lock should be released immediately).

P.S. I use Amazon SQS to drive my queues

P.S.S. I suggested that there are some jobs with not committed nested Laravel transactions. But I tried to reproduce such a scenario, and it looks like this suggestion is wrong.

Answer 1

Speaking short, it was because of big number of jobs in a batch. The solution would be to add jobs to the batch in smaller chunks (using add() method).

The problem occurs because two things happen inside one transaction (see code fragment below):

1. Total number of jobs is updated in database
2. Jobs are sent to the queue (Amazon SQS in my case)

The key point is that second statement takes a lot of time if there are many jobs. And as long as it is performed transaction can't be committed (and the row affected in the first statement stays locked). But as soon as the first job is pushed to the queue it is processed by worker. After processing the job worker tries to update the row in database, it performs SELECT ... FOR UPDATE. Which waits until timeout occurs

This is the corresponding fragment of Laravel's code:

$this->repository->transaction(function () use ($jobs, $count) {
    // Statement 1 (the affected row stays locked until transaction is committed)
    $this->repository->incrementTotalJobs($this->id, $count);

    // Statement 2 (can take a lot of time)
    $this->queue->connection($this->options['connection'] ?? null)->bulk(
        $jobs->all(),
        $data = '',
        $this->options['queue'] ?? null
    );
});