Notes

Exploring note

0%

insert语句加的锁

发表于 更新于 分类于
本文字数： 3.7k 阅读时长 ≈ 6 分钟
insert语句中的锁

普通的insert语句是一个很轻量的操作；有些特殊情况的insert语句，在执行过程中需要给其他资源加锁，无法在申请到自增id以后立马释放自增锁。

insert…select语句

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

可重复读隔离级别，binlog_format为statement，执行insert into t2(c,d) select c,d from t;时，需要对表t的所有行和间隙加锁。

sessionA sessionB
insert into values(-1,-1,-1); insert into t2(c,d) select c,d from t;

如果sessionB先执行，由于语句对表t主键索引加了(-∞,1]这个next-key lock，会在语句执行完成后，才允许sessionA的insert语句执行。

如果没有锁的话，可能出现sessionB的insert语句先执行，但是后写入binlog的情况(日志和数据不一致)。到备库执行，就会把id=-1这一行也写到表t2中，出现主备不一致。

insert循环写入

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

示例1

往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1。

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。

慢查询日志slow log：{Query_time:0.000732 Lock_time:0.000356 Rows_sent:0 Rows_examined:1}

示例2

将数据插入表t。(同一个表，可能造成循环写入，引入用户临时表做优化)

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

慢查询日志slow log：{Query_time:0.000478 Lock_time:0.000128 Rows_sent:0 Rows_examined:5}。扫描行数5。

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 insert t null all null null null null null null null
1 simple t null index null c 5 null 1 100.00 Using temporary

Extra字段中Using temporary，表示这个语句用到了临时表。表示执行过程中，需要把表t的内容读出来，写入临时表。rows=1是受到了limit 1的影响。

执行语句前后查看innode_rows_read的结果：show status like ‘%Innodb_rows_read%’;增加了4。临时表默认使用Memory引擎，这4行查的都是表t，表示对表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上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以这个语句执行期间，其他事务不能在这个表上插入数据。

这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符。（可能造成循环写入，所以需要临时表Using temporary）

由于在实现上，这个语句没有在子查询中就直接使用limit 1，导致了这个语句的执行需要遍历整个表t（？？？）。优化方法，先insert into到临时表temp_t(数据量小可以使用内存临时表)，这样就只需要扫描一行；然后再从表temp_t里面取出这行数据插入表t1。 

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唯一键冲突-会在冲突的唯一值上加共享的next-key lock(S锁)

sessionA sessionB
insert into values(10,10,10);
begin;
insert into values(11,10,10);
(Duplicate entry ‘10’ for key ‘c’)
insert into t values(12,9,9);

可重复读隔离级别下，sessionB要执行的insert语句进入了锁等待状态。

sessionA执行insert语句，发生唯一键冲突的时候，并不是简单的报错返回，还在冲突的索引上加了锁(在要写入的时候，发现有冲突，才会加上next-key lock的锁)。

一个next-key lock是由它右边界的值定义的，这时sessionA持有索引c上的(5,10]共享next-key lock(读锁)（从作用上来看，可以避免这一行被别的事务删掉）。因此碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长，导致后续出现死锁。

gap lock无所谓S还是X的，主要解决幻读问题，不管属于读锁还是写锁，都会阻塞其他事务的写请求。gap lock+排他的record就称作排他的next-key lock。

  • select…lock in share mode是共享锁；
  • select…for update和IUD语句是排它锁。

    复习：next-key lock由间隙锁gap lock和记录锁record lock组成。加锁单位是next-key lock。

主键索引和唯一索引冲突，加的都是next-key lock(间隙锁+记录锁)。(官方文档主键索引加记录锁，唯一索引加next-key lock)

死锁场景

sessionA sessionB sessionC
T1 begin;
insert into t values(null,5,5);
T2 insert into t values(null,5,5); insert into t values(null,5,5);
T3 rollback; (DeadLock found)

sessionA执行rollback语句回滚的时候，sessionC几乎同时发现死锁并返回。

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

    插入意向锁Lock_insert_intention会被间隙锁gap lock阻塞，造成相互等待。

insert into…on duplicate key update

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

insert唯一键冲突示例中主键冲突后直接报错，而该例子主键冲突后会给索引c上(5,10]加一个排他的next-key lock(写锁)。

insert into…on duplicate key update这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。

如果有多个列违反了唯一键约束，就会按照索引的顺序，修改跟第一个索引冲突的行。

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