MySQL insert死锁问题解决详细记录

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版本中又有一些特殊情况。还是举例

假如有如下表和数据

demo表

此时，如果事务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

总结