数据库隔离级别与解决方案

一. 隔离级别简介

数据库的隔离级别主要有四种：

1. 读未提交（Read Uncommitted）：最低的隔离级别，允许读取尚未提交的数据变更，可能导致脏读、幻读或不可重复读。
2. 读已提交（Read Committed）：大多数数据库系统的默认隔离级别（如Oracle, PostgreSQL）。保证了一个事务在读取数据时，那些已经被其他完成的事务修改的数据，可以被当前事务读取。防止了脏读，但幻读和不可重复读仍可能出现。
3. 可重复读（Repeatable Read）：在同一事务内的查询都能够看到一致的快照数据。在MySQL InnoDB引擎中，可重复读是默认的隔离级别。它解决了脏读和不可重复读，但幻读仍可能出现。
4. 串行化（Serializable）：最高的隔离级别，它通过强制事务串行执行，避免了前三个隔离级别中可能出现的问题。但这种级别会导致性能下降，因为事务完全是一个接一个地执行，没有并行性。

以上四种隔离级别，隔离级别越高，数据一致性越好，但并发性能越差。因此在实际应用中，需要根据业务需求和系统负载情况，选择合适的隔离级别。

二. InnoDB各隔离级别的解决方案

2.1 读未提交

有脏读，可以读到未提交的事务的修改结果，隔离性最差。

2.2 读已提交

相比RU，RC在事务提交之后才能读到修改结果，解决了脏读问题。
在RC下，一个事务提交之后，修改对另一个事务可见，并且其他事务多次修改并提交之后，这些改动对一个事务内的多个查询都是可见的。
因为RC下，MVCC的每次查询都会生成新的Read View，导致每次查询都可以查到已提交的最新的数据，所以对RC来说，当前读和快照读的范围都是一致的。

2.3 可重复读

在RR下，MVCC在事务开始时会生成新的Read View，事务内的每次快照读读到的结果都是一致的，但当前读能读到其他事务已提交的最新的数据。
那么有如下几个问题：
下列问题的验证均基于MySQL 5.7版本

2.3.1 当前读之后，会生成新的Read View导致快照读结果改变吗

首先给定一个测试表

id	name	salary
1	a	1000
2	b	2000
3	c	3000

然后执行如下操作并得到结果

Transaction 1	Result 1	Transaction 2	Result 2
START TRANSACTION;
SELECT * FROM tranx_test WHERE salary = 3000;	id|name|salary|--+----+------+ 3|c | 3000|``1 row(s) fetched.
		START TRANSACTION;
		UPDATE tranx_test SET salary = 5000 WHERE id IN (1, 2, 3);	3 row(s) modified.
		COMMIT;
SELECT * FROM tranx_test WHERE id = 3 FOR UPDATE;	id|name|salary|--+----+------+ 3|c | 5000|``1 row(s) fetched.
SELECT * FROM tranx_test WHERE id = 3;	id|name|salary|--+----+------+ 3|c | 3000|``1 row(s) fetched.
SELECT * FROM tranx_test WHERE salary = 5000;	id|name|salary|--+----+------+0 row(s) fetched.
UPDATE tranx_test SET name = “CCC” WHERE id = 3;	1 row(s) modified.
SELECT * FROM tranx_test WHERE id = 3;	id|name|salary|--+----+------+ 3|CCC | 5000|``1 row(s) fetched.
SELECT * FROM tranx_test WHERE salary = 5000;	id|name|salary|--+----+------+ 3|CCC | 5000|``1 row(s) fetched.
SELECT * FROM tranx_test WHERE id = 1;	id|name|salary|--+----+------+ 1|a | 1000|``1 row(s) fetched.
COMMIT;

1. 首先在事务1开启后，生成了一个Read View，并查询到与原表相符的数据。
2. 事务2将所有salary修改为5000后提交。
3. 事务1使用 SELECT … FOR UPDATE 进行当前读，读到了最新salary=5000的数据，但之后再进行快照读，读到的还是原始的事务1刚开始时的快照salary=3000。
4. 事务1使用 UPDATE 进行当前读，修改了id=3的name，之后再进行快照读，读到的是最新的数据+事务1本次UPDATE修改后的数据，salary=5000且name=”CCC”，但是其他没有使用 UPDATE 进行当前读的数据的快照读仍然是原始的数据。

所以很神奇，虽然 SELECT … FOR UPDATE 和 UPDATE 同为当前读，但是造成的结果并不一致：

• SELECT … FOR UPDATE 的当前读，会读到最新的数据，但是对后续的快照读不产生影响。
• UPDATE 的当前读，会读到最新的数据，并且后续的快照读也能读到最新的数据。

另外，上文中的 UPDATE 是指定id修改的，再测试一下如果指定的是salary，那么这个salary应该指定事务2修改之前还是修改之后的值。
详细代码不写了，还是最开始的数据，被事务2改成了salary均为5000.

> UPDATE tranx-test SET salary = 8000 WHERE salary = 3000

0 row(s) modified.

