insert语句本来是一个轻量级的操作,但是这个语句只是对普通的insert语句有效,也就是说,有些insert语句属于 “特殊情况”
这样在执行过程中需要对其他资源加锁,或者无法申请到id后立刻释放锁
1.
| 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 |
创建完成了这个表,接下来看binlog_format=statement执行
insert into t2(c,d) select c,d from t;
这个语句是否是需要表t的所有行和间隙加锁呢?
接下来执行如下的语句
在真实情况下,两个session并不能一起执行
实际上,如果sessionB先执行了,由于这个语句对表t主键索引加了(-∞,1],这个next-key-lock,会在语句执行完成后,才允许sessionA的insert执行
如果不加锁的话,直接录入binlog的话,可能sessionB的语句先执行,但是在sessionA的语句后写入binlog了
可能出现binlog如下语句
insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;
导致出备库执行的时候,出现了主备不一致的问题
insert….select的语句
在执行insert…select的时候,如果是如下的语句,执行从和一个表上复制到另一个表上
执行如下的语句
insert into t2(c,d) (select c+1,d from t froce index(c) order by c desc limit 1)
整个加锁范围,就是表t 索引上(3,4]和(4,supermum]两个next key lock
其执行流程很简单,就是从表t按照索引倒序,扫描第一行,拿到结果写入t2中
整个语句的扫描行数是1
但是如果执行如下的语句
insert into t(c,d) (select c+1,d from t force index(c) order by c desc limit 1);
这时候会在explain中发现,这个语句会扫描5行,
在Extra字段中,可以看出Using temporary字段,说明使用了临时表
而且并不是只扫描了1行,而是对表t做了一次全表扫描,整体流程如下:
1.创建临时表,表中有两个字段c,d
2.按照索引c扫描t,取出 c=4,3,2,1 回表取回c d的值放入临时表
3.从临时表中取出第一行,插入表t中,这一共,5行
为什么会出现这个情况,是因为这个语句执行过程中,如果遍历了数据,然后更新数据的话,读出来的数据直接写回原表,可能导致读出来的值是新插入的值,导致语句混淆
这个语句会导致在表 t 上做全表扫描，并且会给索引 c 上的所有间隙都加上共享的 next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据。
2.进行insert…select的时候发生唯一键冲突
在可重复读隔离级别下,可以看出sessionB的insert语句进入了锁等待的状态
也就是说sessionA执行的insert语句,发生唯一键冲突的时候,并不会简单的报错,而是在冲突的索引上加上了锁,一个next key lock是由其右边界的值决定的,这就是加上了(5,10]这个读锁
这个加锁的规则,不仅仅对唯一索引有用,对主键索引同样有用
或者插入带有唯一键的表,也会导致
执行如上的语句流程,在sessionA中,先加入了next key lock(0,5],但是因为是唯一索引,退化为了记录锁,已经加上了写锁,但是因为事务没有提交,迟迟不放手
在发生主键冲突的时候,不会由next-key lock退化为行锁的
session B,C 为了保证一行数据的存在,于是对于加上了读锁,虽然没有加上读锁,但是加上了gap lock
在T3时刻,sessionA回滚了,SessionB 和C,继续进行插入,都需要加上写锁,但需要等待对方的行锁,于是导致死锁
insert into …on duplicate key update
这个例子是主键冲突后报错,可以改写为下面的语句,使用上on duplicate key update语法
这个语法是插入一行数据,如果遇到了唯一键约束,就执行后面的更新
那么执行如下的语句
insert into t values(2,1,10) on duplicate key update d=100;
现在表t1 t2中已经有(1,1,1)(2,2,2)两行数据了
这个语句的执行为
MySQL先判断了id这一个主键索引,然后修改了id=2的行
但是MySQL中,执行这个语句返回的affected rows是2,看起来更新了两行,实际上只更新了一行
今日的总结,介绍了几种特殊情况下的insert语句
insert…select语句,会给扫描到的记录加上间隙锁和读锁
insert语句出现唯一键冲突,会给冲突的唯一值上,加上共享的next-key-lock