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

晓龙的Coding之路

已于 2024-10-15 21:09:14 修改

阅读量2.3k

点赞数 72

文章标签： mysql 数据库加锁分析

于 2024-08-22 12:25:59 首次发布

本文链接：https://blog.csdn.net/m0_37903574/article/details/141422010

版权

文章目录

概要

我们都知道，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
写锁	x	x

mysql的表级锁有哪些，其中我们需要重点关注的就是元数据锁：
1. 表锁：通常意义上我们是不会给表主动加锁。通过 LOCK TABLES t_stu READ，LOCK TABLES t_stu WRITE去加锁。通过unlock tables去解锁。在实际的业务过程中题主暂时未遇到过使用场景，所以这个锁不在我们的讨论范围。
2. 元数据锁（MDL）：这个锁我们需要关注一下，我们在表结构变更时会加一个MDL写锁，就是当我们操作表做DDL语句时，例如：给表加字段，给表加索引。当我们给做crud语句时会给表加MDL读锁。所以在高并发的场景下，不可以去对表做DDL操作，会锁表。（可以通过 online DDL进行ddl和dml的并行操作） 因为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;