mysql InnoDB引擎各种隔离级别的加锁机制

2024-08-23 19:44

本文主要是介绍mysql InnoDB引擎各种隔离级别的加锁机制,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

    • 概要
    • 前置知识了解
    • 各种隔离锁的验证
    • 小结

概要

我们都知道,mysql的InnoDB引擎在各种隔离级别下的加锁机制都是有差异的,但是对于各种隔离级别下如何加锁大家可能不太了解,今天我就通过一篇文章去带领大家去分析一下各个隔离级别的加锁过程,如果有误,欢迎大家在评论区指正!

今天实验的表结构定义如下,欢迎大家自己去尝试验证。我的mysql版本是5.7。其中id加上了唯一索引为了验证行锁的加锁过程。age加上非唯一索引,给大家演示一下gap的加锁过程。:

-- 学生表,id,姓名,年纪三个字段。
CREATE TABLE `t_stu` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '商品ID',`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '会员卡名称',`age` int NOT NULL,PRIMARY KEY (`id`) USING BTREE,KEY `name` (`name`),KEY `age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8mb3 COMMENT='会员商品表';INSERT INTO `t_stu` (`id`, `name`, `age`) VALUES (1, '小张', 1);
INSERT INTO `t_stu` (`id`, `name`, `age`) VALUES (3, '小王', 3);
INSERT INTO `t_stu` (`id`, `name`, `age`) VALUES (4, '小美', 4);
INSERT INTO `t_stu` (`id`, `name`, `age`) VALUES (44, '小美', 44);

前置知识了解

读锁和写锁的互斥情况,只有读锁和读锁可以并行执行,其他的情况都是互斥的。

读锁写锁
读锁x
写锁xx
  • mysql的表级锁有哪些,其中我们需要重点关注的就是元数据锁:
    1. 表锁:通常意义上我们是不会给表主动加锁。通过 LOCK TABLES t_stu READ,LOCK TABLES t_stu WRITE去加锁。通过unlock tables去解锁。在实际的业务过程中题主暂时未遇到过使用场景,所以这个锁不在我们的讨论范围。
    2. 元数据锁(MDL):这个锁我们需要关注一下,我们在表结构变更时会加一个MDL写锁,就是当我们操作表做DDL语句时,例如:给表加字段,给表加索引。当我们给做crud语句时会给表加MDL读锁。所以在高并发的场景下,不可以去对表做DDL操作,会锁表。 因为DDL语句不会被事物控制,所以对于小批量的数据题主没办法演示加锁情况,大家可以自行多加点数据去做演示。
    3. 意向锁:题主没关注这个。
    4. AUTO-INC 锁:主键索引自增时加的一个锁。

  • mysql的行锁有哪些:
    1. 行级锁: 加在索引上的锁,如果表上没有任何索引,InnoDB会使用一个内部的行ID来锁定行。在RR隔离级别下,我们在没有索引的表上去修改数据,其实是锁定了所有的行+加上行的间隙,并不是锁表。
    2. 间隙锁 GAP LOCK: 间隙锁是RR隔离级别下加在非唯一索引的两个数据之间的锁,注意是非唯一索引,理论上我们对主键索引去修改数据时是不会加间隙锁的。另外一种情况就是where范围查询时,锁定主键索引的区间。注意间隙锁只有在RR隔离级别下才存在。
    3. next-key lock: 行级锁+间隙锁组成的锁。

各种隔离锁的验证

  • 读未提交 (READ UNCOMMITTED): 按照顺序执行,我们会发现事物2在执行到第四步时被阻塞了,说明在读未提交的情况下是有行级锁存在的。并不是网上说的行级锁只存在于读已提交和可重复读的隔离级别。
-- 事物1 ----------------------------------------------------------
-- 1
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
-- 3
UPDATE `t_stu` SET `name` = 'aa' WHERE `id` = 1;
COMMIT;-- 事物2 ----------------------------------------------------
-- 2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
-- 4
UPDATE `t_stu` SET `name` = 'aaa' WHERE `id` = 1;
COMMIT;

