在上一篇文章《MySQL 5.7中如何定位DDL被阻塞的问题》中，对于DDL被阻塞问题的定位，我们主要是基于MySQL 5.7新引入的performance_schema.metadata_locks表。提出的定位方法，颇有种”锦上添花”的意味，而且，也只适用于MySQL 5.7开始的版本。

但在实际生产中，MySQL 5.6还是占绝不多数。虽然MySQL 8.0都已经GA了，但鉴于数据库的特殊性，在对待升级的这个事情上，相当一部分人还是秉持着一种“不主动”的态度。

既然MySQL 5.6用者众多，有没有一种方法，来解决MySQL 5.6的这个痛点呢？

 

还是之前的测试Demo

会话1开启了事务并执行了三个操作，但未提交，此时，会话2执行了alter table操作，被阻塞。

  1. session1> begin;
  2. Query OK, 0 rows affected (0.00 sec)
  4. session1> delete from slowtech.t1 where id=2;
  5. Query OK, 1 row affected (0.00 sec)
  7. session1> select * from slowtech.t1;
  8. +------+------+
  9. | id   | name |
  10. +------+------+
  11. |    1 | a    |
  12. +------+------+
  13. row in set (0.00 sec)
  15. session1> update slowtech.t1 set name='c' where id=1;
  16. Query OK, 1 row affected (0.00 sec)
  17. Rows matched: 1  Changed: 1  Warnings: 0
  19. session2> alter table slowtech.t1 add c1 int; ##被阻塞
  21. session3> show processlist;
  22. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
  23. | Id | User | Host      | db   | Command | Time | State                           | Info                               |
  24. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
  25. |  2 | root | localhost | NULL | Sleep   |   51 |                                 | NULL                               |
  26. |  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
  27. |  4 | root | localhost | NULL | Query   |    9 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
  28. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
  29. rows in set (0.00 sec)

 

其实，导致DDL阻塞的操作，无非两类： 

1. 慢查询  

2. 表上有事务未提交

其中，第一类比较好定位，通过show processlist即能发现。而第二类基本没法定位，因为未提交事务的连接在show processlist中的输出同空闲连接一样。

如下面Id为2的连接，虽然Command显示为“Sleep”，其实是事务未提交。

  1. mysql> show processlist;
  2. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
  3. | Id | User | Host      | db   | Command | Time | State                           | Info                               |
  4. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
  5. |  2 | root | localhost | NULL | Sleep   |   77 |                                 | NULL                               |
  6. |  3 | root | localhost | NULL | Query   |    0 | starting                        | show processlist                   |
  7. |  4 | root | localhost | NULL | Query   |   44 | Waiting for table metadata lock | alter table slowtech.t1 add c1 int |
  8. +----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
  9. 3 rows in set (0.00 sec)

 

所以，网上有kill空闲（Command为Sleep）连接的说法，其实也不无道理，但这样做就太简单粗暴了，难免会误杀。

其实，既然是事务，在information_schema. innodb_trx中肯定会有记录，如会话1中的事务，在表中的记录如下，

  1. mysql> select * from information_schema.innodb_trx\G
  2. *************************** 1. row ***************************
  3.                     trx_id: 1050390
  4.                  trx_state: RUNNING
  5.                trx_started: 2018-07-17 08:55:32
  6.      trx_requested_lock_id: NULL
  7.           trx_wait_started: NULL
  8.                 trx_weight: 4
  9.        trx_mysql_thread_id: 2
  10.                  trx_query: NULL
  11.        trx_operation_state: NULL
  12.          trx_tables_in_use: 0
  13.          trx_tables_locked: 1
  14.           trx_lock_structs: 2
  15.      trx_lock_memory_bytes: 1136
  16.            trx_rows_locked: 3
  17.          trx_rows_modified: 2
  18.    trx_concurrency_tickets: 0
  19.        trx_isolation_level: REPEATABLE READ
  20.          trx_unique_checks: 1
  21.     trx_foreign_key_checks: 1
  22. trx_last_foreign_key_error: NULL
  23.  trx_adaptive_hash_latched: 0
  24.  trx_adaptive_hash_timeout: 0
  25.           trx_is_read_only: 0
  26. trx_autocommit_non_locking: 0
  27. 1 row in set (0.00 sec)

 

其中trx_mysql_thread_id是线程id，结合performance_schema.threads，可以知道当前哪些连接上存在着活跃事务，这样就进一步缩小了可被kill的线程范围。

 但从影响程度上，和kill所有Command为Sleep的连接没太大区别，毕竟，kill真正的空闲连接对业务的影响不大。

 此时，依然可以借助performance_schema. events_statements_history表。

 在上篇MySQL 5.7的分析中，我们是首先知道引发阻塞的线程ID，然后利用events_statements_history表，查看该线程的相关SQL。

 而在MySQL 5.6中，我们并不知道引发阻塞的线程ID，但是，我们可以反其道而行之，利用穷举法，首先统计出所有线程在当前事务执行过的所有SQL，然后再判断这些SQL中是否包含目标表。

 

具体SQL如下，

  1. SELECT
  2.     processlist_id,
  3.     sql_text 
  4. FROM
  5.     (
  6.     SELECT
  7.         c.processlist_id,
  8.         substring_index( sql_text, "transaction_begin;",-1 ) sql_text 
  9.     FROM
  10.         information_schema.innodb_trx a,
  11.         (
  12.         SELECT
  13.             thread_id,
  14.             group_concat( CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text 
  15.         FROM
  16.             performance_schema.events_statements_history 
  17.         GROUP BY
  18.             thread_id 
  19.         ) b,
  20.         performance_schema.threads c 
  21.     WHERE
  22.         a.trx_mysql_thread_id = c.processlist_id 
  23.         AND b.thread_id = c.thread_id 
  24.     ) t 
  25. WHERE
  26.     sql_text LIKE '%t1%';
  28. +----------------+---------------------------------------------------------------------------------------------------------+
  29. | processlist_id | sql_text                                                                                                |
  30. +----------------+---------------------------------------------------------------------------------------------------------+
  31. |              2 | delete from slowtech.t1 where id=2;select * from slowtech.t1;update slowtech.t1 set name='c' where id=1 |
  32. +----------------+---------------------------------------------------------------------------------------------------------+
  33. 1 row in set (0.01 sec)

从输出来看，确实也达到了预期效果。

 

需要注意的是，在MySQL5.6中，events_statements_history默认是没有开启的。

  1. mysql> SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';
  2. +--------------------------------+---------+
  3. | NAME                           | ENABLED |
  4. +--------------------------------+---------+
  5. | events_statements_current      | YES     |
  6. | events_statements_history      | NO      |
  7. | events_statements_history_long | NO      |
  8. | statements_digest              | YES     |
  9. +--------------------------------+---------+
  10. 4 rows in set (0.00 sec)

 

版权声明：本文为ivictor原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/ivictor/p/9460192.html