MySQL 四种隔离级别 & 间隙锁

一、事务的四个特征（ACID）

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

1. 原子性 (Atomicity)

事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

2. 一致性 (Consistency )

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

3. 隔离性 (Isolation )

一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4. 持续性 (Durability )

也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

二、MySQL 的四种隔离级别

SQL 标准定义了 4 类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

1. Read Uncommitted（RU: 读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

2. Read Committed（RC: 读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

3. Repeatable Read（RR: 可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

4. Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

三、出现问题

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。

例如：

1. 脏读(Drity Read)：事务 A 更新了一份数据，但还未提交，另一个事务 B 在此时读取了这份数据，这就是脏读，由于某些原因，前一个事务 A RollBack 了操作，则后一个事务 B 所读取的数据就会是不正确的。

2. 不可重复读(Non-repeatable read): 事务 A 修改了一份数据，并 commit，另一个事务 B 在事务 A 提交前后各查询一次，查询出来的数据不同，这就是不可重复读。

3. 幻读(Phantom Read): 在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

四、MySQL 幻读解决办法

MySQL Innodb 引擎 在 可重复读隔离级别 下使用 间隙锁（Gap Lock） 解决幻读问题，幻读的问题存在是因为新增或者更新操作，这时如果进行范围查询的时候（加锁查询），会出现不一致的问题，这时使用不同的行锁已经没有办法满足要求，需要对一定范围内的数据进行加锁，间隙锁就是解决这类问题的。在 可重复读隔离级别 下，数据库是通过 行锁 和 间隙锁 共同组成的 （next-key lock），来实现的

加锁规则有以下特性，我们会在后面的案例中逐一解释：

​ 1. 加锁的基本单位是（next-key lock），他是前开后闭原则

​ 2. 事务查询过程中访问的对象会增加锁

​ 3. 索引上的等值查询 - - 给唯一索引加锁的时候，next-key lock升级为行锁

​ 4. 索引上的等值查询 - - 向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁

​ 5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止

案例数据：

以上数据为了解决幻读问题，更新的时候不只是对上述的五条数据增加行锁，还对于中间的取值范围增加了 6 间隙锁，（-∞，5]（5，10]（10，15]（15，20]（20，25]（25，+supernum] （其中supernum 是数据库维护的最大的值。为了保证间隙锁都是左开右闭原则。）

案例一：间隙锁简单案例

步骤	事务A	事务B
1	begin; select * from t where id = 11 for update;	-
2	-	insert into user value(12,12,12)
3	commit;	-

当有如上事务 A 和事务 B 时，事务 A 会对数据库表增加（10，15] 这个区间锁，这时insert id = 12 的数据的时候就会因为区间锁（10，15] 而被锁住无法执行。

案例二： 间隙锁死锁问题

步骤	事务A	事务B
1	begin; select * from t where id = 9 for update;	-
2	-	begin; select * from t where id = 6 for update;
3	-	insert into user value(7,7,7)
4	insert into user value(7,7,7)	-

不同于写锁相互之间是互斥的原则，间隙锁之间不是互斥的，如果一个事务A获取到了（5,10] 之间的间隙锁，另一个事务B也可以获取到（5,10] 之间的间隙锁。这时就可能会发生死锁问题，如下案例。 事务A获取到（5,10] 之间的间隙锁不允许其他的 DML 操作，在事务提交，间隙锁释放之前，事务B也获取到了间隙锁（5,10]，这时两个事务就处于死锁状态

案例三： 等值查询 - - 唯一索引

步骤	事务A	事务B	事务C
1	begin; update u set d= d+ 1 where id = 7;	-	-
2	-	insert into u (8,8,8);	-
4	-	-	update set d = d+ 1 where id = 10

1. 加锁的范围是（5,10] 的范围锁
2. 由于数据是等值查询，并且表中最后数据id = 10 不满足id= 7的查询要求，故id=10 的行级锁退化为间隙锁，（5,10）
3. 所以事务B中id=8会被锁住，而id=10的时候不会被锁住

案例四： 等值查询 - - 普通索引

步骤	事务A	事务B	事务C
1	begin; select id form t where c = 5 lock in share mode;	-	-
2	-	update t set d = d + 1 where id = 5	-
4	-	-	insert into values (7,7,7)

1. 加锁的范围是（0,5]，（5,10]的范围锁
2. 由于c是普通索引，根据原则4，搜索到5后继续向后遍历直到搜索到10才放弃，故加锁范围为（5,10]
3. 由于查询是等值查询，并且最后一个值不满足查询要求，故间隙锁退化为（5,10）
4. 因为加锁是对普通索引c加锁，而且因为索引覆盖，没有对主键进行加锁，所以事务B执行正常
5. 因为加锁范围（5,10）故事务C执行阻塞
6. 需要注意的是，lock in share mode 因为覆盖索引故没有锁主键索引，如果使用for update 程序会觉得之后会执行更新操作故会将主键索引一同锁住

案例五： 范围查询 - - 唯一索引

步骤	事务A	事务B	事务C
1	begin; select * form t where id >= 10 and id <11 for update	-	-
2	-	insert into values(8,8,8) insert into values(13,13,13)	-
4	-	-	update t set d = d+ 1 where id = 15

1. next-key lock 增加范围锁（5,10]
2. 根据原则5，唯一索引的范围查询会到第一个不符合的值位置，故增加（10，15]
3. 因为等值查询有id =10 根据原则3间隙锁升级为行锁，故剩余锁[10,15]
4. 因为查询并不是等值查询，故[10,15]不会退化成[10,15)
5. 故事务B（13,13,13）阻塞，事务C阻塞

案例六： 范围查询 - - 普通索引

步骤	事务A	事务B	事务C
1	begin; select * form t where c >= 10 and c <11 for update	-	-
2	-	insert into values(8,8,8)	-
4	-	-	update t set d = d+ 1 where c = 15

1. next-key lock 增加范围锁（5,10]，（10，15]
2. 因为c是非唯一索引，故（5,10]不会退化为10
3. 因为查询并不是等值查询，故[10,15]不会退化成[10,15)
4. 所以事务B和事务C全部堵塞

案例七： 普通索引 - - 等值问题

上面的数据增加一行（30,10,30），这样在数据库中存在的c=10的就有两条记录

步骤	事务A	事务B	事务C
1	begin; delete from t where c = 10	-	-
2	-	insert into values(12,12,12)	-
4	-	-	update t set d = d+ 1 where c = 15

1. next-key lock 增加范围锁（5,10]，（10，15]
2. 因为是等值查询故退化为（5,10]，（10，15），故事务B阻塞，事务C执行成功 加锁的范围如下图

案例八： 普通索引 - - 等值Limit问题

步骤	事务A	事务B	事务C
1	begin; delete from t where c = 10 limit 2	-	-
2	-	insert into values(12,12,12)	-
4	-	-	update t set d = d+ 1 where c = 15

1. 根据上面案例8改造，将delete增加limit操作2的操作
2. 因为知道了数据加锁值加2条，故在加锁（5，10]之后发现已经有两条数据，故后面不在向后匹配加锁。所以事务B执行成功，加锁范围如下

× Jacobc' Blog

如果这篇文章对你有用,请随意打赏

2元 5元 10元 50元 100元 任意金额

2元

使用微信扫描二维码完成支付

• ← Previous Post
• Next Post →