本文对 MySQL 事务隔离级别及其常见问题进行了分析,同时记录了 InnoDB 是如何通过间隙锁来解决幻读的,最后还分析了为什么大部分业务事务隔离级别会使用读已提交级别。

1. 常见问题

在不考虑事务隔离级别的情况下,DB 操作可能出现以下几个问题:

  • 1)脏读:事务 A 读取了事务 B 更新的数据,然后 事务B 进行回滚操作,那么 事务 A 读取到的数据就是脏数据。
    • 指一个事务读取到了另外事务中未提交的数据。
  • 2)不可重复读:事务 A 多次读取同一数据,事务 B 在事务 A 读取的过程中,对数据作了更新并提交,导致事务 A 多次读取同一数据时,结果不一致。
    • 指一个事务读取到了事务中提交的 update 的数据。
  • 3)幻读:系统管理员 A 将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候新增了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。
    • 指一个事务读取到了事务中提交的 insert 的数据。

不可重复读和幻读最大的区别,一者是对已存在的行进行操作导致,一者是对不存在的行进行操作导致。

2. 如何解决

ISO 和 ANIS SQL 提供了4 种事务隔离级别的标准。

  • read-uncommitted
  • read-committed
  • repeatable-read
  • serializable

为了解决这些问题,InnoDB 存储引擎同样提供了 4 种事务隔离级别。

事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted)
读已提交(read-committed)
可重复读(repeatable-read) 对InnoDB否
串行化(serializable)

1. 脏读

将事务隔离级别提升到读已提交(read-committed)即可限制,禁止其他事务访问当前事务未提交的数据,这样就不会出现脏读的情况。

2. 不可重复读

将事务隔离级别提升到可重复读(repeatable-read)即可。

该事务隔离级别下,每次开启事务都会新建一个快照,在当前事务中的多次查询都是基于此快照进行的,不会查询到其他事务提交的数据,所以也不会出现 不可重复读的问题。

3. 幻读

这个就比较复杂了,只有 InnoDB 存储引擎下把事务隔离级别调到可重复读(repeatable-read)才能限制该问题。

幻读与不可重复读的区别:不可重复读是对当前已存在的数据进行更新,导致后续的查询与之前的查询结果不一致,而幻读是新增了满足当前查询条件的行,导致前后查询结果不一致。

解决不可重复读只需要每次将满足条件的行,加锁即可。

当是幻读则不能通过该方式解决,因为当前行都不存在怎么加锁。

于是 MySQL 中为了解决这个问题,新增了一种锁:间隙锁(Gap Lock)。

3. 间隙锁(Gap Lock)

间隙锁是 InnoDB 行锁中的一种。

产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的间隙。因此,为了解决幻读问题,mysql InnoDB 只好引入新的锁,也就是间隙锁 (GapLock)。间隙锁,锁的就是两个值之间的空隙,因此间隙锁只与往间隙里写入记录这个操作冲突 。值得注意的是,间隙锁只在隔离级别是 可重复读隔离级别下才会生效。

  • 1)行锁(Record Lock):锁直接加在索引记录上面。
  • 2)间隙锁(Gap Lock):锁加在不存在的空闲空间,可以是两个索引记录之间,也可能是第一个索引记录之前或者最后一个索引之后的空间,两边都是开区间
  • 3)Next-Key Lock:行锁与间隙锁组合起来用就叫做 Next-Key Lock,是一个前开后闭区间

默认情况下,InnoDB工作在 重复读(repeatable-read)的隔离情况下,并且以 Next-Key Lock 的方式对数据进行加锁,这样就可以有效地防止幻读的发生。

Next-key Lock 是行锁与间隙锁的组合,这样,当 InnoDB 扫描索引记录的时候,会首先对选中的索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。如果一个间隙被事务 A 加了锁,其他事务是不能在这个间隙插入记录的。

4. 加锁规则

1. 概述

  • 1)原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。
  • 2)原则 2:查找过程中访问到的对象才会加锁。
  • 3)优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
  • 4)优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock 退化为间隙锁。
  • 5)一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

MySQL 后面的版本可能会改变加锁策略,所以这个规则只限于截止到现在的最新版本,即 5.x 系列 <=5.7.24,8.0 系列 <=8.0.13。

建表语句和初始化如下:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

2. 等值查询间隙锁

sessionA sessionB sessionC
begin;
update t set d = d+1 where id = 7;
insert into t values(8,8,8);(blocked)
update t set d = d+1 where id =10;(Query OK)

由于表 t 中没有 id=7 的记录,所以用我们上面提到的加锁规则判断一下的话:

  • 1)根据原则 1,加锁单位是 next-key lock,session A 加锁范围就是 (5,10];
  • 2)同时根据优化 2,这是一个等值查询 (id=7),而 id=10 不满足查询条件,next-key lock 退化成间隙锁,因此最终加锁的范围是 (5,10)。

