MySQL中insert语句的锁为什么这么多？

该文章为《MySQL实战45讲》课程学习笔记及部分摘抄，原课程链接MySQL 实战 45 讲 (geekbang.org)

        在上一篇文章中，我提到 MySQL 对自增主键锁做了优化，尽量在申请到自增 id 以后，就释放自增锁。

        因此，insert 语句是一个很轻量的操作。不过，这个结论对于“普通的 insert 语句”才有效。也就是说，还有些 insert 语句是属于“特殊情况”的，在执行过程中需要给其他资源加锁，或者无法在申请到自增 id 以后就立马释放自增锁。那么，今天这篇文章，我们就一起来聊聊这个话题。

一、 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 时执行：

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

        这个语句时，需要对表 t 的所有行和间隙加锁呢？其实，这个问题我们需要考虑的还是日志和数据的一致性。我们看下这个执行序列：

 图 1 并发 insert 场景

        实际的执行效果是，如果 session B 先执行，由于这个语句对表 t 主键索引加了 (-∞,1]这个 next-key lock，会在语句执行完成后，才允许 session A 的 insert 语句执行。

        但如果没有锁的话，就可能出现 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;

        这个语句到了备库执行，就会把 id=-1 这一行也写到表 t2 中，出现主备不一致。

二、insert 循环写入