How to Resolve \'Lock Wait Timeout Exceeded\' for a Stuck MySQL Table?

Resolving "Lock Wait Timeout Exceeded" for a Stuck MySQL Table

A recent issue arose where executing an SQL update without a WHERE clause resulted in an extended table lock. This resulted in a "Lock wait timeout exceeded; try restarting transaction" error while attempting to drop an affected index.

To address this situation, it is recommended to identify and terminate any stuck transactions. This can be achieved by examining the running threads using the SHOW PROCESSLIST command in the MySQL command line interface.

Finding and Killing Stuck Threads

1. Connect to the MySQL database using the command line interface.

2. Run the following command:

   SHOW PROCESSLIST;

   Copy after login
3. This will display a list of currently running threads with their IDs and execution times.
4. Identify the threads that have been executing for an excessive amount of time.

5. To terminate a stuck thread, execute the KILL command followed by its ID, as shown below:

   KILL <thread ID>;

   Copy after login

Example

For example, to terminate thread with ID 115, use the following command:

KILL 115;

Once the stuck threads have been terminated, the table should be unlocked and the index drop operation can proceed normally.

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