How does MySQL view metadata lock blocking? mysql metadata

How does MySQL view metadata lock blocking? mysql metadata

How does MySQL view metadata lock blocking?

Procedure:

1. session 1 execution:

   start transaction;   select *from t1;

2. session 2 is executed after Step 2 is executed:

  drop table t1;

The drop Statement of session 2 is blocked. So how can we analyze and view metadata locks?

Method:

1) execute show processlist;. You can see that the drop statement is 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 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) We can see that the thread of the currently running transaction is trx_mysql_thread_id: 8. 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) The thread executes the select statement. If the show engine innodb status statement is executed, the transaction is in sleep state. That is to say, the transaction statement has been executed but has not been committed.

Run kill 8 to kill the thread of the transaction. Or check the SQL statement of the business and whether there are 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 

If you have any questions, please leave a message or go to the community on this site for discussion. Thank you for reading this article. Thank you for your support!