MySQL死锁排查笔记

最新推荐文章于 2024-03-03 14:49:06 发布

置顶醉鱼！

最新推荐文章于 2024-03-03 14:49:06 发布

阅读量555

点赞数 1

分类专栏： MySql 文章标签： mysql java 数据库

原文链接：https://mp.weixin.qq.com/s?__biz=MzIwNzYzODIxMw==&mid=2247484685&idx=1&sn=0477a223cbbda13067b7ee57a3450c1e&chksm=970e1ea7a07997b16c889d7dc78df5498863d5bea877fc9040fca78f8c13e5af060030097309&token=1481043325&lang=zh_CN#rd

版权

MySql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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

Innodb中常用的锁有哪些？
各种锁之间是兼容还是不兼容的？
Update语句的加锁原理是什么？

下面就跟我一起来还原一下事故现场吧

操作背景

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

复现步骤

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

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

CREATETABLE`t` (
  `id`intDEFAULTNULL,
  `name`varchar(45) DEFAULTNULL
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

image-20220114230006907

客户端一新增一条数据

insertinto t values(1,'1');
 Query OK, 1 row affected (0.00 sec)

客户端二新增一条数据

insertinto t values(2,'2');
Query OK, 1 row affected (0.00 sec)

客户端一执行更新语句,此时会发生阻塞，等待执行，没有输出结果
```
update t setname ='update 1'whereid =1;
```
image-20220114231135821
客户端二执行更新语句，此时返回发现死锁，也就是我们本文要讨论的情况，此时是谁与谁在争夺资源
```
update t setname='update2'whereid =2;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
```
此时客户端一返回响应执行成功，也就是我们看到的发生了死锁但是一个数据插入成功，另一个数据插入失败的情况

image-20220114231028033

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

必备概念

锁信息

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 左开右闭区间

排查死锁用到的语句

查看死锁信息
```
showengineinnodbstatus
```
查看当前事务信息 不加\G 会以表格的形式输出，加上\G输出更直观好看
```
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
```

查看当前事务持有锁信息

SELECT * FROM performance_schema.data_locks\G

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

LOCK_STATUS

锁定请求的状态。

该值取决于存储引擎。对于 InnoDB，允许的值为 GRANTED（持有锁）和 WAITING（等待锁）。

原理分析

新建一个空白表

CREATETABLE`t` (
  `id`intDEFAULTNULL,
  `name`varchar(45) DEFAULTNULL
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

image-20220114233419181
查看此时启动事务持有锁的信息，可以看到此时事务一持有当前表t的意向排它锁

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

image-20220114233814172
事务一执行完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;
```
此时返回事务持有的锁信息如下

image-20220114234455554

可以看到，除了刚才的两个表级别的意向排它锁之外，又增加了四个行锁的信息，这四个行锁从上往下依次是

id=2的行锁（非gap锁）（为什么可以看到这条记录可以查看快照读与当前读的区别，移步文章末尾MVCC文章链接查看原理）

id=1的行锁（非gap锁）

id=1的行锁，gap锁

等待id=2的行锁

到了这其实就可以看出来了，事务一持有自己的行锁，等待事务二持有的行锁，如果此时事务二需要获取事务一持有的行锁，必定会发生死锁。下面继续看，查看死锁信息
事务二继续执行update 语句，此时提示检测到死锁，回滚当前事务

image-20220114235348591

查看死锁信息

showengineinnodbstatus

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

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-01-15 03:48:46 0x7fd520ceb700
*** (1) TRANSACTION: # 事务一
TRANSACTION 682000, ACTIVE 36 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 58, OS thread handle 140553615279872, query id 4198 localhost root updating
update 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 gap
Record 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; asc     h ;;
 2: len 7; hex 020000067712ea; asc     w  ;;
 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 waiting
Record 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; asc     h ;;
 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 read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 59, OS thread handle 140553614690048, query id 4200 localhost root updating
update 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 gap
Record 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; asc     h ;;
 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 waiting
Record 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; asc     h ;;
 2: len 7; hex 020000067712ea; asc     w  ;;
 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 语句加锁信息查看

表中只有一条数据时

image-20220115121106920
二条数据时

image-20220115121231706
三条数据时

image-20220115121340128

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