MySQL Innodb如何找出阻塞事务源头SQL

MySQL Innodb如何找出阻塞事务源头SQL

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

show engine innodb statusInnotop工具 INNODB_TRX 等系统表

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

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

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 statusG;

使用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 changed mysql> 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=17 ORDER  BY d.trx_startedG;

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

但是不要太天真的认为第二个SQL语句能够获取所有场景下的阻塞源头SQL语句，实际业务场景，会话可能在执行一个存储过程或复杂的业务，有可能它执行完阻塞源头SQL后，继续在执行其它SQL语句，此时，你抓取的是这个连接会话***执行的SQL语句，如下所示，我简单构造了一个例子。就能构造这样的一个场景。这个我曾经写过一篇博客“为什么数据库有时候不能定位阻塞（Blocker）源头的SQL语句”，分析***和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语句。所以还需结合应用程序代码与上下文环境进行整体分析、判断！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。