关于mysql:MySQL死锁排查笔记

6次阅读

共计 5793 个字符，预计需要花费 15 分钟才能阅读完成。

本文是生产环境中产生死锁的一次事变排查笔记，通过浏览本文你能够理解到：

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

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; 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 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; 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 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; 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 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; 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 的行锁，造成相互阻塞

表中只有一条数据时
二条数据时
三条数据时

能够看到在表没有主键和索引的状况下，事务持有的锁是要把扫描到的行都增加锁
select for update 加锁与 update 雷同，新开一个客户端，执行查问语句
```
select * from t for update;
```

Innodb 事务和锁的信息