40 | insert 语句的锁

有一些特殊的insert语句，在执行过程中需要加锁，本文针对这些特殊都insert语句进行展开。

insert … select

建表：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

事务级别是可重复读，binlog_format=statement。

demo：

insert into t2(c,d) select c,d from t;

这个语句会对表t加间隙锁，和以前说的加锁规则不一样，虽然c是唯一主键，但是不会退化为行锁。

原因： 有如下并发执行序列：

如果没有锁的话， 会出现 session B 的 insert 语句先执行，后写入 binlog 的情况。于是，在 binlog_format=statement 的情况下，binlog 里面就记录了这样的语句序列：

insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;

备库执行binlog会出现主备不一致。

加上了锁，sessionB先执行，会对表t的主键索引加了(-∞,1] ( 其实是（-∞，+∞），整个表锁了，并不是所有都表锁 )这个 next-key lock，会在语句执行完成后，才允许 session A 的 insert 语句执行。

insert 循环写入

demo:

insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

这个语句的加锁范围，就是表 t 索引 c 上的 (3,4]和 (4,supremum]这两个 next-key lock，以及主键索引上 id=4 这一行。

它的执行流程也比较简单，从表 t 中按照索引 c 倒序，扫描第一行，拿到结果写入到表 t2 中。

整条语句的扫描行数是 1。

假如是插入到原表，demo2：

insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

这个语句使用了临时表，也就是说，执行过程中，需要把表 t 的内容读出来，写入临时表。

流程是这样的:

1. 创建临时表，表里有两个字段 c 和 d。
2. 按照索引 c 扫描表 t，依次取 c=4、3、2、1，然后回表，读到 c 和 d 的值写入临时表。这时，Rows_examined=4。
3. 由于语义里面有 limit 1，所以只取了临时表的第一行，再插入到表 t 中。这时，Rows_examined 的值加 1，变成了 5。共计扫描了5行。

也就是说，这个语句会导致在表 t 上做全表扫描，并且会给索引 c 上的所有间隙都加上共享的 next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据。

至于这个语句的执行为什么需要临时表，原因是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录（因为可重复读隔离级别下，事务是可以看到自己刚刚修改的数据的），新插入的记录如果参与计算逻辑，就跟语义不符。

由于实现上这个语句没有在子查询中就直接使用 limit 1，从而导致了这个语句的执行需要遍历整个表 t。它的优化方法也比较简单，就是用前面介绍的方法，先 insert into 到临时表 temp_t，这样就只需要扫描一行；然后再从表 temp_t 里面取出这行数据插入表 t1。

MySQL8.0版本以后，做了优化，会先把子查询的结果读出来，再写入临时表，所以扫描的行数应该就是2.

insert 唯一键冲突

demo：

这个例子也是在可重复读（repeatable read）隔离级别下执行的。可以看到，session B 要执行的 insert 语句进入了锁等待状态。

session A 持有索引 c 上的 (5,10] 的共享 next-key lock（读锁）。

这个不锁 (10,+supernum] ， next-key lock 就是由它右边界的值定义的，唯一索引等值匹配，退化为行锁。

MySQL官方有一个bug：如果冲突的是主键索引，就加记录锁，唯一索引才加 next-key lock。但实际上，这两类索引冲突加的都是 next-key lock。

但是我试了一下，MySQL5.5版本，主键索引确实加的是行锁，唯一索引加的是next-key lock。

死锁demo：

1. 在 T1 时刻，启动 session A，并执行 insert 语句，此时在索引 c 的 c=5 上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁
2. 在 T2 时刻，session B 要执行相同的 insert 语句，发现了唯一键冲突，加上读锁；同样地，session C 也在索引 c 上，c=5 这一个记录上，加了读锁。
3. T3 时刻，session A 回滚。这时候，session B 和 session C 都试图继续执行插入操作，都要加上写锁。两个 session 都要等待对方的行锁，所以就出现了死锁。

注：这里不能说成加上读锁，应该是判断数据是否能写入需要进行当前读，进行当前读需要先加读锁，然后就被写锁阻塞住了，最后session a回滚，b和c成功加上读锁，且读取判断是否可写入，得到的答案是可以，然后都进行锁升级，于是死锁了

1. 在 session A rollback 前，session B/C 都因为唯一性冲突申请了 S Next-key lock，但是被 session A 的 X but not gap lock 阻塞；
2. 在 session A rollbak 后，session B/C 顺利获得 S Next-key lock，并且都要继续进行插入，这时候我认为是因为插入意向锁（LOCK_INSERT_INTENTION）导致的死锁，因为插入意向锁会被 gap lock 阻塞，造成了相互等待。还没有进入到记录 X lock。

我们说一个insert语句如果要插入一个间隙，而这个间隙上有gap lock的话，insert语句会被堵住，这个被堵住的效果，实现机制上是用插入意向锁和gap lock相互作用来实现的。 gap lock并不属于插入意向锁的一部分