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.