insert 语句的加锁

Posted on | Edited on | In ,

insert…select为什么要对目标表加锁

测试语句如下，注：隔离模式是RR，binlog_format=statement

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

执行如下的语句：

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

这条语句是给表t所有的字段和间隙加锁，原因是考虑到数据的一致性例如下面的情况:

avator

SessionA和SessionB同时执行，SessionB会给索引c的(-supernum,-1]加上nextkey-lock，原因是如果没有这个锁，很可能主库上B先执行，A后执行，但是binlog上回是A先执行，B后执行，导致从库会出现-1的值，造成主备不一致

当然了，目标表的锁其实是给涉及到的字段加锁，详见前面的加锁原理

见下面俩种情况：

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

这条语句实际上会对表t中的索引c(3,4]和(4,supernum)加锁。扫描行数只有2行

但是下面的语句就不是很好了,把表t最大值+1插入会原表

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

原因是：

  • 在一张表中操作，mysql为了select时候读到insert的值，需要将select的结果导入到临时表中
  • 临时表是memory类型的表，因为有limit 1，需要将表里的数据都导入到临时表中，排序取第一条
  • 所以需要全表扫描，并且对全表所有的行加锁。

优化的方法是：采用临时表来解决语句如下

1
2
3
4
create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t  (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

insert违反唯一约束加锁

见下图：
avator

SessionA在第2次插入时候违反了唯一约束，这时候SessionB的语句并没有因为SessionA的insert失败开始执行反而是block住。原因是SessionA在后滚之前为冲突的索引(5,10]加了nextkey-lock读锁。

在并发情况下也会引起死锁，如图：
avator

原因是：

  1. SessaionA为记录加行锁c=5。（唯一索引等值查询是行锁）
  2. T2时刻SessionB和SessionC都对c=5加了读锁。（违反唯一约束）这时候都是等待状态
  3. T3时刻SessionArollback，
    1. SessionB和SessionCD的读锁生效；
    2. SessionB和SessionC要继续执行插入操作，这时候要对数据加写锁，这时候需要等待对方释放读锁，循环等待发生死锁。

见图:
avator
解决方法

  1. 事务中唯一约束冲突后要尽快提交或者回滚，防止并发写循环等待；
  2. 采用insert into…duplicate key update的方式，因为这时候会加一个nextkey-lock写锁是互斥的。这样sessionB和sessionC不会循环等待。