读mysql45讲-insert特殊情况

insert语句是一个很轻量的操作。不过，这个结论对于“普通的insert语句”才有效。也就是说，还有些insert语句是属于“特殊情况”的，在执行过程中需要给其他资源加锁，或者无法在申请到自增id以后就立马释放自增锁。

insert…select语句

表t和t2的表结构、初始化数据语句如下

CREATE TABLE `t` ( `id` int(11) NOTNULLAUTO_INCREMENT, 
`c` int(11) DEFAULTNULL, 
`d` int(11) DEFAULTNULL,
 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情况下执行这个sql：

insert into t2(c,d) select c,d from t;

会给 t 表的所有行和间隙加上锁。

实际执行效果是，如果session先执行，会给表t主键索引加了(-∞,1]这个next-key lock，只有在session完成之后，才会开始执行sessionA的sql。

但如果没有锁的话，就可能出现session B的insert语句先执行，但是后写入binlog的情况。于是，在binlog_format=statement的情况下，binlog里面就记录了这样的语句序列：

insert into t values(-1,-1,-1) 的同时执行 insert into t2(c,d) select c,d from t;
然后写入binglog

实际在t2中不会写入(-1,-1,-1)；但是在binlog日志中记录的sql到了备库执行的时候，就会将(-1,-1,-1)插入表中，出现主备不一致的情况，所以需要加锁。

insert 循环写入

当然了，执行insert …select 的时候，对目标表也不是锁全表，而是只锁住需要访问的资源。

如果现在有这么一个需求：要往表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中扫描的行数只是一行。

如果插入表也是t：

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

这个sql在慢查询日志中会发现扫描的行数是5行。

用explain解析sql：

从Extra字段可以看到“Using temporary”字样，表示这个语句用到了临时表。也就是说，执行过程中，需要把表t的内容读出来，写入临时表。

执行流程如下：

1. 创建临时表，表里有两个字段c和d。
2. 按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表。这时，Rows_examined=4。
3. 由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中。这时，Rows_examined的值加1，变成了5。

（这里我没有理解为啥扫描表t的时候会进行全表扫描，明明是有个limit 1的，而且强制走了c字段的索引。）

使用到临时表是因为是这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符，所以需要将查询结果先放到临时表中进行操作。难道是因为这个原因所以才会全表扫描的？但是limit 1确实是对临时表中的数据应用的。

insert 唯一键索引

对于有唯一键的表，插入数据时出现唯一键冲突也是常见的情况了。

这个例子也是在可重复读（repeatable read）隔离级别下执行的。可以看到，session B要执行的insert语句进入了锁等待状态。 也就是说，session A执行的insert语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁。这时候，session A持有索引c上的(5,10]共享next-key lock（读锁），所以sessionB的sql会被阻塞住。

在session A执行rollback语句回滚的时候，session C几乎同时发现死锁并返回。
这个死锁产生的逻辑是这样的：

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

insert into … on duplicate key update

上面这个例子是主键冲突后直接报错，如果是改写成:

insert into t values(11,10,10) on duplicate key update d=100;

这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句,将这条记录的d字段更新为100。
注意，如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。

表里已经有了(1,1,1)和(2,2,2)这两条数据，插入(2,1,100)既违反了主键id的唯一性约束，也会违反字段c的唯一性约束；所以在执行后面更新语句的时候，会更新第一个发生冲突的索引字段，也就是主键id=2的那条记录，将字段d更新为100。

需要注意的是，执行这条语句的affected rows返回的是2，很容易造成误解。实际上，真正更新的只有一行，只是在代码实现上，insert和update都认为自己成功了，update计数加了1， insert 计数也加了1。