MySQL如何查看元数据锁阻塞在哪里

MySQL如何查看元数据锁阻塞在哪里

操作步骤：

1、session 1 执行：

?

1 2	start transaction ; select * from t1;

2、session 2 在第1步执行完后执行：  

?

1	drop table t1;

此时session 2的drop语句被阻塞。那么怎么分析查看元数据锁呢？

方法：

1）执行show processlist;，可以看到drop语句在等待元数据锁

?

1 2 3 4 5 6 7 8 9 10 11

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，那么这个线程在干什么呢？

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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语句。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

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

如有疑问请留言或者到本站社区交流讨论，感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

原文链接：http://blog.csdn.net/yanzongshuai/article/details/78277890