转载：《两个小工具，MySQL死锁分析，新技能又Get！！！》

死锁的场景

表 T，包含主键 ID 自增，唯一索引字段 p。一个事务内，insert 后根据字段 p 来 update 这条记录（where p=插入时的值），当出现并发操作时 update 处会出现死锁，把 update 条件改为 ID 时，没有出现死锁。

死锁现象还原

准备测试数据

  1. CREATE TABLE `t` (
  2.   `id` int(20) NOT NULL AUTO_INCREMENT,
  3.   `cell` varchar(20) DEFAULT NULL,
  4.   PRIMARY KEY (`id`),
  5.   UNIQUE KEY `cell` (`cell`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4;
  8. insert into t(cell) values(11111111111);
  9. insert into t(cell) values(22222222222);
  10. insert into t(cell) values(33333333333);

说明：

  • 案发时，事务隔离级别 RR；
  • 多终端实验，需要关闭事务自动提交；
  • 建表，设置 PK 与 unique，初始化数据；

并发事务模拟

  1. -- 事务A执行
  2. start transaction;
  4. insert into t(cell) values (44444444444); ------------------------------- 1
  6. -- 事务B执行
  7. start transaction;
  9. insert into t(cell) values (55555555555); ------------------------------- 2
  11. -- 事务A执行
  12. update t set cell=123 where cell=44444444444; --------------------------- 3
  14. -- 事务B执行
  15. update t set cell=456 where cell=55555555555; --------------------------- 4

开启两个客户端模拟并发事务，1、2、3、4 是执行顺序。

执行现象

image.png

1.insert into t(cell) values (44444444444);
事务A插入数据，最先执行
结果：插入成功

2.insert into t(cell) values (55555555555);
事务B插入数据，第二执行
结果：插入成果

3.update t set cell=123 where cell=44444444444;
事务A修改1中插入的数据，第三执行
结果：阻塞，等待执行结果

image.png

4.update t set cell=456 where cell=55555555555;
事务B修改2中插入的数据，最后执行
结果：
4.1.事务B死锁，事务B被回滚；

image.png

4.2.事务A中，语句3阻塞结束，执行成功；

image.png

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

结果分析

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

正常情况下根据唯一索引定位且查到数据，只会使用到记录锁（Record locks），锁住一条数据，各自更新各自的数据不会造成死锁。

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

  1. 语句3阻塞，在等待什么锁？
  2. 语句4死锁，此时事务A和事务B一定是彼此占住一把锁，请求彼此的锁，这些锁又是什么呢？

分析死锁现象

工具一

在客户端中执行 show engine innodb status; 命令，获取如下显示内容：

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2019-08-17 21:48:53 0x700001f02000
  5. *** (1) TRANSACTION:
  6. TRANSACTION 73549, ACTIVE 14 sec fetching rows
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 4 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
  9. MySQL thread id 29, OS thread handle 123145334546432, query id 204 localhost 127.0.0.1 root updating
  10. update t set cell=123 where cell=44444444444
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 176 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 73549 lock_mode X waiting
  13. Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  14.  0: len 4; hex 80000007; asc     ;;
  15.  1: len 6; hex 000000011f52; asc      R;;
  16.  2: len 7; hex b90000012d0110; asc     -  ;;
  17.  3: len 11; hex 3535353535353535353535; asc 55555555555;;
  19. *** (2) TRANSACTION:
  20. TRANSACTION 73554, ACTIVE 10 sec starting index read
  21. mysql tables in use 1, locked 1
  22. 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
  23. MySQL thread id 28, OS thread handle 123145334824960, query id 205 localhost 127.0.0.1 root updating
  24. update t set cell=456 where cell=55555555555
  25. *** (2) HOLDS THE LOCK(S):
  26. RECORD LOCKS space id 176 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 73554 lock_mode X locks rec but not gap
  27. Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  28.  0: len 4; hex 80000007; asc     ;;
  29.  1: len 6; hex 000000011f52; asc      R;;
  30.  2: len 7; hex b90000012d0110; asc     -  ;;
  31.  3: len 11; hex 3535353535353535353535; asc 55555555555;;
  33. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  34. RECORD LOCKS space id 176 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 73554 lock_mode X waiting
  35. Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  36.  0: len 4; hex 80000001; asc     ;;
  37.  1: len 6; hex 000000011f42; asc      B;;
  38.  2: len 7; hex af000001230110; asc     #  ;;
  39.  3: len 11; hex 3131313131313131313131; asc 11111111111;;
  41.  *** WE ROLL BACK TRANSACTION (2)

第一部分的关键词是：

  1. Transaction 1，事务 73549；
  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;

第二部分，关键词是：

  1. Transaction 2，事务 73554；
  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. Transaction 2 回滚（we roll back transaction 2）；

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

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

第一个事务占11111111111抢55555555555，第二个事务占55555555555抢11111111111，循环嵌套，才会死锁。

工具二

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

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

image.png

type：index

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

  • ALL（Full Table Scan）：全表扫描；
  • index：走索引的全表扫描；
  • range：命中where子句的范围索引扫描；
  • ref/eq_ref：非唯一索引/唯一索引单值扫描；
  • const/system：常量扫描；
  • 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 索引进行了全表扫描。
对比表结构和更新语句：

image.png

image.png

建表的时候 cell 定义的是字符串类型，更新语句使用的是整数类型。

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

加上引号，再次通过 explain 验证一下，explain update t set cell='456' where cell= '55555555555';
image.png

果然印证了猜想：

  • type：range，变为了走索引的字符串比对，范围扫描；
  • possible_keys：cell，通过 cell 索引找到了记录；
  • key：cell，实际使用 cell 索引；
  • ref：const，使用了常量 ‘55555555555’ 进行比对；
  • rows：1，预估读取行数是1；

总结

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

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

  1. 通过多终端模拟并发事务，复现死锁；
  2. 通过 show engine innodb status; 可以查看事务与锁的信息；
  3. 通过 explain 可以查看执行计划；

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/xobfld 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。