MySQL死锁排查笔记 mysql死锁

本文是生产环境中产生死锁的一次事故排查笔记，通过阅读本文你可以了解到：

Innodb中常用的锁有哪些？
各种锁之间是兼容还是不兼容的？
Update语句的加锁原理是什么？
下面就跟我一起来还原一下事故现场吧

操作背景

MySQL 8.0.20
开启自动提交事务（autocommit=1）
事务隔离级别可重复度REPEATABLE-READ（RR）
操作的表没有主键和索引

复现步骤注：客户端一也就是事务一，后文不在说明的情况下默认

新建一张空白表t,字段只有两个id和name，没有主键

CREATE TABLE `t` ( `id` int DEFAULT NULL, `name` varchar(45) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

新建客户端一开启新事务一，客户端二开启新事务二

文章图片
客户端一新增一条数据

insert into t values(1,'1'); Query OK, 1 row affected (0.00 sec)
客户端二新增一条数据

insert into t values(2,'2'); Query OK, 1 row affected (0.00 sec)
客户端一执行更新语句,此时会发生阻塞，等待执行，没有输出结果

update t set name ='update 1' where id =1;

文章图片
客户端二执行更新语句，此时返回发现死锁，也就是我们本文要讨论的情况，此时是谁与谁在争夺资源

update t set name='update2' where id =2; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
此时客户端一返回响应执行成功，也就是我们看到的发生了死锁但是一个数据插入成功，另一个数据插入失败的情况

文章图片

到这复现过程就结束了，下面跟着我来一起看下到底是谁和谁在争夺资源？
必备概念锁信息

S（Shared lock）共享锁若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放S锁
X（）排他锁若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他事务不能再对A加作何类型的锁，直到T释放A上的X锁
IS 意向共享锁事务T在对表中数据对象加S锁前，首先需要对该表加IS（或更强的IX）锁
IX 意向排他锁事务T在对表中的数据对象加X锁前，首先需要对该表加IX锁

锁兼容性

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

当对存在的行进行锁的时候(主键)，mysql就只有行锁。
当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁)
行锁

record lock
仅仅锁住索引记录的一行，单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身
gap lock
仅仅锁住一个索引区间（开区间），在索引记录之间的间隙中加锁，或在某一条索引记录之前或之后加锁，并不包括索引记录本身
next-key锁
record lock+gap lock 左开右闭区间

排查死锁用到的语句

查看死锁信息

show engine innodb status
查看当前事务信息不加\G 会以表格的形式输出，加上\G输出更直观好看

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
查看当前事务持有锁信息

SELECT * FROM performance_schema.data_locks\G

查看事务持有锁信息返回字段含义

【MySQL死锁排查笔记】LOCK_STATUS
锁定请求的状态。
该值取决于存储引擎。对于 InnoDB，允许的值为 GRANTED（持有锁）和 WAITING（等待锁）。

原理分析

新建一个空白表

