In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
Editor to share with you why the emergence of MySQL Waiting for table metadata lock, I believe that most people do not understand, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!
When MySQL performs DDL operations such as alter table, there is sometimes a wait scenario for Waiting for table metadata lock. Moreover, once the operation of alter table TableA is stuck in the state of Waiting for table metadata lock, any subsequent operations on TableA (including reads) cannot be carried out, because they will also enter the lock waiting queue of Waiting for table metadata lock during the Opening tables phase. If such a lock waiting queue appears in the core table of the production environment, it will have disastrous consequences.
The reason for Waiting for table metadata lock in alter table is actually very simple. It is generally caused by the following simple scenarios:
Scenario 1: long things run, blocking DDL, and then blocking all subsequent operations with the same table
Through show processlist, you can see that there are operations (including reads) in progress on the TableA, and when the alter table statement cannot acquire the metadata exclusive lock, it will wait.
This is the most basic situation, and this does not conflict with online ddl in mysql 5.6. In general, during the operation of alter table (see figure below), the after create step will acquire the metadata exclusive lock. When it comes to the altering table process (usually the most time-consuming step), the table can be read and written normally. This is the performance of online ddl, which does not block writes during the entire alter table process as before. (of course, not all types of alter operations can be online, see the official manual: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)
How to deal with it: kill drops the session where DDL is located.
Scenario 2: uncommitted transactions, blocking DDL, and then blocking all subsequent operations with the same table
You don't see any operations on TableA through show processlist, but there are actually uncommitted transactions, which you can see in information_schema.innodb_trx. The lock on TableA is not released until the transaction is completed, and alter table also cannot acquire the exclusive lock of metadata.
How to deal with it: through select * from information_schema.innodb_trx\ G, find the sid of the uncommitted thing, and then kill it to be rolled back.
Scenario 3:
You don't see any operations on TableA through show processlist, and there are no transactions in progress in information_schema.innodb_trx. This is probably because in an explicit transaction, a failed operation was performed on TableA (such as querying a field that does not exist), and the transaction did not start, but the lock acquired by the failed statement was still valid and not released. Failed statements can be found in the performance_schema.events_statements_ current table.
This is explained in the official manual as follows:
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
That is, except for syntax errors, locks acquired by other error statements are still not released until the transaction is committed or rolled back. Because the failed statement is written to the binary log and the locks protect log consistency but the reason for this behavior is hard to understand because incorrect statements are not logged to the binary log at all.
How to deal with it: find its sid through performance_schema.events_statements_current, and kill drop the session. You can also kill the session where DDL is located.
All in all, alter table's statement is dangerous (in fact, its danger is caused by uncommitted things or long transactions). Before operating, it is best to make sure that there are no ongoing operations on the table to be operated on, no uncommitted transactions, and no error statements in explicit transactions. If there is an alter table maintenance task to run when unsupervised, it is best to set the timeout through lock_wait_timeout to avoid long metedata lock waiting.
The above is all the content of the article "Why Waiting for table metadata lock appeared in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!
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.
Views: 291
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.