概述

很多时候我们在MySQL数据库中会经常出现事务之间阻塞的问题，也就是阻塞lock,oracle的话实际上之前提供的脚本已经可以很直观看出阻塞的问题，那么对于mysql数据库我们应如何快速查找定位问题根源？

这里用实验来进行演示。

一、环境准备

数据库：mysql5.7.24 操作系统:centos7.3

1、数据准备

create database t DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
use t;
create table test_blocking(id int primary key, name varchar(12));
insert into test_blocking select 1, 'hwb' from dual;
insert into test_blocking select 2, 'hwb2' from dual;
insert into test_blocking select 3, 'hwb3' from dual;

2、参数设置

为了实验效果，我们先将参数innodb_lock_wait_timeout设置为100，否则很快就会提示ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

show variables like 'innodb_lock_wait_timeout';
set global innodb_lock_wait_timeout=100 ;

二、被阻塞环境模拟及定位

1、第一个会话

select connection_id() from dual;
set session autocommit=0;
select * from test_blocking where id=1 for update;

2、第二个会话

在第二个连接会话中执行更新脚本

select connection_id() from dual;
update test_blocking set name='kk' where id=1;

3、第三个会话-- show engine innodb status分析

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

 show engine innodb status\G;

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

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

------------
TRANSACTIONS
------------
Trx id counter 7554368 #下一个事务ID
Purge done for trx's n:o < 7554368 undo n:o < 0 state: running but idle
History list length 8
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421427521662128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421427521657568, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421427521656656, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421427521659392, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421427521658480, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 7554359, ACTIVE 88 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 304193, OS thread handle 139951793415936, query id 20504960 localhost root updating 
#MYSQL线程ID为304193， 操作系统线程句柄为139951793415936， 查询ID为20504960，账号为本地的root的UPDATE操作
update test_blocking set name='kk' where id=1 #具体SQL语句
------- TRX HAS BEEN WAITING 88 SEC FOR THIS LOCK TO BE GRANTED: #TRX等待授予锁已经有88秒了
RECORD LOCKS space id 6143 page no 3 n bits 72 index PRIMARY of table `t`.`test_blocking` trx id 7554359 lock_mode X locks rec but not gap waiting
#在space id=6143（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 000000734138; asc sA8;; #该字段为6个字节的事务id，这个id表示最近一次被更新的事务id（需做十进制转换）
 2: len 7; hex a4000140130110; asc @ ;; #该字段为7个字节的回滚指针，用于mvcc
 3: len 3; hex 687762; asc hwb;; #该字段表示的是此记录的第二个字段，长度为5，值为hwb(如果表有多个字段，那么此处后面还有记录)

------------------
---TRANSACTION 7554352, ACTIVE 144 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 304192, OS thread handle 139952113923840, query id 20504801 localhost root
--------

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

后面会介绍其他几个方式来定位问题，感兴趣的朋友可以关注下~