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

「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锁。

「记录锁：」

「文章内容收录到个人网站，方便阅读」：https://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 表数据如下：

当执行下面的语句时：

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锁

如下图：

「插入意向锁」

❝

插入意向锁是一种「间隙锁形式的意向锁」，（区别于 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锁和插入意向锁之间的兼容性：

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);

并发插入:

死锁分析

查看事务的锁情况：

SELECT*FROM INFORMATION_SCHEMA.data_locks;

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

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

我们从时间线维度分析:

1. 「事务T2」 insert into t7(id,a) values(26,10) 语句 insert 成功，持有a=10 的 X 行锁(X locks rec but not gap) ;
2. 「事务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。」
3. 「事务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。」
4. 综上，产生死锁。

「解决:」

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

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

死锁回滚记录原始 SQL：

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

「参考：」

• MySQL 官方文档：https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/innodb-locks-set.html
• MySQL REPLACE死锁问题深入剖析：https://github.com/OnelongX/learning/blob/master/mysql/MySQL REPLACE死锁问题深入剖析.pdf

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