CREATE TABLE `t` (`id` int DEFAULT NULL,`name` varchar(45) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

开启客户端事务一，执行insert 语句并查看锁信息

文章图片
查看此时启动事务持有锁的信息，可以看到此时事务一持有当前表t的意向排它锁

文章图片
事务二执行insert语句并查看当前运行事务持有锁信息，可以看到两个事务都拿到了表t的意向排它锁，所以表级别的意向排它锁互相之间兼容

文章图片
事务一执行完update 语句查看持有锁情况，对持有的锁进行验证
注： update=select ... for update 语句，会对扫描到的所有行设置一个行锁
此时我们用下面这个语句查看持有的锁信息，因为涉及的锁比较多，在使用\G参数查看不如表格直观

select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;

此时返回事务持有的锁信息如下

文章图片

可以看到，除了刚才的两个表级别的意向排它锁之外，又增加了四个行锁的信息，这四个行锁从上往下依次是
id=2的行锁（非gap锁）（为什么可以看到这条记录可以查看快照读与当前读的区别，移步文章末尾MVCC文章链接查看原理）
id=1的行锁（非gap锁）
id=1的行锁，gap锁
等待id=2的行锁
到了这其实就可以看出来了，事务一持有自己的行锁，等待事务二持有的行锁，如果此时事务二需要获取事务一持有的行锁，必定会发生死锁。下面继续看，查看死锁信息
事务二继续执行update 语句，此时提示检测到死锁，回滚当前事务

文章图片
查看死锁信息

show engine innodb status

因为返回信息太多，只截取关键信息展示

------------------------LATEST DETECTED DEADLOCK------------------------2022-01-15 03:48:46 0x7fd520ceb700*** (1) TRANSACTION: # 事务一TRANSACTION 682000, ACTIVE 36 sec fetching rowsmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2MySQL thread id 58, OS thread handle 140553615279872, query id 4198 localhost root updatingupdate t set name ='update 1' where id =3*** (1) HOLDS THE LOCK(S): # 事务一持有的锁信息RECORD LOCKS space id 430 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 682000 lock_mode X locks rec but not gapRecord lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000f23; asc#; ; 1: len 6; hex 0000000a6810; asch ; ; 2: len 7; hex 020000067712ea; ascw; ; 3: len 4; hex 80000003; asc; ; 4: len 8; hex 7570646174652031; asc update 1; ; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: # 事务一等待的锁信息RECORD LOCKS space id 430 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 682000 lock_mode X waitingRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000f24; asc$; ; 1: len 6; hex 0000000a6811; asch ; ; 2: len 7; hex 81000000b10110; asc; ; 3: len 4; hex 80000002; asc; ; 4: len 1; hex 32; asc 2; ; *** (2) TRANSACTION:# 事务二TRANSACTION 682001, ACTIVE 26 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 59, OS thread handle 140553614690048, query id 4200 localhost root updatingupdate t set name='update2' where id =2*** (2) HOLDS THE LOCK(S): # 事务二持有的锁信息RECORD LOCKS space id 430 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 682001 lock_mode X locks rec but not gapRecord lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000f24; asc$; ; 1: len 6; hex 0000000a6811; asch ; ; 2: len 7; hex 81000000b10110; asc; ; 3: len 4; hex 80000002; asc; ; 4: len 1; hex 32; asc 2; ; *** (2) WAITING FOR THIS LOCK TO BE GRANTED:# 事务二等待的锁信息RECORD LOCKS space id 430 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 682001 lock_mode X waitingRecord lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000f23; asc#; ; 1: len 6; hex 0000000a6810; asch ; ; 2: len 7; hex 020000067712ea; ascw; ; 3: len 4; hex 80000003; asc; ; 4: len 8; hex 7570646174652031; asc update 1; ; *** WE ROLL BACK TRANSACTION (2) # 死锁处理结果

从图中信息可以看出，结果分为三部分
(1) TRANSACTION，是第一个事务的信息；
(2) TRANSACTION，是第二个事务的信息；
WE ROLL BACK TRANSACTION (2)，是最终的处理结果，表示回滚了第二个事务（此处为什么选择第二个事务可关注公众号跟进最新文章，后续推出mysql Innodb 回滚策略的选择）

结论
两个insert 语句执行结束，事务一与事务二各持有一个意向拍它锁，意向排它锁与意向排它锁是兼容的
此时事务一执行update 语句，使用查看锁的语句SELECT * FROM performance_schema.data_locks\G 可以看到本身持有的几个行锁，以及间隙锁，还有正在等待的行锁，正在等待的行锁事务二在持有，所以事务一阻塞。
此时事务二执行update语句，同样的使用SELECT * FROM performance_schema.data_locks\G查看锁信息，发现需要等待事务一持有的id=1的行锁，本身持有的行锁记录id=2的，所以此时事务一持有id=1的行锁，等待事务二持有的id=2的行锁，事务二持有id=2的行锁，等待事务一持有的id=1的行锁，所以此时发生死锁。故mysql发生死锁检索回滚，最终死锁的回滚策略选择了回滚事务二，此时事务二回滚成功，释放id=2的行锁，事务一此时获取锁成功，事务成功提交。所以这也就造成了我们刚开始看到的现象，发生了死锁一个事务成功了一个事务失败了。

理解要点

Update 语句加锁的方式是什么？
Innodb中各种类型锁之间兼容性？
当前读快照读？

总结经过上面的一连串的分析，日志查看，两个insert语句执行结束各自持有IX锁，事务一执行更新语句后，创建了自己持有的id=1的行锁与间隙锁，并给事务二创建id=2的行锁，等待事务二id=2的行锁；事务二在更新时需要获取事务一id=1的行锁，此时发生死锁。原因就是事务一持有id=1的行锁，等待id=2的行锁，事务二持有id=2的行锁，等待id=1的行锁，造成互相阻塞
彩蛋 Update 语句与Select for Update 语句加锁信息查看

表中只有一条数据时

文章图片
二条数据时

文章图片
三条数据时

文章图片

可以看到在表没有主键和索引的情况下，事务持有的锁是要把扫描到的行都添加锁
select for update 加锁与update 相同，新开一个客户端，执行查询语句

select * from t for update;

文章图片