How to solve the table lock problem when mysql deletes the table

Method to solve the problem of table locking when MySQL deletes a table: First check whether there are transactions being executed. If there are uncommitted transactions, kill the transaction or wait for the transaction to be submitted; then re-execute the table deletion operation.

Problem description:

(Recommended tutorial: mysql video tutorial)

1. drop table, causing the table to be locked, and it keeps waiting.

2. SHOW FULL PROCESSLIST; can find the waiting process.

3. After killing the corresponding lock, the query will not be affected.

4. Delete the table again and a locked table appears.

Problem Solution:

Use the following statement to query whether there are transactions being executed. If there are uncommitted transactions, you can consider killing the transaction or waiting for the transaction to be submitted.

SELECT trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx;

Query a transaction that is being executed, and delete the table after killing it. The operation is normal.

Related recommendations: mysql tutorial

The above is the detailed content of How to solve the table lock problem when mysql deletes the table. For more information, please follow other related articles on the PHP Chinese website!