点击上方"数据与人", 右上角选择“设为星标”分享干货，共同成长！
记一次MySQL Insert 操作导致死锁的分析处理过程，聊聊我的思路。
以一个例子为切入点
一、问题背景
某业务数据库最近两周连续出现两次次死锁告警的情况，本文总结了这次死锁排查的全过程，并分析了导致死锁的原因及解决方案。
希望给大家提供一个死锁的排查及解决思路。
基础环境：
- 主机类型：x3850 X6
- 操作系统：DB:CentOS Linux release 7.4.1708、APP:CentOS Linux release 7.2.1511 (Core)
- 存储：IBM存储，2TB,MULTIPATH
- 内存：64 G
- CPU型号：E7-4830 v3 @ 2.10GHz ( 4 U * 12 core)
- CPU核数：32CORE
- 数据库环境：5.7.27
- 事务隔离级别：RR
问题现象：MySQL 死锁告警
告警日志：
Error 1213: Deadlock found when trying to get lock死锁的sql语句：insert into ... on duplicate key update ...
竟然是一条insert语句！这多少戳中了我的盲区：insert也会导致死锁？带着疑问开始分析。
二、分析说明
- 通过分析日志定位、分析死锁原因；
- 追溯历史数据，分析关键指标的历史波动,这些关键指标可以用来做为数据库健康度参考指标。
- 用实际数据来验证推断，排除掉其它干扰因素，定位数据库问题的根本原因，帮助快速修复。
三、MySQL 锁相关知识
在正式介绍案例前我们先来看一下锁相关的知识，这有助于后面的理解。
共享锁：简称S锁，当事务读取一条记录时需要先获取改记录的S锁，如果一条记录持有S锁，其他事务可以继续获取该记录的S锁，但不能获取X锁。
独占锁：也叫排他锁，简称X锁，如果一条记录持有X 锁，其他事务既不可以获取该记录的S锁，也不能获取该记录的X锁。
1、记录锁：又叫行锁
2、间隙锁，简称gap锁，一种在记录前面添加的锁，该锁阻止新记录插入到当前记录的前面。
3、next-key锁：记录锁+间隙锁的组合
4、插入意向锁：新记录在被间隙锁阻塞时会生成插入意向锁，间隙锁释放后插入意向锁也会释放
5、隐式锁
Mysql 为了节省锁的开销，insert语句执行时记录是不会生成锁的，只有在满足下面条件时insert语句执行后的记录才会生成锁：
当其他事务想获取该记录的S锁或X锁时且该记录所在的聚簇索引中的事务属于活跃状态时（1、每条记录的聚簇索引中会有一个隐藏字段存储该记录被最后修改时所在的事务id 2、已开始但未commit的事务称为活跃的事务），在其他事务中会为该事务（指的是记录所在的聚簇索引中存储的事务）生成X锁，并将其置为not waitting（持有）状态，而将自己的锁状态标记为waitting（阻塞）状态。
而其他情况则可以正常读取，不需要生成锁。
我们将insert时不生成锁，等到满足条件时才生成的锁称为隐式锁，从这里可以看出隐式锁实际上不是一种新锁，而是一种特殊的记录锁。
四、分析思路
通过SHOW ENGINE INNODB STATUS语句查看最近一次的死锁记录。
下面为死锁日志
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 1374635254, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 2045802, OS thread handle 140399504230144, query id 12689481084 192.168.0.1 account_001 update
①发生死锁时此事务正在执行的语句
insert into course_member_statics(course_id,uid) values('20230928145601000001',222222) on duplicate key update member_delete_flag=0
②此事务正在等待其他事务对记录course_id:20230928145601000001、uid:222222释放X型记录锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1753 page no 659149 n bits 360 index idx_courseid_uid of table `uclass`.`course_member_statics` trx id 1374635254 lock_mode X waiting
Record lock, heap no 58 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 22; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;; # 3230323330393238313435363031303030303031是20230928145601000001的utf8编码,这里是course_id字段的值
1: len 4; hex 0003640E; asc GD ;;# 0003640E是222222十六进制编码,这里是uid字段的值【下同】
2: len 8; hex 8000000000a66c9d; asc l ;; # 8000000000a66c9d是10906781十六进制编码,这里是主键id字段的值（存储的是有符号数，前面的8要改成0）【下同】
*** (2) TRANSACTION:
TRANSACTION 1374634984, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
15 lock struct(s), heap size 1136, 160 row lock(s), undo log entries 669
MySQL thread id 2045822, OS thread handle 140399430326016, query id 12689481315 192.168.0.2 account_001 update
③发生死锁时此事务正在执行的语句
insert ignore into course_member_statics(course_id,uid) values
('20230928145601000001',222222),
('20230928145601000001',111111)
*** (2) HOLDS THE LOCK(S):
④此事务对记录course_id:20230928145601000001、uid:222222持有X型记录锁
RECORD LOCKS space id 1753 page no 659149 n bits 312 index idx_courseid_uid of table `uclass`.`course_member_statics` trx id 1374634984 lock_mode X locks rec but not gap
Record lock, heap no 38 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 22; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
1: len 4; hex 0003640E; asc ;;
2: len 8; hex 8000000000a66c9d; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
⑤此事务对记录course_id:20230928145601000001、uid:222222持有插入意向锁，正在等待其他事务对该记录释放间隙锁
RECORD LOCKS space id 1753 page no 659149 n bits 472 index idx_courseid_uid of table `uclass`.`course_member_statics` trx id 1374634984 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 58 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 22; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
1: len 4; hex 0003640E; asc GD ;;
2: len 8; hex 8000000000a66c9d; asc ;;
⑥最后决定回滚事务1
*** WE ROLL BACK TRANSACTION (1)
上述日志中有几个关键信息进行说明：
1、LATEST DETECTED DEADLOCK：最近一次检测到的死锁。
2、(x) TRANSACTION：表示第几个事务。
3、WAITING FOR THIS LOCK TO BE GRANTED：表示当前事务正在对某条记录加某种类型的锁，但由于其他事务已经对该记录持有某种类型的锁而导致阻塞，自己处于等待状态中，一旦其他事务释放锁，该事务就可以加锁成功。
RECORD LOCKS...：表示要添加的、处于阻塞中的锁，其中lock_mode X waiting表示正在等待加X型next-key锁，lock_mode X locks gap before rec insert intention waiting表示想在某条记录前面插入记录，由于该记录持有间隙锁，正在等待间隙锁释放，此时持有插入意向锁。
Record lock：表示要加的、处于等待中的锁作用在哪些记录上，可能会有多条。
4、HOLDS THE LOCK(S)：表示当前事务持有哪些锁。
RECORD LOCKS...：表示已经持有的锁，其中lock_mode X locks rec but not gap表示持有记录的X型记录锁，不持有间隙锁。
Record lock：表示持有的锁作用在哪些记录上，可能会有多条。
5、在(x) TRANSACTION下方和WAITING FOR THIS LOCK TO BE GRANTED或HOLDS THE LOCK(S)上方之间出现的sql语句：为导致出现死锁的sql语句。
6、WE ROLL BACK TRANSACTION (1)：表示死锁发生时回滚哪个事务，这里回滚的是第一个事务，Mysql会将受影响的数据最少的事务回滚。
下面我们对这次死锁做一次完整的分析：
表结构
CREATE TABLE `course_member_statics` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键', `course_id` varchar(40) NOT NULL DEFAULT '' COMMENT '课程ID', `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户UID', `delete_flag` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否被删除 状态 0：未删除 1：已删除', PRIMARY KEY (`id`), UNIQUE KEY `idx_courseid_uid` (`course_id`,`uid`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程成员表';
course_id和uid为唯一索引。
1、事务2执行插入222222这条数据
insert ignore into course_member_statics(course_id,uid) values
('20230928145601000001',222222);
2、事务1执行
insert into course_member_statics(course_id,uid) values
('20230928145601000001',222222)
on duplicate key update member_delete_flag=0;
发现事务2已经插入了一个相同的记录，于是事务1要对该记录添加X型next-key锁。
3、对一条insert的数据，如果其他事务要对其加S型或X型锁，且该记录对应的聚簇索引中存储的事务id处于活跃状态时，就会触发这条记录上的隐式锁升级为显示锁。
在这里就是事务1给事务2在222222记录增加X型记录锁，并将其状态置于持有状态，同时将自己置于阻塞状态。
4、事务2执行插入111111这条数据
insert ignore into course_member_statics(course_id,uid) values
('20230928145601000001',111111);
按照二级索引存储的特点，记录111111要插在记录222222的前面，这时出现了插入意向锁阻塞，按照我们前面的说的，在某条记录前面插入数据只有在该记录持有间隙锁时才会阻塞，问题是事务1对记录222222并没有持有间隙锁，怎么会阻塞呢？
Mysql规定，只要别的事务对记录生成了一个显式的间隙锁的锁结构，不论那个事务是已经获取到了该锁(granted)，还是正在等待获取(waiting)，当前事务要在该记录前面插入新记录都会被阻塞。
回到该例，因为事务1已经为记录222222生成了一个X型的next-key锁结构（next-key锁包含间隙锁），虽然该锁的状态是在阻塞等待中，但事务2在该记录前插入记录仍然会被阻塞。
这时事务1在等待事务2释放记录222222上的X型记录锁，同时事务2也在等待事务1在记录222222上的间隙锁释放，出现了互相等待的现象，导致了死锁发生。
最后由于死锁导致事务1被回滚了，事务2执行成功，因为事务2包含事务1的数据，所有没有对线上的数据造成影响，就算最后回滚的是事务2也没问题，因为insert ignore into语句代码做了错误重试处理。
有兴趣的同学可以还原一下上述死锁的场景，对每条sql语句的执行进行加锁分析，顺带思考如下两个问题。
1、改变SQL执行顺序，是否会避免死锁？
2、上面所有的分析都是基于RR隔离级别分析的，如果换成RC，还会出现死锁吗？
问题的答案都是肯定的，有兴趣各位可以测试一下。
五、如何避免死锁
死锁能避免吗？避免死锁的方法有哪些：
1、添加合适的索引
建立合适的索引，缩小锁作用的范围和减少事务的执行的时间，这样能减少事务之间争抢锁的概率。
2、改变事务执行语句的顺序
在确保业务功能正确的情况下，可以通过改变语句的执行顺序避免死锁。当然前提是得知道是什么原因导致的死锁。但很多时候语句的执行顺序会随着数据的变化而变化的，无法人为控制。
3、尽量避免大事务：
将大事务拆分为多个较小的事务，以减少事务的持有锁时间。避免在事务中执行长时间的计算、网络操作或其他耗时操作，以减少锁的持有时间。
4、优化查询语句，提供系统性能：
尽量使用更精确的条件来限制查询范围，避免长时间持有锁定的行。提升数据库服务器的性能，例如增加内存、优化硬件配置等，可以减少事务持有锁的时间。确保系统具有足够的资源来处理并发请求，避免因资源不足而导致事务等待。
虽然死锁可以一定程度的减少，但无法完全避免，当出现死锁时也不必过于担心，Mysql会以最小的代价回滚事务，只要我们做了合理的重试机制，比如对异步的操作要做重试处理，因为发生错误无法直接反馈给操作人，同步操作还好，发生死锁会收到报错信息，重新执行即可。
更多精彩内容，关注我们▼▼
