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

时间:2021-10-15 23:15:43

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