MySQL 死锁模拟分析

小王格子

已于 2022-09-28 18:48:27 修改

阅读量840

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql 死锁

于 2022-09-28 17:50:09 首次发布

原文链接：https://www.lmonkey.com/t/vEwdR9pyJ

版权

MySQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

死锁是一种不同事务无法继续进行的情况，因为每个事务都持有另一个需要的锁。因为两个事务都在等待资源变得可用，所以它们都不会释放它持有的锁。本文记录死锁的发生原因以及解决办法。

一、预备知识

1.1 表锁和行锁

表锁

表锁是 MySQL 中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张数据表，用户的写操作（插入/删除/更新）前，都需要获取写锁（写锁会相互阻塞）；没有写锁时，读取用户才能获取读锁（读锁不会相互阻塞）。

行锁（仅限定于InnoDB）

行级锁可以最大程度的支持并发处理（同时也带来了最大的锁开销）。行级锁只在存储引擎实现，而 MySQL 服务器层没有实现。服务器层完全不了解存储引擎中的具体实现。

1.2 行锁简介

1.2.1 共享锁和排它锁

InnoDB 实现标准的行级锁定，其中有两种类型的锁，共享（S）锁和排他（X）锁。

共享 ( S) 锁允许持有该锁的事务读取一行。
独占 ( X ) 锁允许持有该锁的事务更新或删除一行。

1.2.2 意向锁

InnoDB 支持多粒度锁定，允许行锁和表锁共存。例如，诸如这样的语句在指定的表上 LOCK TABLES ... WRITE 获取了一个排他锁（一个锁）。X为了使多粒度级别的锁定变得实用，InnoDB 请使用意图锁。意向锁是表级锁，它指示事务稍后对表中的行需要哪种类型的锁（共享或独占）。有两种类型的意图锁：

意向共享锁( IS) 表示事务打算在表中的各个行上设置共享锁。
意向排他锁 ( IX)表示事务打算对表中的各个行设置排他锁。

例 SELECT ... LOCK IN SHARE MODE 设置一个 IS 锁，并 SELECT ... FOR UPDATE 设置一个 IX 锁。

意图锁定协议如下：

在事务可以获取表中行的共享锁之前，它必须首先获取IS表上的锁或更强的锁。
在事务可以获取表中行的排他锁之前，它必须首先获取IX 表上的锁。

下面的矩阵总结了表级锁类型的兼容性。

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

行锁根据场景的不同又可以进一步细分：

Next-Key Lock

Next-Key Lock 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

间隙锁 Gap Lock

间隙锁是在索引记录之间的间隙上的锁，或在第一条索引记录之前或最后一条索引记录之后的间隙上的锁。例 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 阻止其他事务将值15 插入 column t.c1，无论该列中是否已经存在任何此类值，因为该范围内所有现有值之间的间隙都已锁定。

记录锁 Record Lock

记录锁是对索引记录的锁。例 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 阻止任何其他事务插入、更新或删除值为的 t.c1 行 10。

插入意向锁 Insert Intention Locks

插入意向锁是一种 INSERT 在行插入之前由操作设置的间隙锁。此锁表示插入的意图，即如果插入到同一索引间隙中的多个事务未插入到间隙内的同一位置，则它们无需相互等待。假设有值为 4 和 7 的索引记录。分别尝试插入值 5 和 6 的单独事务，在获得插入行的排他锁之前，每个使用插入意向锁锁定 4 和 7 之间的间隙，但不要相互阻塞，因为行是不冲突的。

不同的锁锁定的位置是不同的，锁定范围大致如下图所示。

在这里插入图片描述

此外，锁对应的死锁日志信息标记如下所示：

记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap
间隙锁(LOCK_GAP): lock_mode X locks gap before rec
Next-key锁(LOCK_ORNIDARY): lock_mode X
插入意向锁(LOCK_INSERT_INTENTION): lock_mode X locks gap before rec insert intention