通过命令查看加锁状态,可以看到数据都被加了X锁,就是排它锁。
在这里插入图片描述
在这里插入图片描述

  • 读已提交 (READ COMMITTED):
-- 事物1 ----------------------------------------------------------
-- 1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- 3
-- UPDATE `t_stu` SET `name` = 'aa' WHERE `id` = 1;
SELECT * FROM `t_stu` WHERE age = 4 LOCK IN SHARE MODE;
-- 5
SELECT * FROM `t_stu` WHERE age = 4 LOCK IN SHARE MODE;
-- 7
COMMIT;-- 事物2 ----------------------------------------------------
-- 2
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- 4
INSERT INTO `jl`.`t_stu` (`id`, `name`, `age`) VALUES (10, '小小美', 4);
-- 6
COMMIT;

注意这块的查询语句我加上了S共享锁,因为mysql默认查询时SELECT语句是默认不加锁的,所以为了方便演示我加上了读共享锁。上述语句我们在执行时,是能成功的插入数据的,通过锁分析也可得知在RC隔离级别下,对于普通索引的数据我们只加了行级锁,但是没有加gap lock的。当我们执行到步骤5,第六步未执行的情况下,我们看一下加锁状态,写请求加了X排它锁,读数据我加了共享锁。所以从分析就可以得知在RC隔离级别下,就算是普通的非唯一索引age,也不会加gap锁。

在这里插入图片描述

  • 可重复读 (REPEATABLE READ):
-- 事物1 ----------------------------------------------------------
-- 1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 3
-- UPDATE `t_stu` SET `name` = 'aa' WHERE `id` = 1;
SELECT * FROM `t_stu` WHERE age = 4 LOCK IN SHARE MODE;
-- 5
SELECT * FROM `t_stu` WHERE age = 4 LOCK IN SHARE MODE;
-- 7
COMMIT;-- 事物2 ----------------------------------------------------
-- 2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 4
INSERT INTO `jl`.`t_stu` (`id`, `name`, `age`) VALUES (10, '小小美', 4);
-- 6
COMMIT;

当我们在执行步骤4时其实就会被阻塞了,数据是没办法插入成功的,因为此时在age的的非唯一索引上加了age=4的数据的行级别锁,在非主键键索引上在age(3,4)(4,44)之间加了gap lock。导致了数据插入时就会锁冲突,没办法进行数据插入。
在这里插入图片描述
我们在演示一个其他的例子,我们把上面事物2的插入的id为10的age数据改成64。先看一数据库的数据:
在这里插入图片描述

-- 事物2 ----------------------------------------------------
-- 2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 4
INSERT INTO `jl`.`t_stu` (`id`, `name`, `age`) VALUES (10, '小小美', 64);
-- 4
-- 6
COMMIT;

最终发现,执行到步骤4时,数据是可以插入成功的,那就是因为,我们age为64的数据不在gap锁锁定age的范围(3,4),(4,44)范围内。

小结

通过上面的各种的案例演示,给大家展示了各种隔离级别下的数据的加锁状态。可能有些知识点是打破可大家对于加锁状态的认知的,大家可以通过本地数据库去模拟一下各种隔离级别的数据加锁状态,会加深大家对于这些知识的认知:行锁什么时候加,gap锁什么时候加,gap锁的锁定范围这些

这篇关于mysql InnoDB引擎各种隔离级别的加锁机制的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1100326

相关文章

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

java中反射(Reflection)机制举例详解

《java中反射(Reflection)机制举例详解》Java中的反射机制是指Java程序在运行期间可以获取到一个对象的全部信息,:本文主要介绍java中反射(Reflection)机制的相关资料... 目录一、什么是反射?二、反射的用途三、获取Class对象四、Class类型的对象使用场景1五、Class

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是