在MySQL数据库中出现了阻塞问题,如何快速查找定位问题根源?在实验开始前,我们先梳理一下有什么工具或命令查看MySQL的阻塞,另外,我们也要一一对比其优劣,因为有些命令可能在实际环境下可能并不适用。

1:  show engine innodb status

2:  Innotop工具

3:  INNODB_TRX 等系统表

下面我们理论联系实际,通过实验来测试总结一下这个问题。首先构造测试环境,数据库测试环境为( 5.7.21 MySQL Community Server 和5.6.20-enterprise-commercial,这两个测试环境我都测试验证过)

mysql> use MyDB;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> create table test_blocking(id int primary key, name varchar(12));Query OK, 0 rows affected (0.05 sec) mysql> insert into test_blocking    -> select 1, 'kerry' from dual;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0 mysql> insert into test_blocking    -> select 2, 'jimmy' from dual;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0 mysql> insert into test_blocking    -> select 3, 'kkk' from dual;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0

准备好测试环境数据后,那么我们接下来开始实验,为了实验效果,我们先将参数innodb_lock_wait_timeout设置为100。

mysql> show variables like 'innodb_lock_wait_timeout';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 50    |+--------------------------+-------+1 row in set (0.00 sec) mysql> set global innodb_lock_wait_timeout=100 ;Query OK, 0 rows affected (0.00 sec)  mysql> select connection_id() from dual;+-----------------+| connection_id() |+-----------------+|               8 |+-----------------+1 row in set (0.00 sec) mysql> set session autocommit=0;Query OK, 0 rows affected (0.00 sec) mysql> select * from test_blocking where id=1 for update;+----+-------+| id | name  |+----+-------+|  1 | kerry |+----+-------+1 row in set (0.00 sec)

然后在第二个连接会话中执行更新脚本,构造被阻塞的案例

mysql> select connection_id() from dual;+-----------------+| connection_id() |+-----------------+|               9 |+-----------------+1 row in set (0.00 sec) mysql> update test_blocking set name='kk' where id=1;

在第三个连接会话执行下面命令,查看TRANSACTIONS相关信息:

mysql> show engine innodb status\G;

使用show engine innodb status命令后,可以查看其输出的TRANSACTIONS部分信息,如上截图所示,找到类似TRX HAS BEEN WATING ...部分的信息,

通过那部分信息,我们可以看到update test_blocking set name='kk' where id=1这个SQL语句被阻塞了14秒,一直在等待获取X Lock。

mysql> select * from information_schema.INNODB_SYS_DATAFILES where space=337;+-------+--------------------------+| SPACE | PATH                     |+-------+--------------------------+|   337 | ./MyDB/test_blocking.ibd |+-------+--------------------------+1 row in set (0.00 sec) mysql>

但是这种方式也有一些弊端,例如生产环境很复杂,尤其是有大量事务的情况下。诸多信息根本无法清晰判断知道谁阻塞了谁;其次一点也不直观; 另外,这个也无法定位blocker 的SQL语句。这种方式只能作为辅助分析用途,通过查看取锁的详细信息,帮助进一步诊断问题。

2: Innotop工具

如下所示,Innotop工具很多情况下也不能定位到阻塞的语句(Blocking Query), 也仅仅能获取一些锁相关信息

3:通过查询information_schema数据库下与事务相关的几个系统表

还是构造之前的测试案例,在第一个会话中使用SELECT FOR UPDATE锁定其中一行记录

mysql> use MyDB;Database changedmysql>  set session autocommit=0;Query OK, 0 rows affected (0.00 sec)mysql> select connection_id() from dual;+-----------------+| connection_id() |+-----------------+|              17 |+-----------------+1 row in set (0.00 sec) mysql> select * from test_blocking where id=1 for update;+----+-------+| id | name  |+----+-------+|  1 | kerry |+----+-------+1 row in set (0.00 sec) mysql>

然后在第二个连接会话中执行更新脚本,构造被阻塞的案例

mysql> use MyDB;Database changedmysql> select connection_id() from dual;+-----------------+| connection_id() |+-----------------+|              19 |+-----------------+1 row in set (0.00 sec) mysql> update test_blocking set name='kk' where id=1;

此时阻我们在第三个连接会话查找谁被阻塞了

SELECT b.trx_mysql_thread_id             AS 'blocked_thread_id'       ,b.trx_query                      AS 'blocked_sql_text'       ,c.trx_mysql_thread_id             AS 'blocker_thread_id'      ,c.trx_query                       AS 'blocker_sql_text'      ,( Unix_timestamp() - Unix_timestamp(c.trx_started) )                               AS 'blocked_time' FROM   information_schema.innodb_lock_waits a     INNER JOIN information_schema.innodb_trx b          ON a.requesting_trx_id = b.trx_id     INNER JOIN information_schema.innodb_trx c          ON a.blocking_trx_id = c.trx_id WHERE  ( Unix_timestamp() - Unix_timestamp(c.trx_started) ) > 4;  SELECT a.sql_text,        c.id,        d.trx_started FROM   performance_schema.events_statements_current a        join performance_schema.threads b          ON a.thread_id = b.thread_id        join information_schema.processlist c          ON b.processlist_id = c.id        join information_schema.innodb_trx d          ON c.id = d.trx_mysql_thread_id where c.id=17ORDER  BY d.trx_started\G;

如下截图所示,第一个SQL语句能够查到线程19 被线程 17阻塞了, 被阻塞的SQL语句为“update test_blocking set name='kk' where id=1;”, 能够查到被阻塞了多长时间,但是无法查到源头SQL语句。此时就需要第二个SQL语句登场,找到源头语句。

但是不要太天真的认为第二个SQL语句能够获取所有场景下的阻塞源头SQL语句,实际业务场景,会话可能在执行一个存储过程或复杂的业务,有可能它执行完阻塞源头SQL后,继续在执行其它SQL语句,此时,你抓取的是这个连接会话最后执行的SQL语句,如下所示,我简单构造了一个例子。就能构造这样的一个场景。这个我曾经写过一篇博客“为什么数据库有时候不能定位阻塞(Blocker)源头的SQL语句”(),分析SQL Server和ORACLE 定位查找阻塞源头SQL语句,现在看来真是大道同源,殊途同归。

mysql> select * from test_blocking where id=1 for update;+----+-------+| id | name  |+----+-------+|  1 | kerry |+----+-------+1 row in set (0.00 sec) mysql> delete from student where stu_id=1001;Query OK, 1 row affected (0.00 sec) mysql>

总结: 最简单、方便的还是上面两个SQL查询定位blocker的SQL语句,但是需要注意:有时候它也查不到真正阻塞的源头SQL语句。所以还需结合应用程序代码与上下文环境进行整体分析、判断!

来源:博客园 –潇湘隐者