mysql中insert … select锁范围

1、执行 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
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。

2、insert select加了什么锁,唯一索引查询出现死锁场景

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

3. insert into … on duplicate key update怎么加锁

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

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

索引 c 上 (5,10] 加一个排他的 next-key lock(写锁)。

4. 总结

  • insert … select 是很常见的在两个表之间拷贝数据的方法。
  • 在可重复读隔离级别下,这个语句会给 select 的表里扫描到的记录和间隙加读锁。
  • 而如果 insert 和 select 的对象是同一个表,则有可能会造成循环写入。这种情况下,我们需要引入用户临时表来做优化。
  • insert 语句如果出现唯一键冲突,会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此,碰到由于唯一键约束导致报错后,要尽快提交或回滚事务,避免加锁时间过长。
  • 如果在 insert … select 执行期间有其他线程操作原表,会导致逻辑错误。其实,这是不会的,如果不加锁,就是快照读。一条语句执行期间,它的一致性视图是不会修改的,所以即使有其他事务修改了原表的数据,也不会影响这条语句看到的数据。

相关推荐

  1. MySQL

    2024-03-13 01:26:01       29 阅读
  2. mysql数据类型与取值范围

    2024-03-13 01:26:01       42 阅读
  3. MySQL(一)

    2024-03-13 01:26:01       42 阅读

最近更新

  1. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-03-13 01:26:01       94 阅读
  2. Could not load dynamic library ‘cudart64_100.dll‘

    2024-03-13 01:26:01       100 阅读
  3. 在Django里面运行非项目文件

    2024-03-13 01:26:01       82 阅读
  4. Python语言-面向对象

    2024-03-13 01:26:01       91 阅读

热门阅读

  1. PTA L1-009 N个数求和(C++)

    2024-03-13 01:26:01       39 阅读
  2. url中可以包含@吗

    2024-03-13 01:26:01       39 阅读
  3. docker学习笔记——对数据卷的一些简单命令

    2024-03-13 01:26:01       43 阅读
  4. Activiti工作流引擎:流程实例名称模糊查询

    2024-03-13 01:26:01       45 阅读
  5. react hook:useMemo

    2024-03-13 01:26:01       44 阅读
  6. linux设置开机启动慎用nohup

    2024-03-13 01:26:01       43 阅读
  7. PyTorch学习笔记(三)

    2024-03-13 01:26:01       39 阅读
  8. 程序员如何选择职业赛道?

    2024-03-13 01:26:01       53 阅读