欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-16 20:08:22
mysql如何查看元数据锁阻塞在哪里 操作步骤: 1、session 1 执行: start transaction; select *...

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 

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