唯一键insert into on duplicate 死锁问题

本文主要是复现场景以及分析具体是哪些锁导致的阻塞，不会重点讲排查思路以及对show engine innodb的内容分析

1、复现问题

表结构

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alias` int(11) NOT NULL,
  `age` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_idx_alias` (`alias`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

隔离级别为RR

数据库版本5.7.20

插入数据

insert into test(alias,age) values(1,1),(3,3),(5,5),(7,7);

死锁前提条件：多个并发同时执行insert into on duplicate update xxx 判断唯一键是否存在，存在更新数据

| 时间戳 | 事务1 | 事务2 | 事务3 || T1 | begin;insert into test(alias,age) valuse(9,9) on duplicate key update age=9; | | || T2 | | begin;insert into test(alias,age) valuse(10,10) on duplicate key update age=11; | || T3 | | | begin;insert into test(alias,age) valuse(11,11) on duplicate key update age=11; || T4 | commit; | | ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |

2、了解一下本案例用到了哪些锁

1、记录锁

也就是我们常说的行锁，仅仅锁住一行数据

记录锁永远都是锁在索引上的

2、间隙锁

RR隔离级别下在操作数据时，会对操作的索引值秉着左开右闭的原则锁住两个索引之间的间隙（等值操作情况下唯一键会退化为行锁）

间隙锁阻塞事务插入数据到间隙中，但是不会影响其他事务持有间隙锁，换句话说就是间隙锁之间不会等待，只可能扩大间隙锁的范围，等待的往往是记录锁

3、Next-Key Locks

Next-key锁是记录锁+gap锁

这里分享个基础知识点，对于没有索引的表，RC级别是锁住所有表记录（锁住隐式索引），还可以插入数据

RR隔离级别是锁住所有的记录加索引的间隙，无法插入数据

RR隔离级别也是通过这个来避免某些场景下的幻读

4、插入意向锁(Insert Intention Lock)

插入意向锁是为了提供并发插入的性能，也是一种间隙锁， 多个事务 同时写入 不同数据 至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待

同时插入意向锁是会被gap锁锁住的，在持有gap锁的情况下，新insert插入到这个范围时会获取这个范围的插入意向锁，此时如果这个范围有gap锁，则会阻塞当前插入，同时因为阻塞的插入意向锁，所以不会对后续相关的insert进行额外阻塞

MySQL45讲说

所以我个人理解RR就是通过gap锁锁住插入意向锁来避免幻读的，这块大神有不同见解的话欢迎指导一下

3、本案例如何形成的死锁

1、锁兼容性

2、问题分析

事务1：因为没有9这行数据，持有gap锁（7~+∞），持有插入意向锁

事务2：由于gap锁之间互相不会等待，持有gap锁，因为事务1持有gap锁的范围包含10，所以意向锁被阻塞，等待中

事务3：由于gap锁之间互相不会等待，持有gap锁，因为事务1持有gap锁的范围包含11，所以意向锁被阻塞，等待中

T4时间事务1提交，gap锁释放

此时事务2的意向锁被事务3持有的gap锁锁住，事务3的意向锁被事务2持有的gap锁锁住，因此形成死锁，事务3回滚

这里需要注意的几点：

1、唯一键执行insert into on duplicate，在5.7.20版本无论插入数据表中是否存在，都会产生gap锁，其他等值操作在数据存在情况下不会产生gap锁，会退化为行锁

主键已经修复这个问题，不会产生gap锁，所以主键执行insert into on duplicate在这个版本不会有问题，亲测在5.7.35版本已经修复唯一键的问题

2、插入数据时如果唯一键数据已经存在还会额外对这行数据加一个共享锁S，即使已经报错唯一键冲突了依旧会加入这个锁可能会影响其他操作，因此事务在遇到唯一键报错的情况下要及时回滚事务，防止锁影响后续操作

3、建议

1、判断业务是否一定要在数据库中执行insert into on duplicate操作，代码侧能否判断

2、唯一键是否为必须

3、升级到没有问题的版本，如8.0新版本或5.7.35