MySQL 并发insert 唯一键冲突导致的死锁-阿里云开发者社区

一前言

死锁其实是一个很有意思也很有挑战的技术问题，大概每个DBA和部分开发朋友都会在工作过程中遇见。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。

二背景知识

2.1 insert 锁机制在分析死锁案例之前，我们先学习一下背景知识 insert 语句的加锁策略。我们先来看看官方定义:

"An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting."

相信大部分的DBA同行都知道在事务执行insert的时候会申请一把插入意向锁（Insert Intention Lock)。在多事务并发写入不同数据记录至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。假设有一个索引记录包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。但是如果遇到唯一键呢？

"If a duplicate-key error occurs, a shared lock on the duplicate index record is set."

对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加上S Next-key Lock。从这里会发现，即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。然而，文档没有说明的是，对于检测到冲突的唯一索引，等待线程在获得S Lock之后，还需要对下一个记录进行加锁，在源码中由函数row_ins_scan_sec_index_for_duplicate进行判断。via (MySQL REPLACE死锁问题深入剖析 )。我们可以通过如下例子进行验证。

2.2 验证

准备环境默认事务隔离级别为RC模式。

CREATE TABLE t8 (
a int AUTO_INCREMENT PRIMARY KEY,
b int,
c int,
unique key ub(b)
) engine=InnoDB;
insert into t8 values (NULL,1,2)

sess1	sess2
begin;
delete from t8 where b = 1;	begin;
	insert into t8 values (NULL,1);
commit；
	update t8 set c=13 where b=1;

2.3 过程分析

在每次执行一条语句之后都执行show innodb engine status查看事务的状态， 执行完 delete 语句，事务相关日志显示如下:

---TRANSACTION 462308671, ACTIVE 6 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051370 localhost root init
show engine innodb status
TABLE LOCK table `test`.`t8` trx id 462308671 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
RECORD LOCKS space id 232 page no 3 n bits 72 index `PRIMARY` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap

从日志中我们可以看到 delete语句获取了唯一索引ub和主键两个行级锁(lock_mode X locks rec but not gap) 。 执行完 insert 之后 再查看innodb engine status，事务相关日志显示如下:

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 462308676, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root update
insert into t8 values (NULL,1,2)
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S waiting
------------------
TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S waiting
---TRANSACTION 462308671, ACTIVE 70 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051465 localhost root init
show engine innodb status
TABLE LOCK table `test`.`t8` trx id 462308671 lock mode IX
RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
RECORD LOCKS space id 232 page no 3 n bits 72 index `PRIMARY` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap

根据官方的介绍，并结合日志，我们可以看到insert into t8 values (NULL,1,2)在申请一把S Next-key-Lock , 显示lock mode S waiting。这里想给大家说明的是在innodb 日志中如果提示 lock mode S /lock mode X ，其实都是gap锁，如果是行记录锁会提示but not gap ,请读者朋友们在自己分析死锁日志的时候注意。 sess1 delete语句提交之后，sess2的insert 不要提交，不要提交，不要提交。再次查看innodb engine status，事务相关日志显示如下:

------------

TRANSACTIONS

------------

Trx id counter 462308678

Purge done for trxs n:o < 462308678 undo n:o < 0 state: running but idle

History list length 1845

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 462308671, not started

MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051526 localhost root init

show engine innodb status

---TRANSACTION 462308676, ACTIVE 41 sec

3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1

MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root cleaning up

TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX

RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S

RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S locks gap before rec

sess1中的事务因为提交已经结束。innodb中的事务列表中只剩下sess2 中的insert 的事务了。从获取锁的状态上看insert获取一把S Next-key Lock 锁和插入行之前的S GAP锁。看到这里大家是否有疑惑,官方文档说:

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

会对insert成功的记录加上一把X 行锁，为什么看不见呢？我们再在sess1 中执行update t8 set c=13 where b=1; 并查看事务日志

------------

TRANSACTIONS

------------

Trx id counter 462308679

Purge done for trxs n:o < 462308678 undo n:o < 0 state: running but idle

