MySQL如何查看中繼資料鎖阻塞在哪個語句上

標籤：mysql 中繼資料

操作步驟：

1、session 1 執行：

      start transaction;

      select *from t1;

2、session 2 在第1步執行完後執行：

     drop table t1;

此時session 2的drop語句被阻塞。那麼怎麼分析查看中繼資料鎖呢？

方法：

1）執行show processlist;，可以看到drop語句在等待中繼資料鎖

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）可以看到當前正在啟動並執行事務的線程是trx_mysql_thread_id:8，那麼這個線程在幹什麼呢？

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）可以看到這個線程執行的是select語句，如果執行show engine innodb status;可以看到該事務處於sleep狀態，也就是說這個事務語句執行完了，但是沒有提交。

執行kill 8，將該事務的線程殺掉就可以了。或者檢查業務的SQL語句，檢查下是否有未提交的SQL語句。

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如何查看中繼資料鎖阻塞在哪個語句上