🪴 Obsidian Publish

MySQL insert逻辑

14分钟阅读

普通的 insert 语句是轻量化的操作，但是有些 insert 语句涉及到 select、约束后就会需要申请锁，进而变得复杂起来。

insert…select 语句在 binlog=statement 时需要对 select 表的查询字段进行 加锁。否则，可能出现 A 线程后执行，但是 binlog 早于 B 线程，导致数据不一致情况。为了避免上述情况，会对行+间隙加锁。

insert 和 select 对象是一个表时，也会出现循环写入情况。这种情况下，有时需要引入用户临时表来做优化，来让其他线程等待时间变短。

insert 语句出现唯一键冲突时，会在冲突上增加共享的 next-key lock。若出现，应该尽早回滚，避免加锁过长，影响其他命令。

避免唯一键冲突，可以选择 insert into … on duplicate key update 语句。当唯一键冲突时，就会按照索引的顺序，修改跟第一个索引冲突的行。

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

insert … select 语句

我们先从昨天的问题说起吧。表 t 和 t2 的表结构、初始化数据语句如下，今天的例子我们还是针对这两个表展开。

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 的所有行和间隙加锁呢？

其实，这个问题我们需要考虑的还是日志和数据的一致性。我们看下这个执行序列：

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

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

如果没有锁，但是 session B 先执行，session A 再执行，但是 binlog 中先是 A 的 binlog，再是 B 的 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 循环写入

执行 insert … select 的时候，对目标表也不是锁全表，而是只锁住需要访问的资源。

如果现在有这么一个需求：要往表 t2 中插入一行数据，这一行的 c 值是表 t 中 c 值的最大值加 1。

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。

如果我们是要把这样的一行数据插入到表 t 中的话：

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

语句的执行流程是怎样的？扫描行数又是多少呢？可以看到，这时候的 Rows_examined 的值是 5。

40.insert语句的锁为什么这么多？.png|600

40.insert语句的锁为什么这么多？-1.png|600

从 Extra 字段可以看到“Using temporary”字样，表示这个语句用到了临时表。也就是说，执行过程中，需要把表 t 的内容读出来，写入临时表。

图中 rows 显示的是 1，我们不妨先对这个语句的执行流程做一个猜测：如果说是把子查询的结果读出来（扫描 1 行），写入临时表，然后再从临时表读出来（扫描 1 行），写回表 t 中。那么，这个语句的扫描行数就应该是 2，而不是 5。

所以，这个猜测不对。实际上，Explain 结果里的 rows=1 是因为受到了 limit 1 的影响。

从另一个角度考虑的话，我们可以看看 InnoDB 扫描了多少行。如图 5 所示，是在执行这个语句前后查看 Innodb_rows_read 的结果。

40.insert语句的锁为什么这么多？-2.png|600

可以看到，这个语句执行前后，Innodb_rows_read 的值增加了 4。因为默认临时表是使用 Memory 引擎的，所以这 4 行查的都是表 t，也就是说对表 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。

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

至于这个语句的执行为什么需要临时表，原因是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。

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

当然，由于这个语句涉及的数据量很小，你可以考虑使用内存临时表来做这个优化。使用内存临时表优化时，语句序列的写法如下：

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

insert 唯一键冲突

session Asession B
insert into t values(10,10,10);
begin;
insert into t values(11,10,10);
(Duplicate entry'10' for key'c')
insert into t values(12,9,9);
(blocked)

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

也就是说，session A 执行的 insert 语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁。我们前面说过，一个 next-key lock 就是由它右边界的值定义的。这时候，session A 持有索引 c 上的 (5,10] 共享 next-key lock（读锁）。

session Asession Bsession C
begin;
insert into t values(null,5,5);
insert into t values(null, 5,5);insert into t values(null,<br>5,5);
rollback;(Deadlock found)

在 session A 执行 rollback 语句回滚的时候，session C 几乎同时发现死锁并返回。

这个死锁产生的逻辑是这样的：

  1. 在 T1 时刻，启动 session A，并执行 insert 语句，此时在索引 c 的 c=5 上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁（如果你的印象模糊了，可以回顾下第 21 篇文章介绍的加锁规则）。

  2. 在 T2 时刻，session B 要执行相同的 insert 语句，发现了唯一键冲突，加上读锁；同样地，session C 也在索引 c 上，c=5 这一个记录上，加了读锁。

  3. T3 时刻，session A 回滚。这时候，session B 和 session C 都试图继续执行插入操作，都要加上写锁。两个 session 都要等待对方的行锁，所以就出现了死锁。

40.insert语句的锁为什么这么多？-3.png|600

insert into … on duplicate key update

上面这个例子是主键冲突后直接报错，如果是改写成

insert into t values(11,10,10) on duplicate key update d=100;

的话，就会给索引 c 上 (5,10] 加一个排他的 next-key lock（写锁）。

insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

注意，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。

现在表 t 里面已经有了 (1,1,1) 和 (2,2,2) 这两行，我们再来看看下面这个语句执行的效果：

40.insert语句的锁为什么这么多？-4.png|600

可以看到，主键 id 是先判断的，MySQL 认为这个语句跟 id=2 这一行冲突，所以修改的是 id=2 的行。

需要注意的是，执行这条语句的 affected rows 返回的是 2，很容易造成误解。实际上，真正更新的只有一行，只是在代码实现上，insert 和 update 都认为自己成功了，update 计数加了 1， insert 计数也加了 1。

课后问题

老师，死锁的例子，关于 sessionA 拿到的 c=5 的记录锁，sessionB 和 sessionC 发现唯一键冲突会加上读锁我有几个疑惑： 1. sessionA 拿到的 c=5 的记录锁是写锁吗？ 2. 为什么 sessionB 和 sessionC 发现唯一键冲突会加上读锁？ 3. 如果 sessionA 拿到 c=5 的记录所是写锁，那为什么 sessionB 和 sessionC 还能加 c=5 的读锁，写锁和读锁不应该是互斥的吗？ 4. sessionA 还没有提交，为什么 sessionB 和 sessionC 能发现唯一键冲突？

1.是的 2.不知 3.互斥的，两个语句在等待，注意 next-key lock 是由间隙锁和记录锁组成的哦，间隙锁加成功了的。好问题。 4. 还没有提交，但是这个记录已经作为最新记录写进去了，复习一下 08 篇哈

1 关于 insert 造成死锁的情况,我之前做过测试,事务 1 并非只有 insert,delete 和 update 都可能造成死锁问题,核心还是插入唯一值冲突导致的.我们线上的处理办法是 1 去掉唯一值检测 2 减少重复值的插入 3 降低并发线程数量 2 关于数据拷贝大表我建议采用 pt-archiver,这个工具能自动控制频率和速度,效果很不错,提议在低峰期进行数据操作

略。

反向链接