Laravel Queues in Action (2nd edition) is now available!

Avoiding Lock Timeouts When Dispatching Large Batches of Jobs in Laravel

Updated: Jul 5, 2022 — 1 min Read#queues

Imagine dispatching a large batch, one that has thousands of jobs:

Bus::batch($jobs)->dispatch();

During the dispatching process, Laravel creates a record inside the job_batches database table and then acquires a lock on that row while the jobs are being dispatched to the queue.

Workers might pick some of those jobs up before the full batch finishes dispatching. And after processing any of those jobs, the workers will try to update the database row for the batch. But that row will be locked by the process that's still dispatching more jobs.

This results in several worker processes waiting for the lock to be released. Which might cause your entire queue to be stuck.

image

In addition, you may start getting an exception like this:

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction.

This is the database terminating the query sent by the workers because it exceeded the allowed wait time. This means the workers will consider the jobs as failed and try to retry them while they have actually completed successfully. It's just that the batch update is failing.

To avoid this issue, I recommend that you do not push too many jobs to a batch in one go. Dispatch them in chunks instead. In addition, you may configure a delay on the batch jobs so workers won't pick them up too fast.

Hey! 👋 If you find this content useful, consider sponsoring me on GitHub.

You can also follow me on Twitter, I regularly post about all things Laravel including my latest video tutorials and blog posts.

By Mohamed Said

Hello! I'm a former Laravel core team member & VP of Engineering at Foodics. In this publication, I share everything I know about Laravel's core, packages, and tools.

You can find me on Twitter and Github.

This site was built using Wink. Follow the RSS Feed.