两个小工具，MySQL死锁分析，新技能又Get！！！

数据库死锁，是最难调试与追踪的。

场景如下：

同一个表，事务内先插入一条记录，再更新这条记录， 并发时 会死锁。

并且能够复现。

可以通过什么 工具 模拟并发事务，查看信息，解决问题呢？这是今天要分享的内容。

一、前置准备

set session transaction isolation level repeatable read;

set session autocommit=0;

create table t (

id int(20) primary key AUTO_INCREMENT,

cell varchar(20) unique

)engine=innodb;

start transaction;

insert into t(cell) values(11111111111);

insert into t(cell) values(22222222222);

insert into t(cell) values(33333333333);

commit;

说明：

（1）案发时， 事务隔离级别RR ；

（2）多终端实验，需要 关闭事务自动提交 ；

（3）建表，设置PK与unique，初始化数据；

二、并发事务模拟

Session A：

start transaction;

insert into t(cell)values(44444444444);  [1]

Session B：

start transaction;

insert into t(cell) values(55555555555); [2]

update t set cell=123 where cell=44444444444; [3]   

update t set cell=456 where cell=55555555555; [4]

开启两个终端模拟并发事务：

（1）红色 SQL 为事务A；

（2）黑色SQL为事务B；

（3）[1][2][3][4]为执行时序；

三、实验现象

insert into t(cell)values(44444444444);  [1]

事务A插入数据，最先执行

结果：插入成功

insert into t(cell) values(55555555555); [2]

事务B插入数据，第二执行

结果：插入成果

update t set cell=123 where cell=44444444444; [3]

事务A修改[1]中插入的数据，第三执行

结果：阻塞 ，等待执行结果

画外音：修改一条自己插入的数据，在等待什么呢？

update t set cell=456 where cell=55555555555; [4]

事务B修改[2]中插入的数据，最后执行

结果：

（1） 事务B死锁，事务B被回滚 ；

（2） 事务A中，[3]语句阻塞结束，执行成功 ；

画外音：说明事务A中阻塞的语句，确实在等事务B中的某个锁。

四、结果分析

两个事务， 各自修改自己插入的数据 ，却产生了 死锁 ，确实诡异。

上述实验现象的 两个核心问题 是：

（1） 语句[3]阻塞，在等待什么锁 ？

（2） 语句[4]死锁 ，此时事务A和事务B一定是彼此占住一把锁， 请求彼此的锁，这些锁又是什么呢 ？

工具一：

show engine innodb status;

画外音：前文《 超赞，InnoDB调试死锁的方法！ 》就详细分享过，InnoDB死锁的分析实践。

执行之后，显示的内容如下（放大仔细看）：

信息很多，别急，楼主娓娓道来。

第一部分，关键词是：

（1） Transaction 1 ，事务3998；

（2）在执行

update t set cell=123 where cell=44444444444;

（3）正在等待锁释放 (waiting for this lock to be granted) ，记录锁 ( record locks ) ，主键索引上 ( index primary ) ，互斥锁 ( lock_mode X ) ，物理记录 ( physical record ) ，asc 55555555555;

画外音：英文比较差没事，抓关键词。

画外音，InnoDB存储引擎，聚集索引与非聚集索引的实现方式，决定了锁会加在聚集索引上，详见文章：

《 1分钟了解MyISAM与InnoDB的索引差异 》。

第二部分，关键词是：

（1） Transaction 2 ，事务3999；

（2）正在执行

update t set cell=456 where cell=55555555555;

（3）持有锁 ( holds the lock ) ，记录锁 ( record locks ) ，主键索引上 ( index primary ) ，互斥锁 ( lock_mode X ) ，物理记录 ( physical record ) ，asc 55555555555;

（4）正在等待锁释放 ( waiting for this lock to be granted ) ，记录锁 ( record locks ) ，主键索引上 ( index primary ) ，互斥锁 ( lock_mode X ) ，物理记录 ( physical record ) ，asc 11111111111;

