MySQL how to see which statement the metadata lock is blocked on

Operation Steps:

1, Session 1 execution:

Start transaction;

Select *from T1;

2, Session 2 after the completion of the 1th step execution:

drop table T1;

At this point the DROP statement for session 2 is blocked. So how to analyze view metadata lock?

Method:

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 I/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) can see the thread of the currently running transaction is Trx_mysql_thread_id:8, then 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 the SELECT statement, and if you execute show engine InnoDB status, you can see that the transaction is in a sleep state, which means that the transaction statement is executed but not committed.

Execute kill 8, and the thread of the transaction will be killed. Or check your business's SQL statement to see if there are uncommitted 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

MySQL how to see which statement the metadata lock is blocked on