MySQL如何查看元数据锁阻塞在哪个语句上

版权声明:本文为博主原创文章,转载请标明出处。让学习成为一种习惯! https://blog.csdn.net/yanzongshuai/article/details/78277890

操作步骤:

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



展开阅读全文

没有更多推荐了,返回首页