The dreaded “lock wait timeout exceeded” error is a common problem for Magento store owners. As it affects user experience, it cannot be ignored. Luckily, there are simple ways to get rid of it. Learn how the error occurs and what you should do about it.

If you are a Magento developer, you almost certainly have come across some problems with database locks. Online merchants find themselves helpless when their customers can’t place an order or use the product search. It’s all because of the re-indexing script that sometimes might make it impossible to save data in the table.

When does the error occur?

From the perspective of the user, the error occurs at the final stage of the shopping cart process – as they click the “create order” button. The website takes a long time to load… only to display an alert that says: “There was an error processing your order. Please contact us or try again later.”

Not only does the situation greatly impede the overall user experience of the site, but also prevents the order from being saved in the database. Once it occurs, an email that contains information about the error is sent to the email address of the site’s owner (provided it was configured accordingly beforehand). The information is as follows:

The customer may encounter a similar problem also at the time of using the built-in product search. Again, the website will take ages to load and eventually return a critical error.

How to reproduce the error?

To reproduce the error, it is required to configure the environment accordingly. When MySQL is unable to save data in the table due to the lock-error occurrence, it waits for a certain period of time for the block to disappear. The time is defined by two environment variables: innodb_lock_wait_timeout and lock_wait_timeout. For the sake of this tutorial, let’s lower the value down to 20 seconds. Then, let’s write a simple script that will block a given database table for a specific period of time. We’re now ready to place a test order.

If everything went as planned, we should be now able to launch the script like this:

If we place the test order within less than 40 seconds, the error information will show up.

How to fix it?

Lock in the checkout

When the lock error occurs during the checkout process, stack-trace makes it easy to find out what causes the exception to be thrown. It’s the observer of the CatalogInventory module, which at times proceeds to re-index all products previously added to the cart.

It is not uncommon that the PHP process triggered by a user that has just completed the checkout process needs access to the very same tables that are simultaneously being used by the re-indexing process. As we all know by now, if it really does happen, the entire process of saving an order in the database is cut short. Since the module that does re-indexing is totally independent of the Sales module, we can easily find the error, save the product IDs and move re-indexing to a separate process in the background.

Lock on the search results page

If you stumbled upon some article and modified the configuration of the database accordingly and yet the problem persists, it is recommended to try reproducing the error by using the script above. It is possible that the stats of the query typed in the product search are being updated in the catalogsearch_query table, causing the lock exception to be thrown.

There are a few ways to go about it. One may simply find the exception and ignore the update every time the error occurs. It shouldn’t affect the stats significantly. However, it can badly affect the execution time of the query and slow down the database.

This may in turn increase the time it takes for search results to load. Remember that each time the process is attempting to access a blocked database table, it waits for a certain period of time (defined by environment variables) for the table to become available.. As a result, the load time of the page is increased.

An even better solution is to move the process of saving search query stats to RAM and periodically (say, once an hour) update them. If you want to find out what’s blocking the access to a table, you can also review the code of the indexing process. It might be an effective way of dealing with the problem. After all, the search mostly uses two tables: catalogsearch_query and catalogsearch_result. And only the latter needs to be updated during the search re-indexing process.

To sum it all up

The “lock wait timeout exceeded” error is most often caused by the re-indexing process that blocks access to the tables that other processes try to save data to. Due to the nature of eCommerce sites, there are relatively few moments when the site needs to modify data in the database. It usually comes down to the checkout process and the product search.

When it comes to the checkout, most operations involve sales_ tables. They are hardly ever modified by other processes, therefore, the risk of locks is marginal. The product re-indexing process taking place at the time of placing an order is but the only viable situation. As for the product search, the threat has to do with saving data about queries and the results of the queries in catalogsearch_result.