原文出处： 潇湘隐者
在MySQL数据库中呈现了阻塞问题，如何快速查找定位问题来源？在尝试开始前，我们先梳理一下有什么东西或呼吁查察MySQL的阻塞，别的，我们也要一一比拟其黑白，因为有些呼吁大概在实际情况下大概并不合用。
- show engine innodb status
- Innotop东西
- 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 names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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。
TRANSACTIONS
------------
Trx id counter 148281 #下一个事务ID
Purge done for trx's n:o < 148273 undo n:o < 0 state: running but idle
History list length 552
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 15, OS thread handle 0x4cc64940, query id 261 localhost root cleaning up
---TRANSACTION 0, not started
MySQL thread id 14, OS thread handle 0x4cbe2940, query id 278 localhost root init
show engine innodb status
---TRANSACTION 148280, ACTIVE 24 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 8, OS thread handle 0x4cba1940, query id 276 localhost root cleaning up
---TRANSACTION 148279, ACTIVE 313 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 9, OS thread handle 0x4cc23940, query id 277 localhost root updating #线程ID为9， 操纵系统线程句柄为0x4cc23940，昆山软件开发， 查询ID为277，账号为root的UPDATE操纵
update test_blocking set name='kk' where id=1 #详细SQL语句
------- TRX HAS BEEN WAITING 14 SEC FOR THIS LOCK TO BE GRANTED: #TRX期待授予锁已经有14秒了
RECORD LOCKS space id 337 page no 3 n bits 72 index `PRIMARY` of table `MyDB`.`test_blocking` trx id 148279 lock_mode X locks rec but not gap waiting
#在space id=337（test_blocking表的表空间），page no=3的页上，表test_blocking上的主键索引在期待X锁
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;; #第一个字段是主键，制度按长为4，值为1
1: len 6; hex 000000024322; asc C";; #该字段为6个字节的事务id，这个id暗示最近一次被更新的事务id（对应十进制为148258）
2: len 7; hex 9a000001f20110; asc ;; #该字段为7个字节的回滚指针，用于mvcc
3: len 5; hex 6b65727279; asc kerry;; #该字段暗示的是此记录的第二个字段，长度为5，值为kerry（假如表有多个字段，那么此处后头尚有记录）
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> 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 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_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语句。所以还需团结应用措施代码与上下文情况举办整体阐明、判定！
首页
微信交流
关注我们