代码编织梦想


MySQL45讲

实践篇

40 | 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

在这里插入图片描述
如果 session B 先执行,由于这个语句对表 t 主键索引加了 (-∞,1]这个 next-key lock,会在语句执行完成后,才允许 session A 的 insert 语句执行。
但如果没有锁的话,就可能出现 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;

这个语句到了备库执行,就会把 id=-1 这一行也写到表 t2 中,出现主备不一致。

insert 循环写入

示例 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。
在这里插入图片描述
示例 2:

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

在这里插入图片描述
在这里插入图片描述
explain 结果里的 rows=1 是因为受到了 limit 1 的影响。

执行这个语句前后查看 Innodb_rows_read 的结果。
在这里插入图片描述
这个语句执行前后,Innodb_rows_read 的值增加了 4。因为默认临时表使用 Memory 引擎,所以这 4 行查的都是表 t,也就是说对表 t 做了全表扫描。

示例 2 的执行流程如下:

  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。所以,这个语句执行期间,其他事务不能在这个表上插入数据。

这个语句的执行需要临时表,是因为这类一边遍历数据,一边更新数据的情况,如果读出来的数据直接写回原表,就可能在遍历过程中,读到刚刚插入的记录,新插入的记录如果参与计算逻辑,就跟语义不符。

由于这个语句没有在子查询中就直接使用 limit 1,从而导致了这个语句的执行需要遍历整个表 t。
可以考虑使用内存临时表来做这个优化。使用内存临时表优化时,语句序列的写法如下:

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 唯一键冲突

在这里插入图片描述
session A 执行的 insert 语句,发生唯一键冲突的时候,并不只是简单地报错返回,还在冲突的索引上加了锁。 一个 next-key lock 就是由它右边界的值定义的。这时候,session A 持有索引 c 上的 (5,10]共享 next-key lock(读锁),从作用上来看,这样做可以避免这一行被别的事务删掉。

如果冲突的是主键索引,就加记录锁,唯一索引才加 next-key lock。MySQL 8.0.16 及之后修改了这个bug,这两类索引冲突加的都是 next-key lock。

经典的死锁场景
在这里插入图片描述
死锁产生的逻辑如下:

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

在这里插入图片描述
上面这个例子是主键冲突后直接报错,如果是改写成以下语句:

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

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

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

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

在这里插入图片描述
实际上,真正更新的只有一行,只是在代码实现上,insert 和 update 都认为自己成功了,update 计数加了 1, insert 计数也加了 1。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/TQ20160412/article/details/122427118

为什么insert操作会把整张表都锁住呢?-爱代码爱编程

为什么insert操作会把整张表都锁住呢? spring oracle 锁表 项目中遇到一个很奇怪的问题: 问题描述:现在需要从项目外部导大量的数据到项目内,这个时候我起了一个spring事务往很多张表中去插入数据,这个时候其他用户访问系统去更新旧的数据的时候发

mysql insert into select锁表的问题(上)_llliarby的博客-爱代码爱编程_insert into select 锁表

在MySQL中使用insert into table1 select * from table2时,会对table2进行加锁,这个加锁分三种情况: 确认实验条件: select @@global.tx_isolation,@@session.tx_isolation; 查询结果如下: @@global.tx_isolation@@sessi

insert delete 语句卡死,oracle数据库锁表解决。_小小i程序员的博客-爱代码爱编程

delete table XXX where ……,卡死。 select * from XXX for update 的时候没有commit,oracle将该记录锁住了。 先查询锁定记录 Sql代码 SELECT

insert 语句加锁机制_普通网友的博客-爱代码爱编程

一 前言 之前的文章里面总结了很多死锁案例,其实里面有几篇文章对于insert加锁流程表述的不准确,而且微信公众号又无法修改,所以通过本文重新梳理insert加锁流程,最后加上一个死锁案例解析。 有个勤奋好学的同事特地

oracle执行update或insert被锁-爱代码爱编程

使用Oracle执行update或insert语句后,会要求commit,如果没有commit就关掉了连接,这条记录就会被锁,导致后面的update或insert无法执行。 此现象常出现的场景: 1.在执行updat

Oracle 表被锁无法执行INSERT 、UPDATE语句-爱代码爱编程

开发的时候debug到一条update的sql语句时程序就不动了,然后我就在plsql上试了一下,发现plsql一直在显示正在执行,等了好久也不出结果。但是奇怪的是执行其他的select语句却是可以执行的。 原因和解决方法 这种只有update无法执行其他语句可以执行的其实是因为记录锁导致的,在oracle中,执行了update或者insert语句后,

39讲自增主键为什么不是连续的-爱代码爱编程

在第4篇文章中,我们提到过自增主键,由于自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,因此索引更紧凑。 之前我见过有的业务设计依赖于自增主键的连续性,也就是说,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不能保证连续递增。 今天这篇文章,我们就来说说这个问题,看看什么情况下自增主键会出现 “空洞”? 为了便于说

insert select 组合语句-爱代码爱编程

1.INSERT INTO SELECT语句 语句形式为:Insert into Table2(field1,field2,…) select value1,value2,… from Table1 [where column =value][]为可选内容要求目标表Table2必须在由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,

insert into select语句锁表故障-爱代码爱编程

深入研究insert into select语句锁表故障(上) 故障描述 前几天,一个mysql数据库运维同事,在生产上用insert into select * from语句,在生产上备份了一张表,结果将备份表全表锁住了,业务影响了大约10分钟。 看到这个语句,我第一反应就是select语句也能锁表,可是生产上的故障,证明确实锁表了。所以,需要将in

insert会锁表吗mysql_MySQL的insert into select 引发锁表-爱代码爱编程

又是被大佬嫌弃的一天,为了不卷铺盖走人,我决定去学习一下表备份的常见方法。 MySQL一般我们在生产上备份数据通常会用到 这两种方法:INSERT INTO SELECT CREATE TABLE AS SELECT注:本文仅针对MySQL innodb引擎,事务是可重复读RR,数据库版本为5.5 1.INSERT INTO SELECT

mysql高并发insert_高并发insert语句的解决方法-爱代码爱编程

前言 1、防止数据多次修改 1.1 、insert方案 1、添加uniqpue进行解决(重复则是更新) insert一般没什么问题,直接控制好unique就可以,这样的话,就不会插入两条(如果重复了则,进行更新操作) 2、update方案 1、redis分布式锁、消息队列(每次只插入一个) 2、mysql锁(更新可以使用乐观锁) 2、高

21 | 为什么我只改一行的语句,锁这么多?-爱代码爱编程

MySQL45讲 实践篇 21 | 为什么我只改一行的语句,锁这么多? 间隙锁在可重复读隔离级别下才有效。 next-key lock 加锁规则 两个“原则”、两个“优化”和一个“bug” 原则 1:加锁的基本单位是 next-key lock。原则 2:查找过程中访问到的对象才会加锁。优化 1:索引上的等值查询,给唯一索引加锁的时候,next