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

• 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;

  
  
  文章图片
  
  

MVCC之快照读与当前读 参考

• Innodb事务和锁的信息

https://dev.mysql.com/doc/ref...

• Innodb事务表信息返回字段含义

https://dev.mysql.com/doc/ref...

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

https://dev.mysql.com/doc/ref...
https://dev.mysql.com/doc/ref...

• Innodb 锁信息

https://dev.mysql.com/doc/ref...

• Update 加锁规则

https://dev.mysql.com/doc/ref...
https://dev.mysql.com/doc/ref...
扫码关注，回复【面试】获取面试宝典

文章图片

Java进阶扫码关注 不迷路微信:zuiyu17970

推荐阅读

• 

  原神雪山再勘探任务怎么做 原神雪山再勘探任务攻略
• 

  QQ飞车手游1周年雄狮获得方法 绝版专属新载具获得技巧
• 

  超级p57的减肥效果 超级p57怎样服用
• 

  退休工资计算例子 退休工资怎么算
• 

  snmp抓包分析
• 

  电商供货如何去找客户，做电商怎么找供货商
• 

  suv的分类标准 suv级别划分标准
• 

  店铺售假扣24分删除全部宝贝封店,多久才能重开？
• 

  如果你有888点卷你会买赵云的哪个皮肤？
• 

  音爵士音响电子厂|防水专业音响的具体介绍及防水处理方法——声拓电子音爵士
• 

  网咖如何计费使用 网咖如何计费，网咖如何计费用
• 

  洗洁精加牙膏可以做起泡胶吗
• 

  热饭热菜可以直接放冰箱吗
• 

  曾有你的森林翡翠贝儿 曾经有你的森林
• 

  爱普生L1300清零-如何使用epson l1300清零软件清零步骤
• 

  土豆泥天鹅泡芙的做法
• 

  道高望重名词解释 道高望重什么意思
• 

  微信收款语音怎么设置 手机微信收款语音播报怎么设置
• 

  如何在云端服务器上实现分屏功能？ 云端服务器怎么用分屏
• 

  美国的自由女神很有名,为什么美国航空母舰不以自由女神命名？