40 | insert 语句的锁

有一些特殊的insert语句,在执行过程中需要加锁,本文针对这些特殊都insert语句进行展开。

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

事务级别是可重复读,binlog_format=statement。

demo:

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

这个语句会对表t加间隙锁,和以前说的加锁规则不一样,虽然c是唯一主键,但是不会退化为行锁。

原因:
有如下并发执行序列:
在这里插入图片描述
如果没有锁的话, 会出现 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;

备库执行binlog会出现主备不一致。

加上了锁,sessionB先执行,会对表t的主键索引加了(-∞,1] ( 其实是(-∞,+∞),整个表锁了,并不是所有都表锁 )这个 next-key lock,会在语句执行完成后,才允许 session A 的 insert 语句执行。

insert 循环写入

demo:

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。

假如是插入到原表,demo2:

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

这个语句使用了临时表,也就是说,执行过程中,需要把表 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。共计扫描了5行。

也就是说,这个语句会导致在表 t 上做全表扫描,并且会给索引 c 上的所有间隙都加上共享的 next-key lock。所以,这个语句执行期间,其他事务不能在这个表上插入数据。

至于这个语句的执行为什么需要临时表,原因是这类一边遍历数据,一边更新数据的情况,如果读出来的数据直接写回原表,就可能在遍历过程中,读到刚刚插入的记录(因为可重复读隔离级别下,事务是可以看到自己刚刚修改的数据的),新插入的记录如果参与计算逻辑,就跟语义不符。

由于实现上这个语句没有在子查询中就直接使用 limit 1,从而导致了这个语句的执行需要遍历整个表 t。它的优化方法也比较简单,就是用前面介绍的方法,先 insert into 到临时表 temp_t,这样就只需要扫描一行;然后再从表 temp_t 里面取出这行数据插入表 t1。

MySQL8.0版本以后,做了优化,会先把子查询的结果读出来,再写入临时表,所以扫描的行数应该就是2.

insert 唯一键冲突

demo:
在这里插入图片描述
这个例子也是在可重复读(repeatable read)隔离级别下执行的。可以看到,session B 要执行的 insert 语句进入了锁等待状态。

session A 持有索引 c 上的 (5,10] 的共享 next-key lock(读锁)。

这个不锁 (10,+supernum] , next-key lock 就是由它右边界的值定义的,唯一索引等值匹配,退化为行锁。

MySQL官方有一个bug:如果冲突的是主键索引,就加记录锁,唯一索引才加 next-key lock。但实际上,这两类索引冲突加的都是 next-key lock。

但是我试了一下,MySQL5.5版本,主键索引确实加的是行锁,唯一索引加的是next-key lock。

死锁demo:
在这里插入图片描述

  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 都要等待对方的行锁,所以就出现了死锁。

注:这里不能说成加上读锁,应该是判断数据是否能写入需要进行当前读,进行当前读需要先加读锁,然后就被写锁阻塞住了,最后session a回滚,b和c成功加上读锁,且读取判断是否可写入,得到的答案是可以,然后都进行锁升级,于是死锁了

  1. 在 session A rollback 前,session B/C 都因为唯一性冲突申请了 S Next-key lock,但是被 session A 的 X but not gap lock 阻塞;
  2. 在 session A rollbak 后,session B/C 顺利获得 S Next-key lock,并且都要继续进行插入,这时候我认为是因为插入意向锁(LOCK_INSERT_INTENTION)导致的死锁,因为插入意向锁会被
    gap lock 阻塞,造成了相互等待。还没有进入到记录 X lock。

我们说一个insert语句如果要插入一个间隙,而这个间隙上有gap lock的话,insert语句会被堵住,这个被堵住的效果,实现机制上是用插入意向锁和gap lock相互作用来实现的。
gap lock并不属于插入意向锁的一部分

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

醋酸菌HaC

请我喝杯奶茶吧

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值