How to Identify the Specific Query Causing a 'Lock wait timeout exceeded' Error in MySQL?

How to identify the specific query that held the lock when Lock wait timeout exceeded error occurs in MySQL

Introduction:

The Lock wait timeout exceeded error in MySQL indicates that a query encountered a lock wait timeout while trying to access a locked resource. This can occur in scenarios where multiple queries are accessing the same data and one query blocks the execution of another due to a conflicting lock. Determining the specific query causing the lock wait can assist in resolving the issue and preventing future occurrences.

Identifying the Culprit Query:

In the error log, the word "transaction" is a key indicator that the query attempting to access the database is modifying one or more InnoDB tables. Knowing the query that triggered the error, it is possible to identify the tables involved.

Using the command SHOW ENGINE INNODB STATUSG, you can obtain information about the affected tables and locking status. This command displays detailed information, including:

• RW-shared spins: Specifies the number of spins waiting for a shared read lock.
• RW-excl spins: Indicates the number of spins waiting for an exclusive lock.
• Mutex spin waits: Number of spins waiting on mutexes.

Example Output:

The following output from SHOW ENGINE INNODB STATUSG shows that a specific query is waiting for an exclusive lock on the attachment table:

RW-excl spins: 787046, OS waits: 39353
...
Latest foreign key error (a full list is printed in the InnoDB error log when a foreign key error occurs):
...
Transaction:
TRANSACTION 0 606162814, ACTIVE 0 sec, process no 29956, OS thread id 1223895360 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
...
Foreign key constraint fails for table `backoffice`.`attachment`:

,
  CONSTRAINT `attachment_ibfk_2` FOREIGN KEY (`file_id`) REFERENCES `file` (`file_id`)
Trying to delete or update in parent table, in index `PRIMARY` tuple:
...
But in child table `backoffice`.`attachment`, in index `PRIMARY`, there is a record:

Resolution:

To resolve the issue, you can increase the value of the innodb_lock_wait_timeout parameter, which specifies the maximum time a transaction will wait for a lock before reporting an error. By increasing this timeout, you can provide more buffer time for transactions to complete and reduce the likelihood of lock timeout errors.

Setting innodb_lock_wait_timeout:

• To set the timeout permanently, add the following line to /etc/my.cnf and restart MySQL:

  [mysqld]
  innodb_lock_wait_timeout=120

• To set the timeout for the current session, execute the following query:

  SET GLOBAL innodb_lock_wait_timeout = 120;

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