How to Resolve \'Lock Wait Timeout Exceeded\' Error on MySQL Tables?

Troubleshooting "Lock Wait Timeout Exceeded" Error on MySQL Table

Encountering the "Lock wait timeout exceeded" error when attempting database operations can indicate a stuck transaction in the target table. This issue often arises due to incomplete queries, such as missing the WHERE clause, which can inadvertently update multiple rows or repeatedly update the same column.

To resolve this issue in an InnoDB table, where transactions are implicitly started, we can use the following steps:

1. Check Running Threads:

   Start by examining the running threads using the SHOW PROCESSLIST; command. This will display a list of threads, including their IDs and execution time.

2. Identify the Stuck Thread:

   Look for threads that have been running for an unusually long time or are in a "locked" state. These threads may be responsible for the stuck transaction.

3. Kill the Stuck Thread:

   Once the problem thread is identified, it can be terminated using the KILL command followed by its ID. For example:

   KILL 115;

   Executing this command will terminate the connection associated with the stuck thread, effectively releasing any locks it held.

After killing the stuck thread, the table should become operational again. Simple queries should execute normally, and index manipulation operations, such as dropping the index, should succeed.

The above is the detailed content of How to Resolve 'Lock Wait Timeout Exceeded' Error on MySQL Tables?. For more information, please follow other related articles on the PHP Chinese website!