How to Resolve the \'Lock Wait Timeout Exceeded\' Error on a Frozen MySQL Table?

Troubleshooting "Lock Wait Timeout Exceeded" Error on a Frozen MySQL Table

A common issue in MySQL involves encountering the "Lock wait timeout exceeded; try restarting transaction" error while attempting to modify or drop an index on a table. This error typically indicates a stuck transaction within the table, preventing operations from being completed.

Diagnosing the Issue

Suspect a stuck transaction when a table responds exceptionally slowly to queries or when dropping an index fails with the aforementioned error message.

Identifying the Stuck Thread

To identify the thread responsible for the deadlock, execute the following command in the MySQL command line interface or phpMyAdmin:

SHOW PROCESSLIST;

This command will display a list of threads, including their IDs and execution times.

Terminating the Stuck Thread

Once you have identified the thread that is taking excessive time, you can terminate it using either the KILL command in the command line interface or the "Kill" button in phpMyAdmin.

For example, to kill thread ID 115, use:

KILL 115;

This action will terminate the connection for that specific thread.

Resolving the Issue

After killing the stuck thread, you should be able to:

• Successfully drop the index
• Execute queries with normal response times

Additional Tips

• Always include the WHERE clause in your UPDATE statements to avoid unintentionally modifying all rows.
• Monitor MySQL performance and adjust settings as needed to prevent such issues in the future.

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