1.3 行锁加锁示例

InnoDB 是聚簇索引，也就是 B+ 树的叶子节点存储了主键索引以及数据行；InnoDB 的二级索引的叶子节点存储的则是主键值，所以通过二级索引查询数据时，需要根据查询到的主键去聚簇索引中再次进行查询。

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';

（1）第一条 SQL 使用主键进行查询，则只需要在 id=49 主键上加上写锁（X锁）；

（2）第二条 SQL 使用二级索引查询，首先在 name='Tom' 上加写锁，然后根据获取的主键索引查询，在 id=49 主键上添加写锁。

具体如下图所示：

在这里插入图片描述
以上是基于单条数据讨论，针对多条数据：

update user set age = 10 where id > 49;

执行步骤：

（1） MySQL 根据where条件读取满足条件的第一条记录，InnoDB 引擎返回行记录并加锁；

（2） MySQL 发起更新行记录的update请求，更新此记录；

（3）反复循环（1）（2）步骤，直到所有满足条件的记录均被修改。

具体如下图所示：

在这里插入图片描述

二、准备工作

2.1 创建数据表并初始化

create table dead_lock_test
(
    id int auto_increment
        primary key,
    v1 int not null,
    v2 int not null
);

insert into  dead_lock_test (v1,v2) value (1,1);
insert into  dead_lock_test (v1,v2) value (2,2);
insert into  dead_lock_test (v1,v2) value (3,3);

需要注意，数据表中仅存在主键索引。此外，默认数据库引擎为 InnoDB，事务隔离级别为 RR（可重复读，相对于 RC 解决了幻读）。

2.2 开启锁监控

使用如下语句，开启 MySQL 锁监控：

# 开启
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

# 关闭
set GLOBAL innodb_status_output_locks=OFF;

三、场景复现

开启两个数据库连接，分别执行如下 SQL语句

# session1
start transaction ;
insert into  dead_lock_test (v1,v2) value (4,4);
delete from dead_lock_test where v1 = 4 and v2 = 4;
commit;

# session2
start transaction;
insert into  dead_lock_test (v1,v2) value (5,5);
delete from dead_lock_test where v1 = 5 and v2 = 5;
commit;

不要问事务里就两条SQL，插入后删除走回滚就可以了之类的问题（我也不知道为什么这么写的）。

事务执行步骤如下表所示：

在这里插入图片描述
执行 show engine innodb status; 节选事务信息如下所示：

------------
TRANSACTIONS
------------
Trx id counter 91328
Purge done for trx's n:o < 91327 undo n:o < 0 state: running but idle
History list length 19
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 91327, ACTIVE 37 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 15668, query id 3147 localhost 127.0.0.1 root
TABLE LOCK table `igw_proxy_rule_management`.`dead_lock_test` trx id 91327 lock mode IX
---TRANSACTION 91322, ACTIVE 44 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 22788, query id 3103 localhost 127.0.0.1 root
TABLE LOCK table `igw_proxy_rule_management`.`dead_lock_test` trx id 91322 lock mode IX

输出内容中节选当前事务信息，当前存在两个运行中事务，trx id 分别为 91322 以及 91327。

TABLE LOCK table igw_proxy_rule_management.dead_lock_test trx id 91322 lock mode IX

dead_lock_test 表上添加IX锁

91322 事务对应 session1，91327 事务对应 session2

3.2 stage2

执行 delete from dead_lock_test where v1 = 4 and v2 = 4; 后可发现，当前事务被阻塞。

执行 show engine innodb status; 节选事务信息如下所示：

