Dealing with MySQL Lock Timeouts: Bail faster

When using MySQL and InnoDB you will inevitably run into lock timeouts sometime, somewhere. 

We have recently started seeing this with some of our Shopware 6 customers in their storefronts or worker queues, so I was reminded to go back to 2017 in our codebase when we put a fix in place.

In our case, this happened on tables that were constantly written to from many different sources in the code base. But it only happened once every few hours or days in worker or cron jobs, and usually fixed itself by retrying the task, or even the function call itself at the code level.

In Tideways you’ll get a notification about the lock timeout as an exception, and you’ll see that it happens sporadically. In the case represented in the following screenshot this has happened a few times in the last days, always around 21:00:

One downside to MySQL lock timeouts is the default wait time: 50 seconds! This is a time where your web server, workers or cron jobs are idle. In our case when a backlog happened it quickly spiraled out of control and all workers suddenly had to wait.

Dealing with this timeout can make the difference from your application failing from the backpressure of requests or tasks that do not get processed fast enough and ties into decreasing timeouts across the board, a topic that we have blogged and podcasted about before.

If your code reaches the wait timeout failure and stops execution completely, a 1-second wait should be fine. Our write heavy worker tasks therefore change the timeout at the beginning of each task:

$this->connection->exec('SET innodb_lock_wait_timeout=1');

Or, you can change this setting directly in the MySQL server so that it doesn’t have to be a concern of your application.

If the occasional lock timeouts are acceptable and your application can recover from them, then this is a much simpler solution than re-architecturing your data write access to avoid the locks altogether.