How to solve the Waiting for table metadata lock exception in MySQL

1. Reason

When a DDL statement is executed, the MDL metadata write lock will be automatically added to the table, and the lock will not be released until the transaction is committed. The purpose of this lock is to protect the integrity of the table structure and the consistency of metadata.

2. Processing method

Mode the production environment as shown below: session A opens a transaction and executes DQL

This Session B needs to add full-text indexes to the two fields of the table.

After execution, it was found that the master has been stuck.

Check the related process list and find that it has been waiting to obtain the mdl metadata lock

select * from information_schema.processlist where info like '%alter table `bas_business_type_text` %'

Looking at the table lock situation, you can see that the read and write locks are mutually exclusive

SELECT * FROM sys.schema_table_lock_waits

According to the prompts, execute kill 11

You can see that session B was executed successfully immediately

The above is the detailed content of How to solve the Waiting for table metadata lock exception in MySQL. For more information, please follow other related articles on the PHP Chinese website!