------------
TRANSACTIONS
------------
Trx id counter 91332
Purge done for trx's n:o < 91332 undo n:o < 0 state: running but idle
History list length 21
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 91327, ACTIVE 58 sec
* 2 lock strcut(s): 事务91327中锁链表长度为2（每个链表节点表示该事务持有的一个锁结构，包括表锁/记录锁等），当前事务包含表锁（IX）以及一个行锁（记录锁）；
* 1 row lock(s)：当前事务持有的行锁个数；
* undo log entries 1：当前事务的undo log个数
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 24, OS thread handle 15668, query id 3147 localhost 127.0.0.1 root
* TABLE LOCK：当前事务持有的表锁（IX）
TABLE LOCK table `igw_proxy_rule_management`.`dead_lock_test` trx id 91327 lock mode IX
* RECORD LOCKS：当前事务持有的行锁（lock_mode X locks rec but not gap）
* space id 92: dead_lock_test所在空间编号
* page no 4: 当前记录所在页码
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91327 lock_mode X locks rec but not gap
* 行锁信息： heap no=6
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 4; hex 80000005; asc     ;; * hex 80000005：当前加锁的记录id=5
 1: len 6; hex 0000000164bf; asc     d ;;   * hex 0000000164bf： 事务ID；
 2: len 7; hex 81000000b20110; asc        ;;    * hex 81000000b20110： 回滚指针；
 3: len 4; hex 80000005; asc     ;; * hex 80000005: v1字段对应数值；
 4: len 4; hex 80000005; asc     ;; * hex 80000005：v2字段对应数值；

---TRANSACTION 91322, ACTIVE 65 sec fetching rows
* tables in use 1: 有1个表正在被使用；
* locked 1： 有一个表锁
mysql tables in use 1, locked 1
* LOCK WAIT：事务91322处于锁等待状态；其他字段解释详见上问
LOCK WAIT 5 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 23, OS thread handle 22788, query id 3199 localhost 127.0.0.1 root updating
* 事务91322当前执行SQL语句
/* ApplicationName=DataGrip 2021.1.1 */ delete from dead_lock_test where v1 = 4 and v2 = 4
* 事务91322等待的锁信息
------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91322 lock_mode X waiting
* 事务91322等待的记录锁（锁对应记录主键为5，被事务91327持有）
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000164bf; asc     d ;;
 2: len 7; hex 81000000b20110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

------------------
* 以下展示事务91322所持有的锁以及尝试获取的锁，首先是表意向锁（IX锁）
TABLE LOCK table `igw_proxy_rule_management`.`dead_lock_test` trx id 91322 lock mode IX
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91322 lock_mode X
* 记录锁（锁对应记录主键为1）
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000164b3; asc     d ;;
 2: len 7; hex 81000000ad0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;

* 记录锁（锁对应记录主键为2）
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000164b4; asc     d ;;
 2: len 7; hex 82000000ad0110; asc        ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 4; hex 80000002; asc     ;;

* 记录锁（锁对应记录主键为3）
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 0000000164b9; asc     d ;;
 2: len 7; hex 81000000b00110; asc        ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000003; asc     ;;

* 记录锁：锁定记录（添加记录时创建的锁）
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91322 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 0000000164ba; asc     d ;;
 2: len 7; hex 020000011a03cb; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000004; asc     ;;

* 间隙锁：锁定记录（删除记录时创建的锁，在RR模式下生效，主要解决幻读）
* 需要注意，InnoDB的删除记录不是物理删除，而是标记删除（等待后续记录覆盖），因此可理解删除类似于更新操作
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91322 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 0000000164ba; asc     d ;;
 2: len 7; hex 020000011a03cb; asc        ;;
 3: len 4; hex 80000004; asc     ;;
 4: len 4; hex 80000004; asc     ;;

* 事务91322尝试获取的锁（被事务91327持有）
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91322 lock_mode X waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000164bf; asc     d ;;
 2: len 7; hex 81000000b20110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;

（1）由以上注释可知，事务 91322 在尝试删除时，会对表中所有记录添加记录锁。

这是因为当前删除记录条件为 v1 = 4 and v2 = 4，在v1与v2字段上，并未建立相应的索引。

