案例剖析：MySQL唯一索引并发插入导致死锁！

站长

2023年11月22日 13:39 · 阅读数 20

文章内容收录到个人网站，方便阅读：hardyfish.top/

MySQL锁列表

共享与排他锁:

S 锁：共享锁，允许其他事务并行读；禁止其他事务持有排它锁

X 锁：排它锁，允许持有排它锁的事务对数据更新，禁止其他事务对数据持有共享锁或排它锁

注：普通的 select * from user 属于快照读，不加任何锁。

-- S锁
select * from user where id=1 lock in share mode;

-- X锁
select * from user where id=1 for update;
update user set name=‘zhangsan’ where id=1;
delete from user where id=1;
insert into user

意向锁:

在 MySQL 事务进行读写时，需要先对表加意向读写锁，意向锁也分为共享和排他锁，记为 IS、IX。

Innodb的意向锁为表级别的锁，IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。

主要有两种意向锁：

意向共享锁（IS lock）:事务想要获得一张表中某几行的共享锁，必须先获取该表的IS锁。
意向排他锁（IX Lock）:事务想要获得一张表中某几行的排他锁，必须先获得该表的IX锁。

记录锁：

文章内容收录到个人网站，方便阅读：hardyfish.top/

即 Record 锁。对于主键和唯一索引(全部字段)的当前读，加 Record 锁，如下：

select * from table where id=1 lock in share mode;
select * from table where id=1 for update;
update table set name = 'zhangsan' where id = 1;
delete from table where id = 1;

间隙锁:

即 Gap 锁，区间锁, 仅仅锁住一个索引区间(开区间)。

在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

对于非唯一索引的当前读，会加 Gap 锁，如下：

-- seq_id 是非唯一索引
select * from table where seq_id=3 lock in share mode;
select * from table where seq_id=3 for update;
update table set name = 'zhangsan' where seq_id = 3;
delete from table where seq_id = 3;

Next-Key锁:

next-key lock = record + gap lock，左开右闭区间，InnoDB使用next-key lock来避免幻读问题。

举例来说：

假设 MySQL 表数据如下：

id	seq_id
4	1
5	3
6	5
7	7
8	9

当执行下面的语句时：

select * from table where seq_id=3 lock in share mode;

加锁情况如下：

在seq_id=3,id=5记录上加 Record 锁；
在[1,4]~[3,5)区间加Gap锁
在[3,5]~[5,6)区间加Gap锁

如下图：

案例剖析：MySQL唯一索引并发插入导致死锁！

插入意向锁

插入意向锁是一种间隙锁形式的意向锁，（区别于 IS、IX，他们是表级别的锁）。在真正执行 INSERT 操作之前设置。

insert会在insert的行对应的索引记录上加一个排它锁，这是一个X record lock，并没有gap，所以并不会阻塞其他session在gap间隙里插入记录。

不过在insert操作之前，还会加一种锁，官方文档称它为insertion intention gap lock，也就是意向的gap锁。

这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。

假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

需要注意，对于insert操作来说，如果发生了唯一索引冲突，则需要对冲突的唯一索引加上 Share Record Lock 和 Gap Lock，（即使是RC事务隔离级别）。

这个在并发插入时容易导致死锁，后面会分析。

next-key锁和插入意向锁之间的兼容性：

是否兼容	gap	insert intention	record	next-key
gap	是	是	是	是
insert intention	否	是	是	否
record	是	是	否	否
next-key	是	是	否	否

Insert 操作涉及到的锁:

INSERT操作，在插入行之前会设置一个插入意向锁。如果该间隙已被加上了 GAP 锁或 Next-Key 锁，则加锁失败进入等待；（注意：Gap锁是为了防止insert，插入意向锁是为了insert并发更快，两者是有区别的）

如果是简单INSERT操作，并且存在唯一主键，那么 next-key lock 退化为记录锁（即行锁）。

如果是INSERT...ON DUPLICATE KEY UPDAT会加上间隙锁。若再发生duplicate-key错误的时候则需要执行UPDATE操作，对重复的主键值设置排它记录锁，对重复的唯一键值设置排它临键锁，还会加一个共享记录锁（S）。

并发insert 唯一键冲突死锁示例

表和数据准备:

create table test(
  id int not null primary key auto_increment,
  a int not null ,
  unique key ua(a)
) engine=innodb;
 
insert into test(id,a) values(1,1),(5,4),(20,20),(25,12);

并发插入:

事务 1	事务 2	说明
事务 1	事务 2	说明
SET autocommit=0;SET SESSION innodb_lock_wait_timeout = 300000;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;		关闭事务自动提交增加事务超时时间为300s设置事务隔离级别为 RC
	SET autocommit=0;SET SESSION innodb_lock_wait_timeout = 300000;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin;
	begin;
	insert into test(id,a) values(26,10);	成功
insert into test(id,a) values(30,10);		阻塞等待加了(4,10) gap
	insert into test(id,a) values(6,10);	成功
	insert into test(id,a) values(40,9);	死锁

死锁分析

查看事务的锁情况：

SELECT*FROM INFORMATION_SCHEMA.data_locks;

利用 show engine innodb status; 命令来查看死锁日志.

关键：对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上 Share Record Lock + Gap Lock。（即使是RC事务隔离级别）

我们从时间线维度分析:

事务T2 insert into t7(id,a) values(26,10) 语句 insert 成功，持有a=10 的 X 行锁(X locks rec but not gap) ;
事务T1 insert into t7(id,a) values(30,10),因为T2 的第一条insert已经插入a=10的记录，事务T1的 insert a=10 则发生唯一约束冲突，需要申请对冲突的唯一索引 a=10加上Share Record Lock + Gap Lock (也即是 lock mode S waiting ) 这是一个间隙锁会申请锁住(4,10)之间的gap区域。从这里会发现，即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。所以，此时事务 T1 持有(4,10)的 Gap Lock，并且等待 a=10上的 share lock。
事务T2 insert into t7(id,a) values(40,9) 该语句插入的 a=9 ，需要先获取插入意向Gap锁(4,10)，的值在 事务T1申请的gap锁(4,10)之间，故需事务T2的第二条insert语句要等待事务T1的Gaplock锁释放，在日志中显示 lock_mode X locks gap before rec insert intention waiting。所以，此时事务 T2 持有a=10上的 X lock，并且等待(4,10)的插入意向Gap Lock。
综上，产生死锁。

解决:

死锁后，InnoDB会选择资源最小的那个事务进行回滚。另外一个事务会执行成功，目前的解决方案是：

尽量不要有大事务，降低锁冲突的可能。
死锁回滚后，记录下原始 SQL，手动处理。

死锁回滚记录原始 SQL：

try {
    // 事务代码
} catch (DataAccessException e) {
    if (e.getCause() instanceof MySQLTransactionRollbackException) {
        // 遇到 MySQL 死锁异常后，记录下 SQL，人工处理插入数据
        log.error("Caught MySQLTransactionRollbackException, manualSql={}", generateInsertSQL(records));
    }
}

参考：

MySQL 官方文档：docs.oracle.com/cd/E17952_0…
MySQL REPLACE死锁问题深入剖析：github.com/OnelongX/le…

文章内容收录到个人网站，方便阅读：hardyfish.top/