（5）事务2回滚 ( we roll back transaction 2 ) ；

通过show engine innodb status; 能够看到很多事务与锁之间的信息，对分析问题十分有帮助，这些信息，能够解释一些问题，但 仍有两个疑 惑：

（1） 事务1为啥想拿55555555555的锁？

画外音：这正是，事务1被阻塞的原因。

（2） 事务2为啥想拿11111111111的锁？ 死锁的发生，说明事务1此时真占着11111111111的锁，这又是为什么呢？

画外音：第一个事务占111抢555，第二个事务占555抢111，循环嵌套，才会死锁。

工具二：

explain

为了进一步寻找原因，可以通过explain看下导致死锁语句的执行计划。

explain update t set cell=456 where cell=55555555555;

select_type ：SIMPLE

这是一个简单类型的SQL语句，不含子查询或者UNION。

type ：index

访问类型，即找到所需数据使用的遍历方式，潜在的方式有：

（1）ALL（Full Table Scan）：全表扫描；

（2） index：走索引的全表扫描；

（3）range：命中where子句的范围索引扫描；

（4）ref/eq_ref：非唯一索引/唯一索引单值扫描；

（5）const/system：常量扫描；

（6）NULL：不用访问表；

上述扫描方式，ALL最慢，逐步变快，NULL最快。

怀疑点1：明明cell字段有uniq索引，为何要进行走PK索引的全表扫描呢？

possible_keys ：NULL

可能在哪个索引找到记录。

key ：PRIMARY

实际使用索引。

画外音：使用PK进行的全表扫描。

ref ：NULL

哪些列，或者常量用于查找索引上的值。

怀疑点2：where条件中的查询条件55555555555，本来应该作为在索引上被检索的值呀？

rows ：5

找到所需记录，预估需要读取的行数。

怀疑点3：明明修改的是5，为何初始化的1，2，3，以及第一个事务插入的4，以及第二个事务插入的5，都要被读取呢？不应该全表扫描呀。

通过explain，基本已经可以判断：

update t set cell=456 where cell=55555555555;

并没有和我们预想一样，走cell索引进行查询，而是走了PK索引 进行了全表扫描 。

再仔细一看：

create table t (

id int(20) primary key AUTO_INCREMENT,

cell varchar(20) unique

)engine=innodb;

建表 的时候cell定义的是 字符串类型 。

而 更新 的时候，

update t set cell=456 where cell=55555555555;

使用的是 整数类型 。

类型转换，会导致全表扫描，出现锁升级，锁住全部记录 。

加上引号，再次通过explain验证一下：

explain update t set cell= '456 ' where cell= '55555555555 ';

果然印证了猜想：

（1）type：range，变为了走索引的字符串比对，范围扫描；

（2）possible_keys：cell，通过cell索引找到了记录；

（3）key：cell，实际使用cell索引；

（4）ref：const，使用了常量' 555'进行比对；

（5）rows：1，预估读取行数是1；

这下全部可以解释了 。

总结

就本例而言： 需要注意字符串与整数之间的强制类型转换 ，有时候少一个引号，就会使得行锁升级为表锁。

死锁是 MySQL 中非常难调试的问题，常见的思路与方法有：

（1）通过多终端模拟并发事务，复现死锁；

（2）通过 show engine innodb status; 可以查看事务与锁的信息；

（3）通过 explain 可以查看执行计划；

思路 比结论更重要，希望大家有收获。

架构师之路-分享技术思路

相关推荐：

《 MySQL并发控制与锁+调试MySQL死锁d方法 》

《 写一个cache，要掌握哪些技术点 》

《 6条 shell 小技巧 | 1分钟系列 》

《 MyISAM与InnoDB的索引差异 | 1分钟系列 》

《 超赞，InnoDB调试死锁的方法！ 》

末了，有很多同学通过经验，分析出了是强制类型转换的原因，赞！

画外音：但本文“思路”仍值得转，不是吗？