提出一个问题
MySQL InnoDB 引擎, 在 REPEATABLE READ (以下简称 RR)隔离级别下, 是否能避免幻读 (phantom read)?
两本书
《高性能MySQL 第三版》
1.3.1 隔离级别
REPEATABLE READ 解决了脏读的问题。（READ COMMITTED 就解决了脏读问题， REPEATABLE READ是部分解决不可重复读问题）该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另一个幻读（Phantom Read）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC， Multiversion Concurrency Control）解决了幻读的问题。本章稍后会做进一步讨论。
可重复读是MySQL的默认事务隔离级别。
REPEATABLE READ solves the problems that READ UNCOMMITTED allows. It guarantees that any rows a transaction reads will “look the same” in subsequent reads within the same transaction, but in theory it still allows another tricky problem: phantom reads. Simply put, a phantom read can happen when you select some range of rows, another transaction inserts a new row into the range, and then you select the same range again; you will then see the new “phantom” row. InnoDB and XtraDB solve the phantom read problem with multiversion concurrency control, which we explain later in this chapter.
REPEATABLE READ is MySQL’s default transaction isolation leve.
InnoDB 采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ（可重复读），并且通过next-key locking（间隙锁）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。
InnoDB uses MVCC to achieve high concurrency, and it implements all four SQL standard isolation levels. It defaults to the REPEATABLE READ isolation level, and it has a next-key locking strategy that prevents phantom reads in this isolation level: rather than locking only the rows you’ve touched in a query, InnoDB locks gaps in the index structure as well, preventing phantoms from being inserted.
除此之外书内再也搜不到其他关于幻读/phantom的内容
《MySQL技术内幕 - InnoDB存储引擎 第二版》
6.4.2 解决 Phantom problem
在默认的事务隔离级别下, 即 REPEATABLE READ 下, InnoDB 存储引擎采用 Next-Key Locking 机制来避免 Phantom Problem (幻象问题). 这点可能不同于其他的数据库, 如 Oracle 数据库, 因为其可能需要在 SERIALIZABLE 的事务隔离级别下才能解决 Phantom Problem.
7.6 事务的隔离级别
InnoDB 存储引擎默认支持的隔离级别是 REPEATABLE READ, 但是与标准 SQL 不同的是, InnoDB 存储引擎在 REPEATABLE READ 事务隔离级别下, 使用 Next-Key Lock 锁的算法, 因此避免幻读的产生. 这与其他数据库系统(如 Microsoft SQL Server 数据库)是不同的. 所以说, InnoDB 存储引擎在默认的 REPEATABLE READ 的事务隔离级别下已经能完全保证事务的隔离性要求, 即达到 SQL 标准的 SERIALIZABLE 隔离级别.
两个概念
再次回顾提出的问题, 问题两个概念
- 幻读现象
- MySQL InnoDB 引擎的 REPEATABLE READ 隔离级别
Phantom read 幻读
问题 1: 隔离级别是如何被定义的?
ANSI 基于现象(Phenomena)定义隔离级别
P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL- transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.
P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL- transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.
P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some . SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same , it obtains a different collection of rows.
由读取(read), 写入(write), 提交(commit)和终止(abort) 组成的历史记录可以用简写符号表示:
w1[x]表示数据项 x 上的事务 1 的写入, 或者说事务 1 修改数据项 xr2[x]表示事务 2 对数据项 x 的读取w1[P]表示事务 1 满足谓词 P 的数据写入r1[P]表示事务 1 满足谓词 P 的一组数据的读取
predicate 谓词可以理解为断言, 一组查询条件
那么三种现象可以简写为:
- P1: w1[x] . . . r2[x] . . . (a1 and c2 in either order)
- P2: r1[x]...w2[x]...c2...r1[x]...c1
- P3: r1[P]...w2[y in P]...c2...r1[P]...c1
通过规定可能出现和不可能出现的现象划分出四种隔离级别:
| 隔离级别 | P1 脏读 | P2 不可重复读 | P3 幻读 |
|---|---|---|---|
| ANSI-读未提交 | 可能发生 | 可能发生 | 可能发生 |
| ANSI-读提交 | - | 可能发生 | 可能发生 |
| ANSI-可重复读 | - | - | 可能发生 |
| ANSI-可串行化 | - | - | - |
ANSI 给出了幻读的定义, 并且明确说明了 ANSI RR 隔离级别是不能避免幻读的. 那 InnoDB 的 RR 隔离级别呢?
REPEATABLE READ 隔离级别
为什么一定要强调是 InnoDB 的 RR 隔离级别? 跟其他数据库的 RR 隔离级别不一样吗?
"这不是一样的嘛???"
虽然都叫奥特曼, 但他们各有各的"人间体"(实现方式)和各不相同的能力. 为了区分, 他们被叫做迪迦奥特曼, 戴拿奥特曼和盖亚奥特曼
数据库的隔离性也一样, 虽然都叫 REPEATABLE READ, 但每个数据库系统都各有各的实现方式, 就造成了相同的名字, 不同的隔离效果
为了区分, 这里特指了 MySQL InnoDB 引擎的 REPEATABLE READ 隔离级别
多种数据库之间隔离级别的不同可以参考该项目 hermitage
实践是检验真理的唯一标准
既然已经明确幻读的概念, 只需在 MySQL InnoDB RR 隔离级别下进行测试即可得到答案(证伪)
create table t(
id int auto_increment primary key,
c1 int
);
create unique index idx_t_c1 on t(c1);
drop table t;
insert into t(id, c1) values (1,1),(20,20),(30,30);
select * from t;
| id | c1 |
|---|---|
| 1 | 1 |
| 20 | 20 |
| 30 | 30 |
示例 1
| 事务 1 | 事务 2 | |
|---|---|---|
start transaction; | s | |
select * from t; | ||
start transaction; | ||
insert into t(id, c1) values (4,4); | ||
commit; | ||
select * from t; | ||
commit; |
事务 1 两次查询得到了相同的结果集, 看上去是可以避免幻读的.
示例 2
| 事务 1 | 事务 2 | |
|---|---|---|
start transaction; | s | |
select * from t; | ||
start transaction; | ||
insert into t(id, c1) values (4,4); | ||
commit; | ||
select * from t for update; | ||
commit; |
事务 1 第二次查询结果集发生了变化, 查询到了事务 2 新插入的数据, 不能避免幻读情况发生. 但是你可能会说这里使用for update强制执行了当前读这不算...
示例 3
| 事务 1 | 事务 2 | |
|---|---|---|
start transaction; | s | |
select * from t; | ||
start transaction; | ||
insert into t(id, c1) values (4,4); | ||
commit; | ||
update t set c1 = -c1 where id < 10; | ||
select * from t; | ||
commit; |
书里写的也不一定对, 得有点怀疑精神
推荐阅读:
- Bug #63870 Repeatable-read isolation violated in UPDATE
小结
我们好像已经找到了问题的答案, 根据以上三个示例的结果我们可以说MySQL InnoDB RR 隔离级别不能完全避免幻读, 或者说在只读(快照读)情况下可以避免幻读.
幻读问题的解决方式
通过一个示例说明幻读可能造成的问题以及常见的解决方式.
示例：会议室预订系统，试图避免重复预订
START TRANSACTION ;
-- Check for any existing bookings that overlap with the period of noon-lpm
SELECT
COUNT(*)
FROM
bookings
WHERE
room_id = 123
AND end_time > '2015-01-01 12:00'
AND start_time < '2015-01-01 13:00';
-- If the previous query returned zero :
INSERT INTO bookings
(room_id, start_time, end_time, user_id)
VALUES
(123, '2015-01-01 12:00', '2015-01-01 13:00', 666);
COMMIT;
问题2: 如果有两个事务同时执行以上 SQL 可能发生什么情况呢?
实体化冲突
问题的关键在于查询结果中没有对象可以加锁。如果人为引入一些可以加锁的对象呢？
例如： 对于会议室预定的例子，构造一个时间-房间表，表的每一行对应于特定的时间段，比如最小粒度为15分钟。
| id | room_id | start_time | end_time |
|---|---|---|---|
| 1 | 1 | 2015-01-01 12:00 | 2015-01-01 12:15 |
| 2 | 1 | 2015-01-01 12:15 | 2015-01-01 12:30 |
| 3 | 2 | 2015-01-01 12:00 | 2015-01-01 12:15 |
| ... | ... | ... | ... |
现在，预订事务可以查询并锁定表中与查询房间和时间段所对应的行。加锁之后，即可检查是否有重叠，然后像之前一样插入新的预订。注意，这种附加表格并不存储预订相关的信息，它仅仅用于方便加锁，防止同一房间和时间范围内的重复预订。
这种方法称为实体化冲突(或物化冲突)，它把幻读问题转变为针对数据库中一组具体行的锁冲突问题。
属性谓词锁
谓词锁并不属于某个特定的数据项，而是作用于满足某些搜索条件的所有查询对象，例如：
SELECT COUNT(*) FROM bookings
WHERE room_id = 123 AND
end_time > '2015-01-01 12:00' AND start_time < '2015-01- 01 13:00';
谓词锁会限制如下访问：
- 如果事务A想要读取某些满足匹配条件的对象，例如采用SELECT查询，它必须以共享模式获得查询条件的谓词锁。如果另一个事务B正在持有任何一个匹配对象的互斥锁，那么A必须等到B释放锁之后才能继续执行查询。
- 如果事务A想要插入、更新或删除任何对象，则必须首先检查所有旧值和新值是否与现有的任何谓词锁匹配(冲突)。如果事务B持有这样的谓词锁，那么A必须等到B完成提交(或中止)后才能继续。
这里的关键点在于，谓词锁可以保护数据库中那些尚不存在但可能马上会被插入的对象。
索引区间锁
很明显，谓词锁性能不佳: 如果活动事务中存在许多锁，那么检查匹配这些锁就变得非常耗时。因此，大多数数据库实际上实现的是索引区间锁(或next-key loking)，本质上它是对谓词锁的简化或者近似。
下面通过一个例子来看一下 InnoDB 中的 Next-key Lock
drop table scores;
create table scores(
id bigint auto_increment primary key,
name varchar(256),
age int,
score int,
index `age` (age),
index `score` (score)
) ;
insert into scores(name, age, score)
values ('apple',12,58),('boy',12,60),('cat',11,61);
insert into scores(name, age, score)
values ('dog',13,59),('egg',14,62),('fly',13,61);
select * from scores;
| id | name | age | score |
|---|---|---|---|
| 1 | apple | 12 | 58 |
| 2 | boy | 12 | 60 |
| 3 | cat | 11 | 61 |
| 4 | dog | 13 | 59 |
| 5 | egg | 14 | 62 |
| 6 | fly | 13 | 61 |
$ SHOW ENGINE INNODB STATUS;
------------
TRANSACTIONS
------------
Trx id counter 2823
Purge done for trx s n:o < 2819 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422023496961952, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422023496960368, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422023496959576, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 2822, ACTIVE 3 sec
3 lock struct(s), heap size 1128, 5 row lock(s)
MySQL thread id 9, OS thread handle 123145583550464, query id 87 localhost 127.0.0.1 root
TABLE LOCK table `test`.`scores` trx id 2822 lock mode IX
# score 索引添加 Next-Key Lock
RECORD LOCKS space id 7 page no 6 n bits 80 index score of table `test`.`scores` trx id 2822 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000003c; asc <;; # score = 3c = 3 * 16 + 12 = 60
1: len 8; hex 8000000000000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000003d; asc =;; # score = 3d = 3 * 16 + 13 = 61
1: len 8; hex 8000000000000003; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000003b; asc ;;; # score = 3b = 3 * 16 + 11 = 59
1: len 8; hex 8000000000000004; asc ;;
# 加锁范围是 (58, 59], (59, 60], (60, 61]
RECORD LOCKS space id 7 page no 4 n bits 80 index PRIMARY of table `test`.`scores` trx id 2822 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000002; asc ;; # id = 2
1: len 6; hex 000000000935; asc 5;;
2: len 7; hex 81000000860121; asc !;;
3: len 3; hex 626f79; asc boy;;
4: len 4; hex 8000000c; asc ;;
5: len 4; hex 8000003c; asc <;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000004; asc ;; # id = 4
1: len 6; hex 00000000093a; asc :;;
2: len 7; hex 82000000880110; asc ;;
3: len 3; hex 646f67; asc dog;;
4: len 4; hex 8000000d; asc ;;
5: len 4; hex 8000003b; asc ;;;
如上图所示，绿色是应该加锁的区域, 黄色是实际加锁的区域. 索引区间锁的问题是可能会造成锁范围的扩大，随着查询维度的增加扩大的范围也随之快速膨胀。
加锁是悲观并发控制思想的体现, 虽然可以解决幻读问题保证串行化，但性能肯定会大打折扣
乐观并发控制能不能实现可串行化呢? 可以去了解一下: 可串行化的快照隔离(Serializable Snapshot Isolation SSI)
问题 3: 既然在 InnoDB RR 级别下也不能完全避免幻读, 那 InnoDB 在 RR 级别中引入 Next-Key Lock 要解决什么问题呢?
Next-key Lock 与 binlog 同步
MySQL 主从节点之间通过 binlog 进行数据同步, 而 binlog 格式有三种:
- statement-based: 直接记录 SQL 语句
- row-based: 记录写入事件, 描述每行记录的修改细节
- mixed: 一般语句使用 statement-based 保存, 如果使用了一些函数, statement-based 格式无法完成主从复制的操作, 采用 row-based
当采用 statement-based binlog 进行数据同步时, 如果不使用 Next-key Lock(如 RC 级别) 可能导致主从节点数据不一致.
CREATE TABLE `t1` (
`id` int auto_increment primary key,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
drop table t1;
insert into t1 values(1, 10, 2),(2, 20, 1);
select * from t1;
| id | a | b |
|---|---|---|
| 1 | 10 | 2 |
| 2 | 20 | 1 |
| 事务 1 | 事务 2 | |
|---|---|---|
start transaction; | s | |
update t1 set a = 11 where b = 2; | ||
start transaction; | ||
update t1 set b = 2 where b = 1; | ||
commit; | ||
commit; |
以上两个事务如果在 RC 隔离级别执行主节点得到结果:
| id | a | b |
|---|---|---|
| 1 | 11 | 2 |
| 2 | 20 | 2 |
由于事务 2 先于事务 1 提交则通过 statement-based binlog 进行重放的从节点将得到如下结果(从节点上事务 2 语句先执行), 导致主从数据不一致
| id | a | b |
|---|---|---|
| 1 | 11 | 2 |
| 2 | 11 | 2 |
如果在 RR 级别下执行, 由于事务1 会增加 Next-key Lock 来锁住区间(1, 2](2, positive infinity), 事务 2 必须等待锁释放才能执行; 事务 1 将先于事务 2 提交, 保证主从数据一致
推荐阅读:
- Bug #23051 READ COMMITTED breaks mixed and statement-based replication
PS: 看到 Bug 的第一反应总是甩锅
MySQL 5.1.21 (2007-08-16) 之后 InnoDB 的 READ COMMITTED 和 READ UNCOMMITTED 隔离级别只能使用 row-based binlog
ANSI 隔离级别的缺陷
ANSI 希望通过定义读现象和对读现象的排除来划分隔离级别, 并做到标准定义与实现无关, 理想很丰满现实很骨感.
- 读现象描述不全, 缺少脏写定义
- 脏写定义 P0: w1[x]...w2[x]...(c1 or a1)
- 表述有歧义, 无法覆盖更新丢失, 读偏和写偏等现象, 导致即使三个现象全部排除了也不能达到可串行化隔离级别
观察该事务执行历史序列 H2：r1[x=50] r2[x=50] w2[x=10] r2[y=50] w2[y=90] c2 r1[y=90]
假设约束为 x+y=100. 该序列执行时, 事务 1 会观测到 x+y=140, 违反约束. 而且 H2 不属于 ANSI 定义的三种现象中的任意一种
- P1: w1[x] . . . r2[x] . . . (a1 and c2 in either order) // H2 读的都是已提交的数据
- P2: r1[x]...w2[x]...c2...r1[x]...c1 // H2 每个事务都没有重复读取某个数据
- P3: r1[P]...w2[y in P]...c2...r1[P]...c1 // H2 没有任何范围查询
或者观察该序列 H5B: r1[x=50] r1[y=50] r2[x=50] r2[y=50] w1[y=-40] w2[x=-40] c1 c2 假设约束为 x+y >=0, 该序列执行后x+y=-80, 违反约束. 且 H5B 不属于以上三种读现象
如果想要进一步了解针对隔离级别严格正式的定义和分析可以参考以下论文:
- A Critique of ANSI SQL Isolation Levels
- Generalized Isolation Level Definitions
- Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions
- Highly Available Transactions: Virtues and Limitations (Extended Version)
小结
前面我们通过问题: "MySQL InnoDB 引擎, 在 REPEATABLE READ (以下简称 RR)隔离级别下, 是否能避免幻读 (phantom read)?" 引出了幻读的定义, 了解了幻读常见的解决方式. 最终通过实践进行了证伪.
了解了不同数据库系统对隔离级别的实现各不相同, 相同的名字不同的效果. 也初步了解了 ANSI 隔离级别定义的缺陷
重要的是在实践中不能硬套隔离级别定义, 需要根据存储选型的实现结合具体场景进行分析, 选择合适的隔离级别
从理论上来说, 可串行化才是解决问题的唯一选择, 但是在实践中少有人会使用可串行化隔离级别(性能问题), 大多数时候我们都是在使用不同程度的弱隔离级别, 基本上也没有遇到什么问题. 可串行化是否真的有必要? 我们到底需要的是什么?
参考
- ANSI SQL-92
- 《数据密集型应用系统设计》(DDIA: Designing Data Intensive Applications)
