|
|
51CTO旗下网站
|
|
移动端

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

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

作者:潇湘隐者来源:数据库开发|2018-06-01 16:24

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

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

  1. show engine innodb status

  2. Innotop工具

  3. INNODB_TRX 等系统表

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

  1. mysql> use MyDB; 
  2. Reading table information for completion of table and column names 
  3. You can turn off this feature to get a quicker startup with -A 
  4.    
  5. Database changed 
  6. mysql> create table test_blocking(id int primary keyname varchar(12)); 
  7. Query OK, 0 rows affected (0.05 sec) 
  8.    
  9. mysql> insert into test_blocking 
  10.     -> select 1, 'kerry' from dual; 
  11. Query OK, 1 row affected (0.00 sec) 
  12. Records: 1  Duplicates: 0  Warnings: 0 
  13.    
  14. mysql> insert into test_blocking 
  15.     -> select 2, 'jimmy' from dual; 
  16. Query OK, 1 row affected (0.00 sec) 
  17. Records: 1  Duplicates: 0  Warnings: 0 
  18.    
  19. mysql> insert into test_blocking 
  20.     -> select 3, 'kkk' from dual; 
  21. Query OK, 1 row affected (0.00 sec) 
  22. Records: 1  Duplicates: 0  Warnings: 0 

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

  1. mysql> show variables like 'innodb_lock_wait_timeout'
  2. +--------------------------+-------+ 
  3. | Variable_name            | Value | 
  4. +--------------------------+-------+ 
  5. | innodb_lock_wait_timeout | 50    | 
  6. +--------------------------+-------+ 
  7. 1 row in set (0.00 sec) 
  8.    
  9. mysql> set global innodb_lock_wait_timeout=100 ; 
  10. Query OK, 0 rows affected (0.00 sec) 
  11.    
  12.    
  13. mysql> select connection_id() from dual; 
  14. +-----------------+ 
  15. | connection_id() | 
  16. +-----------------+ 
  17. |               8 | 
  18. +-----------------+ 
  19. 1 row in set (0.00 sec) 
  20.    
  21. mysql> set session autocommit=0; 
  22. Query OK, 0 rows affected (0.00 sec) 
  23.    
  24. mysql> select * from test_blocking where id=1 for update
  25. +----+-------+ 
  26. | id | name  | 
  27. +----+-------+ 
  28. |  1 | kerry | 
  29. +----+-------+ 
  30. 1 row in set (0.00 sec) 

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

  1. mysql> select connection_id() from dual; 
  2. +-----------------+ 
  3. | connection_id() | 
  4. +-----------------+ 
  5. |               9 | 
  6. +-----------------+ 
  7. 1 row in set (0.00 sec) 
  8.    
  9. mysql> update test_blocking set name='kk' where id=1; 

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

  1. 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。

  1. TRANSACTIONS 
  2.   
  3. ------------ 
  4.   
  5. Trx id counter 148281  #下一个事务ID 
  6.   
  7. Purge done for trx's n:o < 148273 undo n:o < 0 state: running but idle 
  8.   
  9. History list length 552 
  10.   
  11. LIST OF TRANSACTIONS FOR EACH SESSION: 
  12.   
  13. ---TRANSACTION 0, not started 
  14.   
  15. MySQL thread id 15, OS thread handle 0x4cc64940, query id 261 localhost root cleaning up 
  16.   
  17. ---TRANSACTION 0, not started 
  18.   
  19. MySQL thread id 14, OS thread handle 0x4cbe2940, query id 278 localhost root init 
  20.   
  21. show engine innodb status 
  22.   
  23. ---TRANSACTION 148280, ACTIVE 24 sec 
  24.   
  25. 2 lock struct(s), heap size 360, 1 row lock(s) 
  26.   
  27. MySQL thread id 8, OS thread handle 0x4cba1940, query id 276 localhost root cleaning up 
  28.   
  29. ---TRANSACTION 148279, ACTIVE 313 sec starting index read 
  30.   
  31. mysql tables in use 1, locked 1 
  32.   
  33. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) 
  34.   
  35. MySQL thread id 9, OS thread handle 0x4cc23940, query id 277 localhost root updating  #线程ID为9, 操作系统线程句柄为0x4cc23940, 查询ID为277,账号为root的UPDATE操作 
  36.   
  37. update test_blocking set name='kk' where id=1  #具体SQL语句 
  38.   
  39. ------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED:  #TRX等待授予锁已经有14秒了 
  40.   
  41. RECORD LOCKS space id 337 page no 3 n bits 72 index `PRIMARYof table `MyDB`.`test_blocking` trx id 148279 lock_mode X locks rec but not gap waiting 
  42.   
  43. #在space id=337(test_blocking表的表空间),page no=3的页上,表test_blocking上的主键索引在等待X锁 
  44.   
  45. Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 
  46.   
  47.  0: len 4; hex 80000001; asc     ;;            #第一个字段是主键,制度按长为4,值为1 
  48.   
  49.  1: len 6; hex 000000024322; asc     C";;      #该字段为6个字节的事务id,这个id表示最近一次被更新的事务id(对应十进制为148258) 
  50.   
  51.  2: len 7; hex 9a000001f20110; asc        ;;   #该字段为7个字节的回滚指针,用于mvcc 
  52.   
  53.  3: len 5; hex 6b65727279; asc kerry;;         #该字段表示的是此记录的第二个字段,长度为5,值为kerry(如果表有多个字段,那么此处后面还有记录) 

  1. mysql> select * from information_schema.INNODB_SYS_DATAFILES where space=337; 
  2. +-------+--------------------------+ 
  3. SPACE | PATH                     | 
  4. +-------+--------------------------+ 
  5. |   337 | ./MyDB/test_blocking.ibd | 
  6. +-------+--------------------------+ 
  7. 1 row in set (0.00 sec) 
  8.    
  9. mysql> 

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

2: Innotop工具

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

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

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

  1. mysql> use MyDB; 
  2. Database changed 
  3. mysql>  set session autocommit=0; 
  4. Query OK, 0 rows affected (0.00 sec) 
  5. mysql> select connection_id() from dual; 
  6. +-----------------+ 
  7. | connection_id() | 
  8. +-----------------+ 
  9. |              17 | 
  10. +-----------------+ 
  11. 1 row in set (0.00 sec) 
  12.    
  13. mysql> select * from test_blocking where id=1 for update
  14. +----+-------+ 
  15. | id | name  | 
  16. +----+-------+ 
  17. |  1 | kerry | 
  18. +----+-------+ 
  19. 1 row in set (0.00 sec) 
  20.    
  21. mysql> 

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

  1. mysql> use MyDB; 
  2. Database changed 
  3. mysql> select connection_id() from dual; 
  4. +-----------------+ 
  5. | connection_id() | 
  6. +-----------------+ 
  7. |              19 | 
  8. +-----------------+ 
  9. 1 row in set (0.00 sec) 
  10.    
  11. mysql> update test_blocking set name='kk' where id=1; 

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

  1. SELECT b.trx_mysql_thread_id             AS 'blocked_thread_id' 
  2.       ,b.trx_query                      AS 'blocked_sql_text' 
  3.       ,c.trx_mysql_thread_id             AS 'blocker_thread_id' 
  4.       ,c.trx_query                       AS 'blocker_sql_text' 
  5.       ,( Unix_timestamp() - Unix_timestamp(c.trx_started) )  
  6.                               AS 'blocked_time' 
  7. FROM   information_schema.innodb_lock_waits a  
  8.     INNER JOIN information_schema.innodb_trx b  
  9.          ON a.requesting_trx_id = b.trx_id  
  10.     INNER JOIN information_schema.innodb_trx c  
  11.          ON a.blocking_trx_id = c.trx_id  
  12. WHERE  ( Unix_timestamp() - Unix_timestamp(c.trx_started) ) > 4;  
  13.    
  14. SELECT a.sql_text,  
  15.        c.id,  
  16.        d.trx_started  
  17. FROM   performance_schema.events_statements_current a  
  18.        join performance_schema.threads b  
  19.          ON a.thread_id = b.thread_id  
  20.        join information_schema.processlist c  
  21.          ON b.processlist_id = c.id  
  22.        join information_schema.innodb_trx d  
  23.          ON c.id = d.trx_mysql_thread_id  
  24. where c.id=17 
  25. 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语句”,分析SQL Server和ORACLE 定位查找阻塞源头SQL语句,现在看来真是大道同源,殊途同归。

  1. http://www.cnblogs.com/kerrycode/p/5821413.html 

  1. mysql> select * from test_blocking where id=1 for update
  2. +----+-------+ 
  3. | id | name  | 
  4. +----+-------+ 
  5. |  1 | kerry | 
  6. +----+-------+ 
  7. 1 row in set (0.00 sec) 
  8.    
  9. mysql> delete from student where stu_id=1001; 
  10. Query OK, 1 row affected (0.00 sec) 
  11.    
  12. mysql> 
 

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

【编辑推荐】

  1. 选择云计算数据库的正确方法
  2. 如何从命令行管理MySQL数据库和用户
  3. 解救 DBA——数据库分库分表思路及案例分析
  4. 区块链代表的数据库和传统数据库有何区别
  5. 高性能数据库集群-分库分表
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +更多

Java程序员面试宝典

本书是程序员面试宝典系列中的一册,也是上一本《程序员面试宝典》的姊妹书。本书对程序设计面试中Java常见的题型和常用解答技巧进行了介绍...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