MySQL死锁案例分析-编程学习网

短信预约信息系统项目管理师报名、考试、查分时间动态提醒

MySQL死锁案例分析

最近项目中某个模块稳定复现MySQL死锁问题，本文记录死锁的发生原因以及解决办法。

1. 预备知识

1.1 表锁和行锁

表锁

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

行锁（仅限定于InnoDB）

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

1.2 行锁简介

行锁的模式有：读/写意向锁（IS/IX锁）,读锁（S锁），写锁（X锁）以及自增锁（AI）。

行锁根据场景的不同又可以进一步细分，依次为Next-Key Lock，Gap Lock间隙锁，Record Lock记录锁和插入意向GAP锁。不同的锁锁定的位置是不同的，比如说记录锁只锁住对应的记录，而间隙锁锁住记录和记录之间的间隔，Next-Key Lock锁住记录和记录之前的间隙。不同类型锁的锁定范围大致如下图所示。

avatar

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

记录锁（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主键上添加写锁。

具体如下图所示：
avatar

以上是基于单条数据讨论，针对多条数据：

update user set age = 10 where id > 49;

执行步骤：

（1）MySQL Server根据where条件读取满足条件的第一条记录，InnoDB引擎返回行记录并加锁；
（2）MySQL Server发起更新行记录的update请求，更新此记录；
（3）反复循环（1）（2）步骤，直到所有满足条件的记录均被修改。

具体如下图所示：
avatar

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;

3.场景复现

开启两个数据库连接，分别执行如下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，插入后删除走回滚就可以了之类的问题（我也不知道为什么这么写的）。

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

session1	session2	stage	备注
`start transaction;`	`start transaction;`
`insert into dead_lock_test (v1,v2) value (4,4);`	do nothing		执行成功
do nothing	`insert into dead_lock_test (v1,v2) value (5,5);`	stage1	执行成功
`delete from dead_lock_test where v1 = 4 and v2 = 4;`	do nothing	stage2	session1执行结果阻塞
do nothing	`delete from dead_lock_test where v1 = 5 and v2 = 5;`	stage3	session2执行结果报deadlock

3.1 stage1

执行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语句
 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 Server会先尝试锁定当前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
 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
 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 最终

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

参考文章

把MySQL中的各种锁及其原理都画出来
MySQL锁系列（二）之锁解读

文章详情

MySQL死锁案例分析

1. 预备知识

1.1 表锁和行锁

1.2 行锁简介

1.3 行锁加锁示例

2.准备工作

2.1 创建数据表并初始化

2.2 开启锁监控

3.场景复现

3.1 stage1

3.2 stage2

3.3 stage3

解决方案

4.1 添加索引

4.2 最终

参考文章

软考中级精品资料免费领

相关文章

猜你喜欢

MySQL死锁案例分析

MySQL死锁举例分析

MySQL死锁的案例分享

Mysql死锁排查实例分析

MySQL死锁分析

【MySQL】死锁案例之六

【MySQL】死锁案例之三

【MySQL】死锁案例之八

java并发编程死锁定义及避免死锁案例分析

Linux死锁实例分析

MySQL死锁的案例详解

一个mysql死锁场景实例分析

Go并发编程之死锁与活锁的案例分析

剖析6个MySQL死锁案例的原因以及死锁预防策略

MySQL中死锁与日志的示例分析

MySQL锁等待与死锁问题分析

MySQL:Innodb 一个死锁案例

mysql insert导致死锁的案例介绍

Java检测死锁案例

MySQL数据库之Purge死锁问题的示例分析