因为无法通过索引确定主键，导致 MySQL 会先尝试锁定当前 dead_lock_test 表中所有记录添加记录锁（可以设置参数进行优化，根据where条件逐渐解除不满足条件记录上的记录锁）。

（2）事务 91322 尝试对 dead_lock_test 表中所有记录添加锁，发现记录 （id=5） 已经被事务 91327 添加记录锁，导致事务91322 只能等待事务 91327 放弃记录锁。

3.3 stage3

执行 delete from dead_lock_test where v1 = 5 and v2 = 5; 后即可发现终端输出：

[2021-05-13 15:33:29] [40001][1213] Deadlock found when trying to get lock; try restarting transaction

执行 show engine innodb status; 节选死锁信息如下所示：

因为内容较多，不再列出解释，详见输出信息中文注释部分

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-05-13 17:27:09 0xca4
*** (1) TRANSACTION:
* 事务91322持有锁情况，在stage2已经详细解释，此处不再赘述
TRANSACTION 91322, ACTIVE 78 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 23, OS thread handle 22788, query id 3199 localhost 127.0.0.1 root updating
/* ApplicationName=DataGrip 2021.1.1 */ delete from dead_lock_test where v1 = 4 and v2 = 4

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91322 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000164b3; asc     d ;;
 2: len 7; hex 81000000ad0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000164b4; asc     d ;;
 2: len 7; hex 82000000ad0110; asc        ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000003; asc     ;;
 1: len 6; hex 0000000164b9; asc     d ;;
 2: len 7; hex 81000000b00110; asc        ;;
 3: len 4; hex 80000003; asc     ;;
 4: len 4; hex 80000003; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91322 lock_mode X waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000164bf; asc     d ;;
 2: len 7; hex 81000000b20110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 91327, ACTIVE 71 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 24, OS thread handle 15668, query id 3237 localhost 127.0.0.1 root updating
/* ApplicationName=DataGrip 2021.1.1 */ delete from dead_lock_test where v1 = 5 and v2 = 5

*** (2) HOLDS THE LOCK(S):
* 事务91327持有记录（id=5）的记录锁，此锁正在被事务91322等待持有
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91327 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000164bf; asc     d ;;
 2: len 7; hex 81000000b20110; asc        ;;
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000005; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
* 事务91327等待持有记录锁，锁信息见后续解释
RECORD LOCKS space id 92 page no 4 n bits 72 index PRIMARY of table `igw_proxy_rule_management`.`dead_lock_test` trx id 91327 lock_mode X waiting
* 事务91327等待持有记录（id=1）的记录锁（delete无法走索引查询，因此会尝试对所有表记录进行加锁，但是事务91322持有id=1/2/3/4的记录锁，死锁条件构成）
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000164b3; asc     d ;;
 2: len 7; hex 81000000ad0110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

由上可知：

（1）事务 91322 执行删除操作时，尝试获取表中所有记录的记录锁，其中记录（id=5）的锁被事务 91327 持有；

（2）事务 91327 执行删除操作时，尝试获取表中所有记录的记录锁，发现记录（id=1/2/3/4）的锁被事务 91322 持有；

（3）至此，事务 91322 与事务 91327构成互相等待，死锁形成。

四、解决方案

4.1 添加索引

由上述的分析可知，删除时因为where条件无法利用索引，导致 MySQL 会尝试对表中所有记录加记录锁，产生死锁。

我们仅需在v1以及v2字段上建立联合索引，缩小记录冲突范围。

create index dead_lock_test_v2_v1_index on dead_lock_test (v1, v2);

此处没有设置唯一索引，如果多个事务根据索引查询，锁定的记录存在重叠，也容易复现死锁现象。

不过当前业务侧的数据插入，可保证在短暂时间范围内，不存在重叠记录，且表中存在一些重复数据，因此不使用唯一索引。

4.2 最终

表中添加索引。事务中添加后再删除，通过回滚实现。

Reference:
https://www.lmonkey.com/t/vEwdR9pyJ
https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html

小王格子

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录