MySQL insert 死锁问题记录

jhgdike

已于 2022-11-11 18:49:09 修改

阅读量2.7k

点赞数 3

分类专栏：个人日记 MySQL 文章标签： mysql 数据库

于 2022-11-03 17:50:56 首次发布

本文链接：https://blog.csdn.net/jhgdike/article/details/127673970

版权

MySQL 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

个人日记

5 篇文章 0 订阅

订阅专栏

这里写自定义目录标题

Insert死锁问题剖析
MySQL 5.7 的死锁

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