[Resolved] – MySQL lock wait timeout exceeded using Laravel queues and jobs

Valerio Barbera
Title graphic with Laravel and MySQL logos and Lock Wait Timeout Exceeded text

Hi, I’m Valerio, software engineer and CTO at Inspector.

One of the most-read articles I’ve posted on our blog relates to a queue and jobs configuration in a Laravel application.

Queues and Jobs introduce parallel task execution in your app. And it’s also the most important step to enable a new level of scalability while keeping server resources cost-friendly.

So I decided to write about some side effects that Laravel queues adoption can cause in your application. I also want to provide solutions based on my real-life experience.

Use MySQL database transactions

One of the best-known uses of database transactions is when an error occurs, the transaction can be rolled back. Any changes you made to the database within that transaction are rolled back as well.

Data Integrity

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);

    DB::table('posts')->delete();
});

This result is beneficial when your code needs to update several tables. Or to run some complicated tasks that could produce exceptions.

If an error occurs in one statement all previous data changes will not apply to the database, keeping your data consistent with itself.

Concurrency

Another thing database transaction can help us with is “concurrency”.

In a highly concurrent environment (that’s what we want to build using Laravel queues), where the application needs to update resources on the database (such as multiple jobs that want to update the same record in the same table), you can handle this situation retrying to execute the blocked transaction:

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);

    DB::table('posts')->delete();
}, 5); // <-- Number of tries

Here, the second parameter, “5” is the number of times a transaction should reattempt before closing it (rolled back).

And than throwing the “Lock wait timeout” exception.

After becoming familiar with queues and jobs, you may see more exceptions appearing in your log files.

That was my case.

My experience using the Laravel Queue system

I struggled with this issue in a scenario where I needed to update a date-time field in a table called “recently_executed_at“. As described above, many concurrent jobs trying to execute the update on the same record caused the “Lock wait timeout” exception.

The code abelow is not a solution:

DB::transaction(function () use ($task) {
    $task->update(['recently_executed_at' => now()]);
}, 5);

because after attempting the execution five times, it throws an exception.

In my case, it was not essential to have this field update with the last execution time from the previously executed job. I only needed a reasonable recent timestamp to show in the frontend as extra information.

When another job is locking the row for an update, I want to skip this statement in my other jobs.

Reading and dealing with Pessimistic/Optimistic locking sent me off the rails for several days. In such a scenario, the solution was to deal with “Race Condition“. This is when multiple jobs try to update the same database record at the same time.

In the Laravel documentation, you can find the solution in the Cache section as “Atomic Lock“. Unfortunately, it took me almost a week of research to understand that the Atomic Lock in the Cache driver could solve my problem 🤕 🤕 🤕.

Final code

Cache::lock("task-{$task->id}-update")->get(function () use ($task) {
    $task->update(['recently_executed_at' => now()]);
});

It was enough to wrap the update statement in a closure where the LOCK conditions the execution. The code block will be skipped if the lock isn’t available (another job is executing the update).

Note that you need to create the lock name based on the “record id”. This strategy lets you manage the race condition on one record at a time and not on the entire table.

Conclusion

Efficient interaction with your database at scale isn’t easy. The database is a shared resource. Yet, we often forget that each request is not independent of other requests.

If one request is slow, it’s unlikely to affect the other, right?

Your database uses all the processes that run in your application. So, even one poorly designed access can hurt the whole system’s performance. So, be careful when thinking, “it’s okay if this piece of code isn’t optimized”.

One slow database access can strain your database, causing a negative experience for your users.

Laravel application monitoring

If you found this post interesting and want to drastically change your developers’ life for the better, you can give Inspector a try.

Inspector is an easy to use Code Execution Monitoring tool that helps developers to identify bugs and bottlenecks in their application automatically. Before customers do.

It is completely code-driven. You won’t have to install anything at the server level or make complex configurations in your cloud infrastructure.

It works with a lightweight software library that you can install in your application like any other dependency. You can try the Laravel package, it’s free.

Create an account, or visit our website for more information: https://inspector.dev/laravel

Related Posts

cloud costs savings with code execution monitoring

How to save thousands of dollars in cloud costs with Code Execution Monitoring

Hi, I’m Valerio, software engineer, co-founder and CTO at Inspector. In this article I want to show you a monitoring strategy to help you save thousands of dollars a year on cloud costs. The typical scenario where you can get the most payback from this strategy is when your application runs on multiple servers. If

The 5 Best Application Monitoring Tools for 2022

When it comes to application monitoring software, there are some great tools available to make our lives easier. We’ve compiled a list of the very best. No offense intended but the truth is customers are no longer interested in what you have to do to make your product work when they need it. They only

What Are Source Maps and How to Properly Use Them

You are debugging a web app for a client but the minified version of the Javascript and CSS code makes it impossible to understand what statements the browser is actually executing. You could break down the original code line by line in your editor putting some “console.log()” statements here and there, or try debugging it

Join the "Scalable Applications" community

Learn from other developers' experience.
Join the international community of developers to build scalable applications.

Inspector customer feedback
2020 © Inspector S.R.L. - VAT: 09552901218 - Progetto agevolato con la misura Resto al SUD RSUD0000000 - CUP C61B21012300008

How to build scalable applications

Get the e-book about the Inspector scalability journey.