How to check where the metadata lock is blocked in MySQL

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:
  • In-depth analysis of MySQL lock blocking

<<:  VMware Tools installation and configuration tutorial for Ubuntu 18.04

>>:  Summary of react basics

Recommend

CSS sets the list style and creates the navigation menu implementation code

1. Set the list symbol list-style-type: attribute...

Additional instructions for using getters and actions in Vuex

Preliminary Notes 1.Differences between Vue2.x an...

Detailed explanation of MySQL index selection and optimization

Table of contents Index Model B+Tree Index select...

Div adaptive height automatically fills the remaining height

Scenario 1: Html: <div class="outer"...

MySQL 5.5 installation and configuration graphic tutorial

Organize the MySQL 5.5 installation and configura...

Summary of block-level elements, inline elements, and variable elements

Block element p - paragraph pre - format text tabl...

Four ways to compare JavaScript objects

Table of contents Preface Reference Comparison Ma...

Detailed installation tutorial of Docker under CentOS

Docker is divided into CE and EE. The CE version ...

Example code for mixing float and margin in CSS

In my recent studies, I found some layout exercis...

Use of Linux ls command

1. Introduction The ls command is used to display...

Example of how to implement MySQL cascading replication

The so-called cascading replication is that the m...

Introduction to MySQL method of deleting table data with foreign key constraints

When deleting a table or a piece of data in MySQL...

Detailed explanation of Json format

Table of contents A JSON is built on two structur...