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