Updating websites can often show  MySQL error 1205 lock wait timeout when it fails to insert data in MySQL tables.

Fortunately, when MySQL server detects a problem, it will be logged into the error log file.

Also, this error occurs when lock_wait_timeout expires or when an existing process prevents a new process being executed on the same table.

That’s why, at Bobcares, we often get requests from our customers to fix MySQL errors as part of our Server Management Services.

Today, we’ll see how our Support Engineers fix MySQL error 1205 lock wait timeout.

Why MySQL error 1205 lock wait timeout?

MySQL is a database management system that is used by all CMS to store and collect website data. Also, it allow multiple users to manage and create databases.

Moreover, to retrieve data from the database, popular content management systems like WordPress, Joomla, Magento, etc performs SQL queries to generate content dynamically.

However, while managing the website database, customers may get an error SQLSTATE[HY000]: General error: 1205 Lock wait timeout due to an incomplete MySQL query.

This lock_wait_timeout variable specifies the timeout in seconds for attempts to acquire metadata locks of the database.

Sometimes, this error could occur if you run a bunch of custom scripts and kill the scripts before the database closes the connection.

How we fixed MySQL error 1205 lock wait timeout

From our experience in managing MySQL servers, we’ve seen many customers facing this error. Here, Let’s see top reasons for 1205 error and how our Support Engineers solved the problem.

1. Inactive process

An inactive process can cause problems while writing to MySQL tables.

Recently, one of our customers came up with an error ERROR 1205 (HY000): Lock wait timeout exceeded;try restarting transaction. He was getting this error while trying to update a row on a specific table of a database.

Then we took the following steps to solve the issue.

1. Initially, we logged into the server as a root user.

2. We login to MySQL prompt from CLI and ran the command

SHOW PROCESSLIST;

It showed the MySQL processes running on the server as

+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
| 545566 | db_user| 91.xx.yy.65:21532 | db_name| Sleep | 3800 | | NULL | 0 | 0 | 0 |
| 547967 | db_user| 91.xx.yy.65:27488 | db_name| Sleep | 3757 | | NULL | 0 | 0 | 0 |
| 549360 | db_user| 91.xx.yy.65:32670 | db_name| Sleep | 3731 | | NULL | 0 | 0 | 0 |
| 549450 | db_user| 91.xx.yy.65:47424 | db_name | Sleep |2639 | | NULL | 0 | 0 | 0 |
| 549463 | db_user| 91.xx.yy.65:56029 | db_name| Sleep | 2591 | | NULL | 0 | 0 | 0 |

From the output, we found that the process with id 545566 was in sleep status for a time of 3800 seconds. This exceeded the lock timeout settings and caused the problem.

In addition to this, when an existing process is running, it can prevent a new process from executing on the same tables.

Therefore, we analyzed the process status and found that it was perfectly fine to end the process. So, we solved this error by killing the processes in sleep status using the command.

kill -9 <pid>

To avoid unwanted website behavior, it is always recommended to have a detailed check of the process by a Database expert.

2. Insufficient innodb_lock_wait_timeout

Similarly, many customers have heavy traffic websites may need to run different processes simultaneously. They might experience this MySQL error 1205 lock wait timeout due to insufficient innodb_lock_wait_timeout. That means the query is taking too long to perform.

For instance, one of the customers had a website with 8000 visitors a day. Also, they added 6000 articles to the website without optimizing server resources. As a result, when they tried to save articles, the website showed a blank page. The error was intermittent as some time later, it showed the original website.

On checking, we found that the problem was related to the SQL query on the MySQL server. When too many people edited the same article at the same time, it created a lot of calls to one MySQL database table. Then query that waited too long was rolled back. Hence, the customer got a blank page on the original website.

So, our Support Engineers fixed this error by increasing the value of the innodb_lock_wait_timeout  in the MySQL configuration file at /etc/my.cnf.

Then, it allowed the SQL query to wait longer for other transactions to complete.

Nonetheless, tweaking MySQL configuration parameters always have a key role in the smooth working of any website.

[Getting MySQL error 1205 lock wait timeout? We’ll fix it for you.]

Conclusion

In short, MySQL error 1205 lock wait timeout occurs when lock_wait_timeout expires or when an existing process prevents a new process being executed on the same table. Today, we saw the reasons how our Support Engineers fixed related errors.