In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
The following mainly brings you how to view metadata lock blocking statements through MySQL. I hope that how to view metadata lock blocking statements through MySQL can bring you practical use. This is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.
Procedure:
1. Session 1 executes:
Start transaction
Select * from T1
2. Session 2 is executed after the execution of step 1:
Drop table t1
The drop statement for session 2 is blocked at this point. So how do you analyze and view metadata locks?
Methods:
1) execute show processlist;, to see the drop statement waiting for the metadata lock
Mysql > show processlist +-+ -+-+ | Id | User | Host | db | Command | Time | State | Info | + -+- -+ | 5 | system user | | NULL | Connect | 1050234 | Waiting for master to send event | NULL | | 6 | system user | | NULL | Connect | 983193 | Slave has read all relay log Waiting for the slave waiting for the slave O thread to update it | NULL | | 8 | root | localhost | yzs | Sleep | 93 | NULL | | 9 | root | localhost | yzs | Query | 3 | Waiting for table metadata lock | | drop table T1 | | 10 | root | localhost | NULL | Query | 0 | init | show processlist | +-- +-| -+ 5 rows in set (0.00 sec)
2) you can see that the thread of the currently running transaction is trx_mysql_thread_id:8, so what is this thread doing?
Mysql > select * from information_schema.innodb_trx\ G * * 1. Row * * trx_id: 17683 trx_state: RUNNING trx_started: 2017-10-18 05: 32:46 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 8 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx _ lock_memory_bytes: 320 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only : 0 trx_autocommit_non_locking: 0 1 row in set (0.03 sec)
3) you can see that this thread is executing a select statement, and if you execute show engine innodb status;, you can see that the transaction is in the sleep state, that is, the transaction statement is finished, but it is not committed.
Execute kill 8 and kill the thread of the transaction. Or check the SQL statement of the business to see if there are any unsubmitted SQL statements.
Mysql > select * from performance_schema.events_statements_current\ G * * 1. Row * * THREAD_ID: 27 EVENT_ID: 15 END_EVENT_ID: 15 EVENT _ NAME: statement/sql/select SOURCE: mysqld.cc:962 TIMER_START: 1050544992900922000 TIMER_END: 1050544993740836000 TIMER_WAIT: 839914000 LOCK_TIME: 196000000 SQL_TEXT: select * from T1 DIGEST: 1aa32397c8ec37230aed78ef16126571 DIGEST_TEXT: SELECT * FROM `t1` CURRENT_ SCHEMA: yzs OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: NULL MYSQL_ERRNO: 0 RETURNED_SQLSTATE: NULL MESSAGE_TEXT: NULL ERRORS: 0 WARNINGS: 0 ROWS_AFFECTED: 0 ROWS_SENT: 10 ROWS_EXAMINED: 10 CREATED_TMP_DISK_TABLES: 0 CREATED_TMP_TABLES: 0 SELECT_FULL_JOIN: 0 SELECT_FULL_RANGE_JOIN: 0 SELECT_RANGE: 0 SELECT_RANGE_CHECK: 0 SELECT_SCAN: 1 SORT_MERGE_PASSES: 0 SORT_RANGE: 0 SORT_ROWS: 0 SORT_SCAN: 0 NO_INDEX_USED: 1 NO_GOOD_INDEX_USED: 0 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL
For the above about how to view metadata lock blocking statements through MySQL, you do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.
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: 0
*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.