Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to do "waiting for table metadata lock in MySQL adding index process". In daily operation, I believe many people have doubts about how to do "waiting for table metadata lock" in MySQL adding index process. Xiaobian consulted all kinds of data and sorted out simple and easy operation methods. I hope to answer your doubts about "waiting for table metadata lock in MySQL adding index process". Help! Next, please follow the small series to learn together!

Take some time today to sort out the faults that occurred some time ago.

Indexing tables related to a production warehouse

alter table user_address add index idx_create_time(create_time);

The master library is added normally. A high delay is observed in the slave library. Show processlist View the status of each thread and find a large number of Waiting for table metadata locks.

Since this is the first time I have encountered it, I went online to look up metadata lock (MDL) related information.

select * from information_schema.innodb_trx\G

See an access to the user_address table that never ends

Based on experience, this is an abnormal SQL, so kill this thread.

Then show processlist:

Waiting for table metadata lock disappears completely, and show slave status \G delay gradually decreases and disappears.

Information consulted:

1) Versions after MySQL 5.6 support online DDL. Creating an index itself will not affect the addition, deletion and query of a table. However, since the MDL (metadata lock) cannot be obtained, the session created by indexing from the library will always be in the Waiting for table metadata lock state, which will affect subsequent accesses to this table.

2) Late operation alter table, you can avoid this situation by checking the transaction commit situation of information_schema.innodb_trx in advance.

At this point, the study of "waiting for table metadata lock during MySQL index addition" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.