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

[git-zjx]

insert ... select 语句

在可重复读隔离级别下，binlog_format=statement 时，会给 select 表里扫描到的记录和间隙加读锁，因为可能会出现 insert 语句先执行，但后写入 binlog 的情况，导致主备不一致。

而当 insert 和 select 的对象是同一个表时，可能会造成循环写入的问题，所以需要引入用户临时表来进行优化，具体优化过程如下：

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 唯一键冲突

insert 语句发生唯一键冲突的时候，并不只是报错返回，还会在冲突的索引上加共享的 next-key lock (S 锁)，因此碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长

insert into … on duplicate key update 会在冲突的索引上加一个排他的 next-key lock (X 锁)，这个语句的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行