死锁场景之insert语句加锁流程分析（二）

死锁场景之insert语句加锁流程分析（二） 死锁场景之insert语句加锁分析（二）

关于mysql有哪些锁？锁的分类有哪些？可查看另外一篇博客：mysql 的锁

在实际情景中的加锁情况分析博客请查看另外一篇博客：数据库 *** 作加锁情况分析

关于数据库的死锁日志如何查看？ 请查看另外一篇博客：死锁日志查看

关于数据库的insert导致死锁案例查看：死锁场景之insert语句死锁分析

这篇主要是介绍平时常见的insert语句的加锁情况。

大部分的DBA同行都知道在事务执行insert的时候会申请一把插入意向锁(Insert Intention Lock)。在多个会话并发写入不同数据记录至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。但是如果加入意向锁、next-key锁的时候，再insert的时候的加锁情况进行分析。

情况一：非唯一索引下的insert加锁情况分析 1、环境介绍

针对mysql数据库，innodb 引擎下，隔离级别在 REPEATABLE READ insert语句加锁情况

CREATE TABLE `my` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`num1` int(11) DEFAULT NULL,

`num2` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `indx_1` (`num1`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4

insert into my(num1,num2) values(3,4),(5,8),(7,9);

说明：创建一个表 my ，id为自增主键索引，num1上有一个普通非唯一索引

2 、事务 *** 作介绍 T2T1begin;select * from my where num1=5 for update;begin;insert into my(num1,num2) values(4,10);insert into my(num1,num2) values(4,10);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

说明：

事务T2：

• 加悲观锁查询一条num1为5的记录

事务T1：

• 插入一条num1为4的数据记录

3 、死锁日志

数据库查看最近的死锁日志：

show engine innodb status

日志：

insert into my(num1,num2) values(4,10) ------- 
TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 27 page no 5 n bits 72 index indx_1 of table `mydatabase`.`my` trx id 2391 lock_mode X locks gap before rec insert intention waiting

加锁情况分析：

a. 事务T2 也即sess1执行的事务

sess1 先执行 sql select * from my where num1=5 for update; 该事务持有索引num1=5 的行锁lock_mode X （next key锁）,因为是RR隔离级别, 所以sess1 不仅对该记录持有一个行锁，还持有两个gap锁**[3,4]-[5,8], [5,8]-[7,9]** 。

b. 事务T1的日志也即sess2执行的事务

其执行 insert into my(num1,num2) values(4,10); 申请对 num1=4 加锁，根据日志看，其需要加入插入意向锁

但是因为sess1中加上了间隙锁还没释放，故sess2的事务T1等待sess1的事务T2释放锁资源。

情况二：唯一索引下的insert 加锁情况分析 1、环境介绍

针对mysql数据库，innodb 引擎下，隔离级别在 REPEATABLE READ 出现的死锁问题

CREATE TABLE `my2` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`num1` int(11) DEFAULT NULL,

`num2` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

unique KEY  `indx_1` (`num1`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4

insert into my2(num1,num2) values(2,3),(5,4),(7,8);

说明：创建一个表 my2 ，id为自增主键索引，num1上有一个唯一索引

2 、事务 *** 作介绍 T2T1begin;select * from my where num1>=5 for update;begin;insert into my(num1,num2) values(4,10);insert into my(num1,num2) values(4,10);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

说明：

事务T2：

• 加悲观锁查询一条num1大于5的记录

事务T1：

• 插入一条num1为4的数据记录

3 、死锁日志

数据库查看最近的死锁日志：

show engine innodb status

日志：

---TRANSACTION 2448, ACTIVE 11 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1967928, OS thread handle 140143801399040, query id 8311237 127.0.0.1 root update 
insert into my(num1,num2) values(4,10); 
---TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 54 page no 3 n bits 80 index PRIMARY of table `mydatabase`.`my` trx id 2448 lock_mode X insert intention waiting

加锁情况分析：

a. 事务T2 也即sess1执行的事务

sess1 先执行 sql select * from my where num1>=5 for update; 该事务持有索引num1=5 的行锁lock_mode X （next key锁）,因为是RR隔离级别, 所以sess1 不仅对该记录持有一个行锁，还持有两个gap锁**[2,3]-[5,5], [5,5]-[+∞,+∞]** 。

b. 事务T1的日志也即sess2执行的事务

其执行 insert into my(num1,num2) values(4,10); 申请对 num1=4 加锁，根据日志看，其需要加入插入意向锁

但是因为sess1中加上了next-key锁还没释放，故sess2的事务T1等待sess1的事务T2释放锁资源。

还有一种情况，就是sess1查询一条已经存在的记录，然后对该记录进行删除，暂不提交事务。那么sess2这时候再提交insert这条记录的时候，也会出现等待。这时候insert申请的是一个S共享锁，与delete申请到的nextkey锁冲突，导致了等待的发生。具体案例可查看 死锁场景之insert语句死锁分析

总结

本文介绍了两个情况，在RR事务隔离级别下，普通索引与唯一索引两种情况下insert语句的加锁情况。

insert N的流程(非唯一索引的情况):

1. 找到大于N的第一条记录M
2. 如果M上面没有gap/next-key lock的话,则可以插入,否则等待锁释放 (对其next-rec加insert intension lock，由于有gap锁，所以等待)

insert N的流程(有唯一索引的情况)：

1. 找到大于N的第一条记录M，以及前一条记录 P。
2. 如果M上有gap/next-key lock的话 等待(对其next-rec加insert intension lock，由于有gap锁，所以等待)，如果没有则进行下一步判断
3. 检查前一条记录P: 判断p是否等于n：
   1. 如果两者不相等: 则完成插入
   2. 如果相等， 再判断P 是否有锁
      1. 如果没有锁: 报1062错误（duplicate key） --说明该记录已经存在，报重复值错误
      2. 如果有锁: 则加S-lock --说明该记录被标记为删除，事务还未提交.