Insert死锁问题剖析
线上有个批量的insert … on duplicate key update语句引发的死锁问题，查过很多资料并且亲自尝试过后，发现好多博客说的都是错的，其实本身只跟insert的顺序有关，在此记录一下备忘。
前置知识
X型锁：排他锁
S型锁：共享锁
行锁：锁住一行记录
Next-Key锁：左开右闭区间
Gap锁：左右开区间
构造死锁
建表：
CREATE TABLE hero (
number INT AUTO_INCREMENT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (number),
UNIQUE KEY uk_name (name)
) Engine=InnoDB CHARSET=utf8;
构造初始数据：
INSERT INTO hero VALUES
(1, 'l刘备', '蜀'),
(3, 'z诸葛亮', '蜀'),
(8, 'c曹操', '魏'),
(15, 'x荀彧', '魏'),
(20, 's孙权', '吴');
好了，开始了，下面开始两个事务，按顺序执行：
事务1
begin:
INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
事务2
begin:
INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
事务1
INSERT INTO hero(name, country) VALUES('d邓艾', '魏');
来了，它来了，这个时候我们就可以注意到事务2的死锁报错了：
# 事务T2
mysql> INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
原因
- T1先插入name值为g关羽的记录，可以插入成功，此时对应的唯一索引记录被隐式锁保护
- T2随后插入name值为g关羽的记录（必须为同一条记录），发生阻塞，并且T2会想要获取一把S型next-key锁（只有唯一索引才会发生）(左开右闭)。此时T1的隐式锁转化为显示锁（X型行锁）
- T1想要插入d邓艾的记录，由于T2的next-key锁（虽然没被T2持有，但锁已存在）而进入阻塞等待状态，进而发生死锁
故死锁产生的原因
T1在等待T2释放name值为’g关羽’的二级索引记录上的gap锁。
T2在等待T1释放name值为’g关羽’的二级索引记录上的X型行锁。
MySQL 5.7 的死锁
前提
在比较新的版本中都可以遇见的，只要是insert … on duplicate key update 触发了后面的update操作，那么此时其他的insert语句都会被阻塞，这主要是为了解决RR下的一些幻读问题。
示例
在5.7版本中又有一些特殊情况。还是举例
假如有如下表和数据，其中name为唯一键索引
demo表
| id | name | value |
|---|---|---|
| 1 | 111 | 2 |
| 2 | 222 | 2 |
| 3 | 333 | 2 |
此时，如果事务1执行了：
insert into demo (name, value) VALUES ("333", 1) ON duplicate KEY UPDATE value = value + 1;
事务2执行了：
insert into demo (name, value) VALUES ("223", 1) ON duplicate KEY UPDATE value = value + 1;
事务3执行了：
insert into demo (name, value) VALUES ("224", 1) ON duplicate KEY UPDATE value = value + 1;
那么首先事务2和事务3会被阻塞，然后事务1提交了，事务2和事务3就会发生死锁，其中一个爆出死锁的错误然后失败，另一个则成功执行。
原因
当insert … on duplicate key 执行成功之时，会在当前唯一键和之前唯一键之间加一个隐式GAP锁，如上会在222和333之间加上GAP锁，此时，事务2和事务3想插入新数据都会被GAP锁阻塞，此时GAP锁转为显式，事务2和事务3同时也分别想要获取X型的插入意向锁。
然后事务1提交，此时GAP锁并不会被释放，由于5.7的bug，事务2和事务3都会拿到GAP锁，此时他们去获取插入意向锁的时候由于GAP锁被对方拿到而矛盾，进而死锁。
解决方案
网上有很多方法，这里我提出一个另类的想法。
我们可以先用非事务的insert ignore去初始化数据，后面在用事务的update操作去更新。
参考：https://zhuanlan.zhihu.com/p/457191971
转载：https://blog.csdn.net/jhgdike/article/details/127673970