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

目录 Insert死锁问题剖析 前置知识 构造死锁 原因 故死锁产生的原因 MySQL 5.7 的死锁 前提 示例 原因 解决方案 总结 Insert死锁问题剖析 线上有个批量的insert on duplicate key update语句引发的

                        目录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的死锁报错了：

1. 事务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持有，但锁已存在）而进入阻塞等待状态，进而发生死锁