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 

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