History list length 1845

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 462308678, ACTIVE 12 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781059217 localhost root updating

update c set c=13 where b=1

------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308678 lock_mode X locks rec but not gap waiting

------------------

TABLE LOCK table `test`.`t8` trx id 462308678 lock mode IX

RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308678 lock_mode X locks rec but not gap waiting

---TRANSACTION 462308676, ACTIVE 5113 sec

4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1

MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781059230 localhost root init

show engine innodb status

TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX

RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S

RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S locks gap before rec

RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock_mode X locks rec but not gap

从日志中可以看到 sess2的事务持有的锁多了一把 lock_mode X locks rec but not gap,也即是 sess2 对 insert 成功的记录加上的X 行锁。分析至此，对于并发insert造成唯一键冲突的时候 insert的加锁策略是:

第一阶段 唯一性约束检查，先申请LOCK_S + LOCK_ORDINARY

第二接入 获取阶段一的锁并且insert成功之后

插入的位置有Gap锁:LOCK_INSERT_INTENTION,为了防止其他insert 唯一键冲突。

新数据插入:LOCK_X + LOCK_REC_NOT_GAP

三案例分析

本案例是两个事务并发insert 唯一键冲突和gap锁一起导致的死锁案例。

3.1 环境

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

3.2 测试用例

T1	T2
begin;	begin;
	insert into t7(id,a) values(26,10);
insert into t7(id,a) values(30,10);
	insert into t7(id,a) values(40,9);

3.3 死锁日志

------------------------

LATEST DETECTED DEADLOCK

------------------------

2017-09-17 15:15:03 7f78eac15700

*** (1) TRANSACTION:

TRANSACTION 462308661, ACTIVE 6 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ead9d700, query id 781045166 localhost root update
insert into t7(id,a) values(30,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308661 lock mode S waiting
*** (2) TRANSACTION
TRANSACTION 462308660, ACTIVE 43 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 3796960, OS thread handle 0x7f78eac15700, query id 781045192 localhost root update
insert into t7(id,a) values(40,9)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

日志分析我们从时间线维度分析: 事务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 则发生唯一约束冲突，需要申请对冲突的唯一索引加上S Next-key Lock (也即是 lock mode S waiting ) 这是一个间隙锁会申请锁住[4,10],[10,20]之间的gap区域。从这里会发现，即使是RC事务隔离级别，也同样会存在Next-Key Lock锁，从而阻塞并发。 事务T2 insert into t7(id,a) values(40,9) 该语句插入的a=9 的值在事务T1申请的gap锁[4,10]之间，故需事务T2的第二条insert语句要等待事务T1的S-Next-key Lock锁释放，在日志中显示lock_mode X locks gap before rec insert intention waiting。

四总结

本文案例和知识点一方面从官方文档获取，另一方面是根据何登成和姜承尧两位MySQL技术大牛的技术分享整理，算是站在巨人的肩膀上的学习总结。在研究分析死锁案例的过程中，insert 的意向锁和 gap 锁这种类型的锁是比较难分析的，相信通过上面的分析总结大家能够学习到 insert的锁机制，如何加锁，如何进行 insert 方面死锁分析。

MySQL 并发insert 唯一键冲突导致的死锁

一前言

二背景知识

2.1 insert 锁机制在分析死锁案例之前，我们先学习一下背景知识 insert 语句的加锁策略。我们先来看看官方定义:

2.2 验证

2.3 过程分析

三案例分析

3.1 环境

3.2 测试用例

3.3 死锁日志

四总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL 并发insert 唯一键冲突导致的死锁

一 前言

二 背景知识

2.1 insert 锁机制 在分析死锁案例之前，我们先学习一下背景知识 insert 语句的加锁策略。我们先来看看官方定义:

2.2 验证

2.3 过程分析

三 案例分析

3.1 环境

3.2 测试用例

3.3 死锁日志

四 总结

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

一前言

二背景知识

2.1 insert 锁机制在分析死锁案例之前，我们先学习一下背景知识 insert 语句的加锁策略。我们先来看看官方定义:

三案例分析

四总结