How to check where the metadata lock is blocked in MySQL

How to check where the metadata lock is blocked in MySQL

Steps:

1. Session 1 execution:

   start transaction;
   select *from t1;

2. Session 2 is executed after step 1:

  drop table t1;

At this time, the drop statement of session 2 is blocked. So how do you analyze and view metadata locks?

method:

1) Execute show processlist;, and 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) 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. If you execute show engine innodb status; you can see that the transaction is in the sleep state, which means that the transaction statement has been executed but not committed.

Execute kill 8 to kill the thread of the transaction. Or check the SQL statements 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

If you have any questions, please leave a message or come to the community to discuss. Thank you for reading and I hope it can help you. Thank you for your support of this site!

You may also be interested in: