首页 > 数据库 >MySQL间隙锁,next-key锁

MySQL间隙锁,next-key锁

时间:2024-09-15 21:54:19浏览次数:10  
标签:间隙 18 age 28 next 索引 session key MySQL

在这里插入图片描述

间隙锁

间隙锁是对索引记录之间的间隙的锁,或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如,SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;阻止其他事务将 的值插入15到列中t.c1,无论列 中是否已经存在任何此类值,因为该范围内所有现有值之间的间隙被锁定。

间隙锁的目的

是为了防止幻读,其主要通过两个方面实现这个目的:
(1)防止间隙内有新数据被插入
(2)防止已存在的数据,更新成间隙内的数据

间隙是怎么划分的

id(主键)nameage(普通索引)
1name115
5lucy18
11南风22
20洛神赋28

这个表根据age列(间隙锁作用在索引上,必须要有索引),间隙可以划分为 (-∞, 15),(15,18),(18,22),(22,28),(28,+∞)

间隙锁锁定的区域

根据检索条件向左寻找最靠近检索条件的记录值A,作为左区间,向右寻找最靠近检索条件的记录值B作为右区间,即锁定的间隙为(A,B)。

间隙锁作用范围

1、间隙锁只能作用在RR隔离级别

2、能作用在索引上

补充:

1、使用唯一索引锁定行以搜索唯一行的语句不需要间隙锁定。(这不包括搜索条件仅包含多列唯一索引的某些列的情况;在这种情况下,确实会发生间隙锁定。)

2、不同的事务可以在间隙上持有冲突的锁。例如,事务 A 可以在间隙上持有共享间隙锁(间隙 S 锁),而事务 B 在同一间隙上持有排他间隙锁(间隙 X 锁)。允许冲突间隙锁的原因是,如果从索引中清除记录,则必须合并不同事务在记录上持有的间隙锁。

3、可以明确禁用间隙锁定。将事务隔离级别更改为READ COMMITTED即可

准备数据:

创建表:

CREATE TABLE `user` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT '0' COLLATE 'utf8_general_ci',
	`age` INT(10) NULL DEFAULT '0',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `age` (`age`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=11
;

表数据:

id(主键)nameage(普通索引)
1name115
5lucy18
11南风22
20洛神赋28

普通索引的间隙锁

在普通索引列上,不管是何种查询,只要加锁,都会产生间隙锁,这跟唯一索引不一样;
在普通索引跟唯一索引中,数据间隙的分析,数据行是优先根据普通索引排序,再根据唯一索引排序。

案例1(检索单个值)

开启两个会话,设置会话隔离级别为RR,设置自动提交为0,开启事物测试:

##############session 1

SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

START TRANSACTION;
SELECT * FROM USER WHERE age=22 FOR UPDATE 

############session 2

SET autocommit=0;
SET session transaction isolation level REPEATABLE read;

START TRANSACTION;
INSERT INTO user VALUE(3, 'gap lock', 18) #成功
INSERT INTO user VALUE(6, 'gap lock', 18) #阻塞
INSERT INTO user VALUE(3, 'gap lock', 20) #阻塞
INSERT INTO user VALUE(10, 'gap lock', 22) #阻塞
INSERT INTO user VALUE(14, 'gap lock', 28) #阻塞
INSERT INTO user VALUE(21, 'gap lock', 28) #成功

会话1执行sql: age=22 会锁定间隙[15,18)(18,22), 会话2中 age=[15,22)之间的插入都会失败。2、同样插入age=22,id=10的时候可以成功,id=14的时候就会失败,这说明当索引顺序相同时,会根据主键来排序。对age=18同理

锁定区域示意图:
在这里插入图片描述

案例2 (检索不存在的值)

仍基于原始表数据测试验证:

##############session 1

SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

START TRANSACTION;
SELECT * FROM USER WHERE age=30 FOR UPDATE 

############session 2

SET autocommit=0;
SET session transaction isolation level REPEATABLE read;

START TRANSACTION;
INSERT INTO user VALUE(13, 'gap lock', 28) #阻塞
INSERT INTO user VALUE(14, 'gap lock', 100) #阻塞
INSERT INTO user VALUE(15, 'gap lock', 27) #成功

会话1执行sql:条件 age=30
会锁定间隙[28,正无穷大), 会话2中 age>=28 的插入都会失败

案例3 (检索范围)

仍基于原始表数据测试验证:

##############session 1

SET autocommit=0;
SET session transaction isolation level REPEATABLE READ;

START TRANSACTION;
SELECT * FROM USER WHERE age>=18 AND age<23 FOR UPDATE 

############session 2

SET autocommit=0;
SET session transaction isolation level REPEATABLE read;

START TRANSACTION;
INSERT INTO user VALUE(11, 'select range', 14) #成功
INSERT INTO user VALUE(11, 'select range', 15) #阻塞
INSERT INTO user VALUE(12, 'select range', 17) #阻塞
INSERT INTO user VALUE(13, 'select range', 18) #阻塞
INSERT INTO user VALUE(14, 'select range', 20) #阻塞
INSERT INTO user VALUE(17, 'select range', 23) #阻塞
INSERT INTO user VALUE(18, 'select range', 24) #阻塞
INSERT INTO user VALUE(19, 'select range', 28) #成功

会话1执行sql:条件 age>=18 AND age<23
会锁定age=[15,28)之间的间隙区间, 会话2中 age>=15 and age<28 的插入都会失败。 由于23是22-28之间不存在的记录,所以这个间隙区间也被锁定了

next-key锁

next-key锁是记录锁和间隙锁的组合,mysql默认使用这个锁。
上面的案例一session 1中的sql是:SELECT * FROM USER WHERE age=18 FOR UPDATE; next-key锁锁定的范围为间隙锁+记录锁,首先在(15,18)(18,22)加间隙锁,同时age=18的记录加记录锁。

标签:间隙,18,age,28,next,索引,session,key,MySQL
From: https://blog.csdn.net/qq_36037795/article/details/142252952

相关文章

  • MySQL练手题--体育馆的人流量(困难)
    一、准备工作CreatetableIfNotExistsStadium(idint,visit_dateDATENULL,peopleint);TruncatetableStadium;insertintoStadium(id,visit_date,people)values('1','2017-01-01','10');insertintoStadium(id,visit_date,......
  • mysql事务
    MySQL事务是数据库管理系统(DBMS)中的一项关键功能,确保一系列数据库操作作为一个整体被执行,且具有原子性、一致性、隔离性和持久性(ACID)的特性。事务处理机制可以帮助开发者确保数据的完整性和一致性,特别是在出现错误或并发操作时。1.事务的四大特性(ACID)原子性......
  • mySql 添加新用户
    运行以下SQL语句来创建新用户并设置密码:INSERTINTOmysql.user(User,Host,Password)VALUES('用户名','主机名',PASSWORD('密码'));其中,'用户名'为新用户的名称,     '主机名'为允许该用户连接的主机,可以使用通配符'%'表示允许从任何主机连接,    ......
  • 【MySQL】MySQL索引与事务的透析——(超详解)
    前言......
  • mysql数据怎么导入到帝国cms
    将MySQL数据导入到帝国CMS中通常有两种情况:一种是从现有的MySQL数据库导入数据到帝国CMS的新建数据库中,另一种是从帝国CMS的备份文件恢复数据到现有的帝国CMS数据库中。以下是针对这两种情况的具体步骤:从现有MySQL数据库导入数据到帝国CMS方法一:手动迁移数据导出现有数据库:......
  • VPS Ubuntu22.04 安装WordPress 搭建网站 详细全流程(基于Apache+MySQL+PHP)(二)
    VPSUbuntu22.04安装WordPress搭建网站详细全流程(基于Apache+MySQL+PHP)(二)简介在网站处理和网络管理方面,WordPress是用户可以采取的最明智的选择。由于WordPress的巨大优势,它在网页设计师中广受欢迎。统计数据显示,访问量最大的1000个网站中约有35%是WordPress。......
  • MySQL 大表拆分
    概述在实际工作中,在关系数据库(MySQL、PostgreSQL)的单表数据量上亿后,往往会出现查询和分析变慢甚至无法执行统计分析的情况。这时就需要将大表拆分为多个小表,将小表分布在多个数据库上,形成一个数据库集群。这样的话,一条SQL统计语句就可以在多台服务器上并发执行,然后将执行结果汇......
  • 【MySQL】基础部分——DDL,DML,DQL,DCL,函数,约数,多表查询,事务
    个人学习记录,供以后回顾和复习ubuntu下安装使用1.DDL,DML,DQL,DCLDDL数据库表DML增改删DQL条件查询分组查询排序查询分页查询DCL管理用户权限控制2.函数字符串函数数值函数日期函数流程函数3.约束4.多表查询多表关系内连接外连接自连接联合查询union子查询标量子查询......
  • mysql笔记8(多表查询)
    文章目录1.union联合查询可能会用到去重操作2.innerjoin内连接3.leftjoin左连接4.rightjoin右连接5.crossjoin交叉连接6.naturaljoin自然连接naturalleftjoin自然左连接naturalrightjoin自然右连接自然连接的两张表没有同名字段怎么办?7.using......
  • Mysql 面试题总结
    1.Mysql数据库,隔离级别有哪几个?在MySQL数据库中,事务的隔离级别决定了一个事务在执行期间对其他事务可见的数据变化情况。MySQL支持SQL标准定义的四种隔离级别,从低到高依次为:读未提交(READUNCOMMITTED)在该隔离级别下,事务中的修改即使没有提交,对其他事务也是可见的。......