40-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
2
3
4
5
6
7
8
9
10
11
12
13
14
在可重复读隔离级别下，binlog_format=statement 时执行 insert into t2(c,d) select c,d from t; 会对表 t 所有行和间隙加锁。
这样的目的还是为了一致性。比如
| Session A | Session B |
|---|---|
| insert into t values(-1,-1,-1); | insert into t2(c,d) select c,d from t; |
如果 Session B 先执行，那么会对表 t 加上 (-∞,1]、(1,4]、(4,supremum)。的锁。只有等 Session B 执行完成后才能执行 Session A。
如果不加锁的话，在 statement 下，是直接记录 sql 语句的，而 binlog 是在执行器执行后才进行记录的，这个过程中如果 Session B 的 binlog 记录在了 SessionA 的后面，那么就会出现把 - 1 这一行也写入。
# insert 循环写入
往表 t2 中插入一行数据，这一行的 c 值是表 t 中 c 值的最大值加 1，sql 如下。
insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
这个 sql 的加锁范围是 (3,4]、(4,supremum]。而通过慢 sql 日志或者 show status like '%Innodb_rows_read%' 也能看到值扫描了一行。
insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);
但是这个 sql 通过慢 sql 日志或 show status like '%Innodb_rows_read%' 前后对比看到新增了 5 行。并且用到了临时表，步骤如下：
1. 创建临时表，里面 c,d 两个字段。
2. 根据索引 c 倒序扫描，依次取 c=4、3、2、1，然后回表查数据写入到临时表，所以一共是扫描了 4 行。
3. 由于 limit 1 语义，只取了临时表中的第一行在插入表 t，所以扫描的是 5 行。
也就是说这个语句会导致全表扫描并且会给索引 c 上的所有间隙都加上共享的 next-key lock。
在新版本 MySQL 中已经优化掉了，虽然也用到了临时表但是只扫描了一行。
至于临时表，先要明白上一章说的 INSERT INTO ... SELECT 还是一行一行进行读取 -> 插入 -> 读取 -> 插入这样的顺序，如果说没有临时表可能在读取和插入之间有其他事务进行了操作，插入的数据是在其他事务进行修改后的基础上进行的 c+1 而不是，原先 select 出来的结果了。
# insert 唯一键冲突
| Session A | Session B |
|---|---|
| insert into t values(10,10,10); | |
| begin; insert into t values(11,10,10); //blocking | |
| insert into t values(12,9,9); //blocking |
一旦 Session A 中出现了 10 的唯一键冲突，那么就会对上一个值到这个值之间加间隙锁，并且对这个值加上行锁形成一个 (5,10] 的 next-key lock。但实际上主键冲突也会加上这个 next-key lock。
这个 BUG 已经在 8.0.16 被修复了，MYSQL 官方的回复。
一个死锁的案例
| Session A | Session B | Session C |
|---|---|---|
| begin; insert into t values(null,5,5); | ||
| insert into t values(null,5,5); | insert into t values(null,5,5); | |
| rollback | // deadlock found |
这个死锁产生的逻辑是
- 首先 Session A 执行 insert 语句，c=5 会加根据原则 1 加上 (4,5] 的 next key lock，根据优化 2 会退化 c=5 的行锁。
- Session A 和 Session C 都是要执行相同 insert 语句，由于 Session A 还没有释放锁，所以 B 和 C 都会等待。
- Session A 回滚，B 和 C 的都获取到了间隙共享锁，然后开始尝试获取间隙排它锁，这个时候就出现了循环等待，发生死锁。
# insert into … on duplicate key update
insert into t values(11,10,10) on duplicate key update d=100;
这个 sql 就会给索引 c 上 (5,10] 加一个排他的 next-key lock（写锁）。
insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。
比如说表中已经有 (1,1,1) 和 (2,2,2) 两行数据，那么执行 insert into t values(2,1,100) on duplicate key update d=100; 后发现 id=2 的行数据被替换了，说明这个是先从主键 id 判断的。
注意
需要注意的是客户端返回的 affected row = 2 这个实际上只更新了一行，只是在代码层面上 insert 和 update 都认为自己执行成功了，各自的计数器都加 1。
# 总结
- insert … select 在可重复度的隔离级别下，会对扫描到的记录和他们之间的间隙加上读锁。
- insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock (S 锁)。
- insert into … on duplicate key update 会对新增的数据和上一条数据之间加上排他的 next key lcok。
# 上一章答案
执行 insert into t2 (c,d) select c,d from t; 这个语句的时候，如果隔离级别是可重复读（repeatable read），binlog_format=statement。这个语句为什么会对表 t 的所有记录和间隙加锁？
答案就在一开始说的，如果不加锁可能会出现先写新增的 binlog，然后在执行这个语句的时候把新增的也给插入了。