所以,session B 要往这个间隙里面插入 id=8 的记录会被锁住,但是 session C 修改 id=10 这行是可以的

3. 非唯一索引等值锁

sessionA sessionB sessionC
begin;
select id from t where c =5 lock in share mode;
update t set d = d+1 where id =5;
insert into t values(7,7,7);(blocked)

这里 session A 要给索引 c 上 c=5 的这一行加上读锁。

  • 1)根据原则 1,加锁单位是 next-key lock,因此会给 (0,5]加上 next-key lock。要注意 c 是普通索引,因此仅访问 c=5 这一条记录是不能马上停下来的,需要向右遍历,查到 c=10 才放弃。
  • 2)根据原则 2,访问到的都要加锁,因此要给 (5,10]加 next-key lock。
  • 3)但是同时这个符合优化 2:等值判断,向右遍历,最后一个值不满足 c=5 这个等值条件,因此退化成间隙锁 (5,10)。
  • 4)根据原则 2 ,只有访问到的对象才会加锁,这个查询使用覆盖索引,并不需要访问主键索引,所以主键索引上没有加任何锁,这就是为什么 session B 的 update 语句可以执行完成。

但 session C 要插入一个 (7,7,7) 的记录,就会被 session A 的间隙锁 (5,10) 锁住。

5. 间隙锁带来的问题

1. 死锁 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

间隙锁的引入可能会导致死锁。前面提到了间隙锁只与往间隙里写入记录这个操作冲突。同一个间隙多次加锁是不会冲突的,于是问题来了。

SessionA SessionB
begin;
select * from t where id = 9 for update;
begin;
select * from t where id = 9 for update;
insert into values(9,9,9);(blocked)
insert into values(9,9,9);(ERROR Deadlock found)

分析如下:

  • 1)session A 执行 select … for update 语句,由于 id=9 这一行并不存在,因此会加上间隙锁 (5,10);
  • 2)session B 执行 select … for update 语句,同样会加上间隙锁 (5,10),间隙锁之间不会冲突,因此这个语句可以执行成功;
  • 3)session B 试图插入一行 (9,9,9),被 session A 的间隙锁挡住了,只好进入等待;
  • 4)session A 试图插入一行 (9,9,9),被 session B 的间隙锁挡住了。

至此,两个 session 进入互相等待状态,形成死锁。当然,InnoDB 的死锁检测马上就发现了这对死锁关系,让 session A 的 insert 语句报错返回了。

2. 死锁 2

sessionA sessionB
begin;
select id from t where c= 10 lock in share mode;
update t set d= d+1 where c = 10;(blocked)
insert into t values(8,8,8);
ERROR Deadlock found when trying to get lock;try restaring transaction.

现在,我们按时间顺序来分析一下为什么是这样的结果。

  • 1)session A 启动事务后执行查询语句加 lock in share mode,在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15);
  • 2)session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ,进入锁等待;然后 session A 要再插入 (8,8,8) 这一行,被 session B 的间隙锁锁住。由于出现了死锁,InnoDB 让 session B 回滚。

你可能会问,session B 的 next-key lock 不是还没申请成功吗?其实是这样的,session B 的“加 next-key lock(5,10] ”操作,实际上分成了两步:

  • 1)先是加 (5,10) 的间隙锁,加锁成功;
  • 2)然后加 c=10 的行锁,这时候才被锁住的。

也就是说,我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道,具体执行的时候,是要分成间隙锁和行锁两段来执行的。

6. 小结

本文对 MySQL 事务隔离级别及其常见问题进行了分析,同时记录了 InnoDB 是如何解决幻读的。

最后一个问题,既然间隙锁能解决其他锁都解决不了的问题(幻读),那么为什么大部分业务还是使用 读提交事务隔离级别呢(只有在可重复读级别下间隙锁才生效)?

  • 1)间隙锁的引入可能会出现死锁。
  • 2)间隙锁的引入,可能会导致同样的语句锁住更大的范围,对并发度有一定影响。
  • 3)在读提交隔离级别下,锁的范围更小,锁的时间更短。
    • 因为读提交隔离级别下有一个优化:语句执行过程中加上的行锁,在语句执行完成后,就要把“不满足条件的行”上的行锁直接释放了,不需要等到事务提交

所以大部分业务都使用的读提交(Read Commit)级别,同时为了解决可能出现的数据和日志不一致问题,需要把 binlog 格式设置为 row

7. 参考

  • 《Mysql技术内幕InnoDB存储引擎》
  • 极客专栏-<MySQL 实战45讲>