> UPDATE tranx-test SET salary = 8000 WHERE salary = 5000

3 row(s) modified.

所以，需要指定修改后的最新数据。
那么总结一下，当前读会修改Read View吗？
不会。Read View内其实只记录了一些事务ID，并不会存实际的数据，RR下创建后也并不会再改变。
在 SELECT … FOR UPDATE 时，数据并没有修改，但是 UPDATE 修改了数据，并且由于是当前读，并不会受快照影响，修改用的是最新的数据，并且由于同事务内修改是可见的，所以后续的快照读能读到最新的数据。

2.3.1 InnoDB的RR到底解决幻读了吗

经常能看到说，InnoDB在RR隔离级别下，通过MVCC和next-key locks解决了幻读的问题，真的解决了吗？
首先是快照读，并不会加锁，并且只能读到快照，所以解决了幻读。
其次是当前读，会加锁，但是通过next-key locks锁住了区间，导致其他事务不能插入数据，所以解决了幻读。
那如果在事务开始后，当前读加锁之前，另一个事务就已经插入了数据，这会有幻读吗？
首先验证一下两种能够解决幻读的场景，原始数据依旧如2.3.1的初始测试表：

Transaction 1	Result 1	Transaction 2	Result 2
START TRANSACTION;
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000;	id|name|salary|--+----+------+ 1|a | 1000| 2|b | 2000| 3|c | 3000|``3 row(s) fetched.
		START TRANSACTION;
		INSERT INTO tranx_test values(4, “d”, 2500);	1 row(s) modified.
		COMMIT;
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000;	id|name|salary|--+----+------+ 1|a | 1000| 2|b | 2000| 3|c | 3000|``3 row(s) fetched.
COMMIT;

由上表，快照读不会有幻读。

Transaction 1	Result 1	Transaction 2	Result 2
START TRANSACTION;
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000 FOR UPDATE;	id|name|salary| –+—-+——+ 1|a | 1000| 2|b | 2000| 3|c | 3000| 3 row(s) fetched.
		START TRANSACTION;
		INSERT INTO tranx_test values(4, “d”, 2500);	这里被阻塞了，没法执行，需要事务1先提交后释放锁。

由上表，当前读有锁，事务2无法在事务1锁住的区间范围内插入数据，保证了事务1的查询不会出现幻读。

如果当前读是在另一个事务写入之后执行的，会出现幻读吗？

Transaction 1	Result 1	Transaction 2	Result 2
START TRANSACTION;
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000;	id|name|salary|--+----+------+ 1|a | 1000| 2|b | 2000| 3|c | 3000|``3 row(s) fetched.
		START TRANSACTION;
		INSERT INTO tranx_test values(4, “d”, 2500);	1 row(s) modified.
		COMMIT;
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000;	id|name|salary|--+----+------+ 1|a | 1000| 2|b | 2000| 3|c | 3000|``3 row(s) fetched.
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000 FOR UPDATE;	id|name|salary|--+----+------+ 1|a | 1000| 2|b | 2000| 3|c | 3000| 4|d | 2500|4 row(s) fetched.
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000;	id|name|salary|--+----+------+ 1|a | 1000| 2|b | 2000| 3|c | 3000|``3 row(s) fetched.
COMMIT;

由上表，当前读还是会发生幻读的，因为在当前读加锁之前，另一个事务就已经把数据写入了，当前读会读到最新的数据。

如果把 SELECT … FOR UPDATE 换成 UPDATE ，也会有幻读，类似2.3.1中的结论。

Transaction 1	Result 1	Transaction 2	Result 2
START TRANSACTION;
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000;	id|name|salary| –+—-+——+ 1|a | 1000| 2|b | 2000| 3|c | 3000| 3 row(s) fetched.
		START TRANSACTION;
		INSERT INTO tranx_test values(4, “d”, 2500);	1 row(s) modified.
		COMMIT;
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000;	id|name|salary| –+—-+——+ 1|a | 1000| 2|b | 2000| 3|c | 3000| 3 row(s) fetched.
UPDATE tranx_test SET name = “DDD” WHERE salary = 2500;	1 row(s) modified.
SELECT * FROM tranx_test WHERE salary >= 1000 AND salary <= 3000;	id|name|salary| –+—-+——+ 1|a | 1000| 2|b | 2000| 3|c | 3000| 4|DDD | 2500| 4 row(s) fetched.
COMMIT;

更新的时候，应该找不到符合salary=2500的数据，但是找到并更新了，所以是发生了幻读。

当前读读到最新数据并修改后，同事务内的快照读也能够看到这条改动的数据。

那么总结一下，InnoDB的RR解决幻读了吗？

解决了，但没完全解决。MVCC和next-key locks确实能在[快照读]和[当前读之后]解决幻读问题，但是如果另一个事务的插入发生在[当前读之前]，即并没有加上next-key lock，那么就仍然会发生幻读。

2.4 串行化

纯串行，确实就没幻读了，但串行就太慢了，没见用过的。