关于mysql:故障分析-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 死锁剖析

1. sess1 在 T3 时刻执行了更新，affect rows 为 0，在 c2 的(2，5) 区间中加了 X,GAP 锁。

2. sess2 在 T4 时刻执行了更新，affect rows 为 0，同样在在 c2 的 (0，3) 区间中加了 GAP 锁 lock_mode X locks gap before rec，GAP 锁和 GAP 锁互相兼容，因而 sess1 和 sess2 不会产生期待。

3. 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 锁，产生了期待.

4. 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：惟一索引上的范畴查问会拜访到不满足条件的第一个值为止。

在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行实现后，就要把“不满足条件的行”上的行锁间接开释了，不须要等到事务提交