mysql中insert … select锁范围

1、执行 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

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。

2、insert select加了什么锁，唯一索引查询出现死锁场景

• 在 T1 时刻，启动 session A，并执行 insert 语句，此时在索引 c 的 c=5 上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁
• 在 T2 时刻，session B 要执行相同的 insert 语句，发现了唯一键冲突，加上读锁（0，5]；
• 同样地，session C 也在索引 c 上，c=5 这一个记录上，加了读锁（0，5]。
• T3 时刻，session A 回滚。
• 这时候，session B 和 session C 都试图继续执行插入操作，都要加上写锁。两个 session 都要等待对方的行锁，所以就出现了死锁。

3. insert into … on duplicate key update怎么加锁

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（写锁）。

4. 总结

• insert … select 是很常见的在两个表之间拷贝数据的方法。
• 在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。
• 而如果 insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。
• insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。
• 如果在 insert … select 执行期间有其他线程操作原表，会导致逻辑错误。其实，这是不会的，如果不加锁，就是快照读。一条语句执行期间，它的一致性视图是不会修改的，所以即使有其他事务修改了原表的数据，也不会影响这条语句看到的数据。