mysql隔离级别与锁

1. 环境准备

本文以mysql8.0版本为例

2. 过程

本文主要以读未提交, 读已提交, 可重复读三种隔离级别来展示mysql的各种锁及索引对锁的影响

3. 表准备

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int NOT NULL,
  `card` varchar(255) NOT NULL,
  `sign` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_card` (`card`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

id : 主键索引, 自增

card : 唯一索引

name : 普通索引

sessionA与sessionB

4. 隔离级别 : 读未提交

4.1 定义

question : 读未提交隔离级别的特点是什么? 

现在存在两个会话, sessionA,sessionB. 同时开启事务, sessionB对数据的修改, 在事务不提交的情况下, sessionA也可以查询到sessionB修改的数据

• 因此读未提交的隔离级别不能解决脏读问题

示例 : 

 4.2 索引对锁的影响

 

 根据主键索引为条件对数据进行加锁, 锁的只是主键值这一行, 是行锁, 对其他行数据的修改不会进行阻塞

 

  根据唯一索引为条件对数据进行加锁, 锁的只是唯一索引这一行, 是行锁, 对其他行数据的修改不会进行阻塞, 同主键索引相差不大

插入一条 name = "hctrl"的数据行

根据普通索引对数据进行上锁, 实际上是对与普通索引值相同的所有行进行上锁

(即 对根据普通索引查询出来的所有行进行上锁) 

 根据没有索引的字段进行上锁查询, 是对查询出来的数据行进行上锁, 也是行锁

(即 对根据没有索引字段查询出来的所有行进行上锁)

4.3 是否存在间隙锁?

 不存在间隙锁

5. 隔离级别 : 读已提交

5.1 定义

question : 读已提交的特点是什么?

现在存在两个会话sessionA, sessionB. sessionB对数据的修改在不提交的情况下sessionA无法发现, 如果sessionB将事务提交, sessionA可以发现. 如果sessionB对数据再一次进行修改, 则sessionA两次读取到的数据可能不相同

现在存在两个会话sessionA, sessionB, sessionB添加一条数据, 在不提交事务的情况下, sessionA也可以读到这条数据

• 读已提交无法解决不可重复读问题

5.2 索引对锁的影响

与读未提交完全相同

5.3 是否存在间隙锁?

不存在

6. 隔离级别 : 可重复读

6.1 定义

question : 可重复读的特点是什么?

现在存在两个会话sessionA, sessionB, sessionB对数据进行修改, 并且提交事务. 此时sessionA无法发现sessionB修改的数据, 只有sessionA本身事务提交之后, 才可以发现修改的数据

• 可重复读不能完全的解决幻读问题

 6.2 索引对锁的影响

 根据主键索引为条件对数据进行加锁, 锁的只是主键值这一行, 是行锁, 对其他行数据的修改不会进行阻塞 (同读未提交, 读已提交相同) 

 根据唯一索引为条件对数据进行加锁, 锁的只是唯一索引这一行, 是行锁, 对其他行数据的修改不会进行阻塞, 同主键索引相差不大  (同读未提交, 读已提交相同) 

根据普通索引对数据进行上锁, 实际上是对与普通索引值相同的所有行进行上锁

question为什么第二个update阻塞, 第三个update通过呢?

因为根据普通索引的查询条件就是name = "hctrl", 如果我们把其他行的name修改成为hctrl, 就会出现幻读问题, 此处是可重复读解决了幻读问题

 根据没有索引的字段进行上锁查询, 对整个表上锁, 是表锁

question为什么会全部阻塞呢?

因为是对没有索引的字段age = 20进行查询, 因此如果可以将任何数据的age修改成20, 因此为了防止此现象的发生, 对全表进行锁定

6.3 是否存在间隙锁? 

存在

 根据数据可以知道, 存在的间隙是(5,10),(10,15),(17,正无穷)

 因为查询的条件是id > 7 and id < 13, 所以id在(5,10),(10,15)这两个区间的id都不能进行插入

6.4 解决幻读问题 

question : 为什么说读可重复读解决了部分幻读问题呢?

就像6.2中, 普通索引和非索引字段插入/修改数据被阻塞, 就是为了解决幻读问题, 6.3中的间隙锁也是为了解决幻读问题, 同时还存在这种情况,是没有能解决的

7. 总结

• 只有在可重复读的隔离级别下存在间隙锁
• 可重复读的隔离级别下对普通索引进行加输查询, 极端情况下会发生表锁
• 可重复读的隔离级别下对非索引字段进行加锁查询会锁全表