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

林晓斌



该思维导图由 AI 生成，仅供参考

在上一篇文章中，我提到 MySQL 对自增主键锁做了优化，尽量在申请到自增 id 以后，就释放自增锁。
因此，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 的所有行和间隙加锁呢？
其实，这个问题我们需要考虑的还是日志和数据的一致性。我们看下这个执行序列：
图 1 并发 insert 场景
实际的执行效果是，如果 session B 先执行，由于这个语句对表 t 主键索引加了 (-∞,1]这个 next-key lock，会在语句执行完成后，才允许 session A 的 insert 语句执行。
但如果没有锁的话，就可能出现 session B 的 insert 语句先执行，但是后写入 binlog 的情况。于是，在 binlog_format=statement 的情况下，binlog 里面就记录了这样的语句序列：

公开

同步至部落

取消

完成

0/2000

荧光笔

直线

曲线

笔记

复制

AI

深入了解
翻译
英语
中文简体
中文繁体
法语
德语
日语
韩语
俄语
西班牙语
阿拉伯语
解释
总结

MySQL的insert语句在执行过程中可能会涉及到不同的锁，导致一些特殊情况下的性能问题。文章深入剖析了在可重复读隔离级别下，binlog_format=statement时执行insert ... select语句时，需要对表t的所有行和间隙加锁的情况。同时，讨论了insert循环写入的情况，以及对目标表的锁范围和执行流程。特别是针对一种需求，即往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1的情况进行了详细分析。文章还介绍了执行insert ... select语句时的慢查询日志和explain结果，以及对InnoDB扫描行数的分析。最后，提出了针对这类语句的优化方法，以及使用内存临时表来优化的写法。总的来说，本文深入探讨了insert语句执行过程中的锁问题，以及相关的性能优化方法，对于MySQL数据库开发和性能优化有一定的参考价值。

仅可试看部分内容，如需阅读全部内容，请付费购买文章所属专栏
《MySQL 实战 45 讲》，新⼈⾸单¥68

立即购买

登录后留言

全部留言(86)

最新
精选

huolang
老师，死锁的例子，关于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篇哈
2019-02-13
27
108
轻松的鱼
老师好，想请教一下死锁的例子中： 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。不知道我分析的对不对？
作者回复: 对
2019-03-06
12
71
sonic
你好，我想问下文章中关于为什么需要创建临时表有这一句话：如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。我的疑问是：既然隔离级别是可重复读，照理来说新插入的的记录应该不会参与计算逻辑呀。
作者回复: 可重复读隔离级别下，事务是可以看到自己刚刚修改的数据的，好问题
2019-02-14
3
50
夹心面包
1 关于insert造成死锁的情况,我之前做过测试,事务1并非只有insert,delete和update都可能造成死锁问题,核心还是插入唯一值冲突导致的.我们线上的处理办法是 1 去掉唯一值检测 2减少重复值的插入 3降低并发线程数量 2 关于数据拷贝大表我建议采用pt-archiver,这个工具能自动控制频率和速度,效果很不错,提议在低峰期进行数据操作
作者回复: 👍，这两点都是很有用的建议
2019-02-13
2
50
Mr.Strive.Z.H.L
老师您好：关于文中的锁描述有所疑惑。文中出现过共享的next-key锁和排他的next-key锁。我们知道next-key是由 gap lock 和行锁组成的。我一直以来的认知是 gap lock都是s锁，没有x锁。而行锁有s锁和x锁。比如 select………lock in share mode，行锁是s 锁。比如select………for update，行锁就是x锁。但是gap lock 始终是s锁。文中直接描述next-key lock是排他的，总让我认为gap lock和行锁都是x锁。不知道我理解得对不对？
作者回复: 是这样的，gap lock是无所谓S还是X的。但是record lock 有。 Gap lock + 排他的record 就称作排他的next-key lock 吧😄
2019-02-27
2
36
老杨同志
课后问题：我用的最多还是insert into select 。如果数量比较大，会加上limit 100,000这种。并且看看后面的select条件是否走索引。缺点是会锁select的表。方法二：导出成excel，然后拼sql 成 insert into values(),(),()的形式。方法3，写类似淘宝调动的定时任务，任务的逻辑是查询100条记录，然后多个线程分到几个任务执行，比如是个线程，每个线程10条记录，插入后，在查询新的100条记录处理。
作者回复: 👍
2019-02-13

31
Justin
插入意向锁的gal lock和next key lock中的 gaplock互斥吗？
作者回复: 额，这里我们要澄清一下哈只有一个gap lock，就是 next key lock = gap lock + record lock；我们说一个insert语句如果要插入一个间隙，而这个间隙上有gap lock的话，insert语句会被堵住，这个被堵住的效果，实现机制上是用插入意向锁和gap lock相互作用来实现的。 gap lock并不属于插入意向锁的一部分，就没有“插入意向锁的gal lock”这个概念哈
2019-02-15
7
24
一大只😴
老师，我想问下：insert 语句出现唯一键冲突，会加next-key lock，而产生死锁的例子中，同样也是唯一键冲突却只加了记录锁，然后我按照唯一键冲突中的两个例子试了试 1、比如t表中有两条记录(19,19,19)，(22,22,22)，这时候我再insert (22,22,22)造成了主键冲突，这时候加的就是(19,22]的next-key lock，这个insert为啥不是等值查询？ 2、根据死锁的例子，我又在t表中准备插入一行 session A :begin; insert into t values (25,25,25) session B :insert into t values (25,25,25) 这时候sessionB锁等待 session C：insert into t values (24,24,24) 锁等待，等B锁等待超时，session C插入成功那这里的session B应该是加了个(22,25]的next-key lock，并没有因为是唯一键退化成记录锁我想死锁的例子中t表已经有了(1,1,1),(2,2,2),(3,3,3),(4,4,4)4条记录，这时候insert (null,5,5)，是不是加的(4,5]这个next-key lock，由于是整型并且间隙非常小，所以将他当成记录锁？
作者回复: “那这里的session B应该是加了个(22,25]的next-key lock，并没有因为是唯一键退化成记录锁” 由于insert主键冲突导致的锁，是不会退化的。 session B 加了next-key lock，这样session C插入也要等待，然后等session B超时，释放了这个next-key lock，session C就可以执行了。跟我们文中说的是一致的哦。你这个验证挺合理的呀，不会有因为“间隙非常小，所以将他当成记录锁”这种逻辑哈, a和a+1之间也是有间隙的😆。不过这个是个好的实验和好问题👍
2019-02-13
6
19
inrtyx
现在一般都用utf8mb4?
作者回复: 要看需求，不过因为表情用的很多了，utf8mb4很常用了
2019-04-07

16
roaming
MySQL8.0.12环境下，执行insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1); slow log Rows_examined: 2 Innodb_rows_read 的值增加1 是不是MySQL8进行了优化，先把子查询的结果读出来，再写入临时表？
作者回复: 看来是的了， 👍，很好的验证，我加到明天文章末尾说明
2019-02-13

14

收起评论