故障分析 | MySQL死锁案例分析
原创
作者：杨奇龙
网名“北在南方”，资深 DBA，主要负责数据库架构设计和运维平台开发工作，擅长数据库性能调优、故障诊断。
本文来源：原创投稿
*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
一 背景
死锁，其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发同学都会在工作过程中遇见 。
本次分享的死锁案例是 更新不存在的记录加上 X GAP lock 和 insert 的意向锁冲突。希望能够对想了解死锁的朋友有所帮助。
二 案例分析
2.1 业务逻辑
业务逻辑: 业务需要并发不同数据(insert+update)，首先是更新记录，如果发现更新的 affect rows 为0，然后就执行插入，如果插入失败，再执行更新。因此存在并发的情况下，两个事务都执行了更新，affect rows 为0，然后有进行并发插入相同记录的情况。
2.2 环境说明
数据库版本 8.0.30
事务隔离级别 REPEATABLE-READ
create table dl(
id int auto_increment primary key,
c1 int not null ,
c2 int not null,
c3 int not null,
unique key uc1(c1),
unique key uc2(c2));
insert into dl(c1,c2,c3) values(2,0 ,2),(5,5,5);2.3 测试用例
2.4 死锁日志
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 1422661, ACTIVE 51 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 3149, OS thread handle 140261085611776, query id 3267 localhost msandbox update
insert into dl(c1,c2,c3) values(3,2,2)
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 50 page no 5 n bits 72 index uc1 of table `test`.`dl` trx id 1422661 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 page no 6 n bits 72 index uc2 of table `test`.`dl` trx id 1422661 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 1422664, ACTIVE 45 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 3152, OS thread handle 140261086668544, query id 3268 localhost msandbox update
insert into dl(c1,c2,c3) values(3,2,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 50 page no 6 n bits 72 index uc2 of table `test`.`dl` trx id 1422664 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 page no 5 n bits 72 index uc1 of table `test`.`dl` trx id 1422664 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
*** WE ROLL BACK TRANSACTION (2)2.5 死锁分析
- sess1 在 T3 时刻执行了更新，affect rows 为0，在c2的(2，5) 区间中加了X,GAP锁。
- sess2 在 T4 时刻执行了更新，affect rows 为0，同样在在c2的(0，3)区间中加了GAP锁
lock_mode X locks gap before rec，GAP 锁和GAP 锁相互兼容，因此 sess1 和 sess2 不会发生等待。
- sess1 在 T5 时刻执行了插入，由于插入记录c2 =2 的时候需要申请插入意向锁(insert intention lock)，而insert intention lock锁和已存在的 (0,3) 的
lock_mode X locks gap before rec insert intention waiting是冲突的，也就是sess1 需要等待sess2 在 T4 持有的GAP X锁，发生了等待.
- sess2 在 T6 时刻执行了插入，由于插入的时候需要申请插入意向锁(insert intention lock)，同样需要等待Sess1 在 T3 持有的GAP锁，两个会话循环等待对方释放锁，因此导致死锁。
感兴趣的朋友可以自己 做测试，每一步操作 都打印 show engine innodb status 查看当前事务的锁等待事件。 或者执行 SQL 查询当前事务持有或者等待的锁
select ENGINE_TRANSACTION_ID, index_name, lock_type, lock_mode, LOCK_STATUS, lock_data from performance_schema.data_locks;
2.6 如何解决
将update + insert 合并为 insert on duplicate key 的方式，避免同一个事务申请多个锁。
小结
敲黑板 ，重点: 死锁是因为不同事务对表记录加锁的顺序不一致导致相互等待对方持有的锁导致的。大家在分析死锁的时候能基于该原则去分析理清业务的sql 逻辑和执行顺序，基本上都能解决大部分的问题场景。
另外文章的最后我们再次复习一下 MySQL 的加几个基本原则，方便大家后面遇到死锁案例进行分析:
原则 1：加锁的基本单位是 next-key lock。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。