How to Troubleshoot MySQL's 'Lock wait timeout exceeded' Error?

How to debug Lock wait timeout exceeded on MySQL?

When a query attempts to access a database and encounters a lock, it may result in the Lock wait timeout exceeded error in the MySQL error logs. While you may know which query is trying to access the database, determining which query has the lock at that exact moment is not immediately clear.

The key to finding the culprit is the mention of "transaction" in the error statement. This indicates that the query is trying to modify at least one row in one or more InnoDB tables.

Since you have identified the query, the next step is to run the SHOW ENGINE INNODB STATUSG command to view the affected tables. This command will provide you with detailed information about the locks and mutexes involved in the query.

For example, consider the following output from the SHOW ENGINE INNODB STATUSG command:

mysql> show engine innodb status\G
...
Per second averages calculated from the last 4 seconds
----------
SEMAPHORES
----------
...
14 lock struct(s), heap size 3024, 8 row lock(s), undo log entries 1
...
---------------
LATEST FOREIGN KEY ERROR
---------------
...
DELETE FROM file WHERE file_id in ('6dbafa39-7f00-0001-51f2-412a450be5cc' )
...

In this output, we can see that there are 8 row locks, indicating that a write operation is being performed on 8 rows in the file table. The DELETE statement provided in the output confirms that a deletion operation is being attempted.

To resolve this issue, you can increase the innodb_lock_wait_timeout value, which defines the maximum amount of time a transaction can wait for a lock before timing out. The default value for this setting is 50 seconds. You can adjust this value to a higher number, such as 120 seconds, to give the transaction more time to complete.

To set the innodb_lock_wait_timeout value permanently, add the following line to your /etc/my.cnf file:

[mysqld]
innodb_lock_wait_timeout=120

Restart MySQL to apply the changes. Alternatively, you can temporarily modify the innodb_lock_wait_timeout value for the current session using the following command:

SET GLOBAL innodb_lock_wait_timeout = 120;

This will allow you to run your query without encountering the Lock wait timeout exceeded error.

The above is the detailed content of How to Troubleshoot MySQL's 'Lock wait timeout exceeded' Error?. For more information, please follow other related articles on the PHP Chinese website!