40 | 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.1.insert … select 语句在RR并且binlog_formart为statement下为什么要加锁？

• 数据一致性问题。举例：sessionA=insert into t values(-1,-1,-1)。sessionB=insert into t2(c,d) select c,d from t。解释： 如果session B 先执行，由于这个语句对表 t 主键索引加了 (-∞,1]这个 next-key lock，会在语句执行完成后，才允许 session A 的 insert 语句执行。否则，如果不加锁就有可能sessionB的语句先执行后写入binlog。这个语句到了备库执行，就会把 id=-1 这一行也写到表 t2 中，出现主备不一致。

2.2.insert循环写入不同表和相同表有什么区别（举例说明）？

• insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1); // 由于c索引有序，所以这个语句执行的慢查询日志（slow log）的Rows_examined 的值是 1

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

2.2.1.上面这个语句explain的结果是扫描多少行，extra中显示什么？上述扫描行数有什么问题？为什么会用到临时表？有什么改进方法？

• 1行且用到临时表

• Explain结果里的 rows=1 是因为受到了 limit 1，通过show status like '%Innodb_rows_read%'可以看到扫描行数前后增加了4。总体为5。

• 原因是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。备注：RR隔离级别下本事务的更新变更自己是可以看到的。

• 分为两步，1）先将数据导入到内存临时表中【1行】。2）再从内存临时表查询出插入到t表。

2.3.insert 唯一键冲突会有什么问题？

• 唯一键冲突时还会加上next-key锁。  

• 唯一键冲突时的死锁问题。

2.3.1.死锁分析

• 1）在 T1 时刻，启动 session A，并执行 insert 语句，此时在索引 c 的 c=5 上加了记录锁。注意，这个索引是唯一索引，因此退化为记录锁

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

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

2.4.insert into … on duplicate key update，语句的影响行数是多少

• 2，插入+更新

三、评论中的小技巧

3.1.插入唯一值冲突导致的死锁，线上的处理办法？

•  1 ）去掉唯一值检测； 2）减少重复值的插入； 3）降低并发线程数量；