数据库的隔离等级

在这里讲讲数据库的隔离等级。

以 MySQL 和 Postgres 为例

数据库事务的四大特性：

1. 原子性：整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。

2. 一致性：一个事务执行之前和执行之后都必须处于一致性状态。举例：假设用户 A 和用户 B 两者的钱加起来一共是 5000，那么不管 A 和 B 之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是 5000，即从一个一致性的状态转换到另一个一致性的状态，这就是事务的一致性。

3. 隔离性：多个事务并发访问时，事务之间是相互隔离的，不应该被其他事务干扰。等级一般有：read_uncommitted，read_committed，read_repeatable，Serializable 串行化访问。

4. 持久性：一个事务一旦被提交了，那么对数据库中数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

事务并发可能导致的问题

• 脏读(dirty read)：读取到未提交的数据。

• 不可重复读(read unrepeatable)：在同一个事务中，读取了其他事务（已提交）更改的数据，针对update操作。相同的查询语句得到不同的结果（针对单行数据）。

• 幻读(photom read)：在同一个事务中，读取了其他事务（已提交）新增的数据，针对insert 和 delete操作。相同的查询语句返回的记录条数不同（针对多行数据，注意区别不可重复读）。

• 串行化异常(serialization anomaly)：成功提交一组事务的结果与这些事务所有可能的串行执行结果都不一致。

隔离等级

数据库有四大隔离等级：读未提交(read uncommitted)，读已提交(read committed)，可重复读(repeatable read)，可串行化(serializable)。接下来通过在 MySQL 与 Postgres 演示来理解它们，并对比 MySQL 与 Postgres 实现这些隔离等级的差异。

MySQL

0.查看 MySQL 等默认隔离级

mysql> select @@global.transaction_isolation;  # MySQL 默认隔离等级
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

1.读未提交

设置完后，该隔离等级 在 当前控制台 所有事务中都有效 （直到修改为其他隔离等级）

出现脏读。

2.读已提交

由于屏幕不够大所以修改查询语句为只返回需要的结果 ψ(._. )>

解决了脏读，但是没有解决 不可重复读 和 幻读。

在事务 1 提交（第 12 步）后，事务 2 再次查询，但是结果与上一次（第 10 步）不同，且查询余额大于等于 900 的记录，返回记录条数也与上一次（第 11 步）不同。

3.可重复读

第 12 步及之后的操作都是在 事务 1 已提交的基础上进行的。接着如果在 事务 2 对 id = 1账户余额 减 100，结果会如何？

没有报错 ψ(._. )>，那么结果会是 600 OR 700 ？

查询其余额:

结果是 600，没问题，因为 事务 1 中我们减去了 100。但是如果单从 事务 2 的视角来看，原来 800， 减 100， 结果是 600 ？！这算不算没有保持一致性呢？ 由此来看，事务的并发修改操作还是对其他事务有影响。 为什么不组织修改并抛出错误呢？ （ Postgres 的 可重复读 就是这么实现的）。

4.可串行化

最高的隔离等级，最安全但是带来的开销也最大。

# 等待超时，自动结束了
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

在 可串行化 等级中，MySQL 将所有 select 语句转化为 select for share，使得查询操作持有共享锁，一个事务中的 select for share 操作只允许其他事务进行读操作，而不能写。通过这样的锁机制 可串行化 这个等级解决了上一个等级的问题。

并发修改导致死锁，其中一个事务由于死锁操作失败自动回滚，释放了锁，另一个事务的写操作就可以进行下去了。

这次在超时前提交，修改成功。

由于事务 1 修改了数据（第 5 步），它提交前事务 2 无法读，之后事务 2 写数据就不会写入相同的数据，这样也保证了串行化。

Postgres

创建了一个与 MySQL 中一样的数据库，并随机生成了 3 条数据用来演示。

0.查看 Postgres 默认隔离等级

show transaction isolation level;
 transaction_isolation
-----------------------
 read committed
(1 row)

默认为 读已提交，比 MySQL 低一个等级。

1.读未提交

与 MySQL 不同，Postgres 中只能在事务中设置隔离等级（即每次设置只对当前事务有效）。

当前隔离等级为 读未提交，所以应该在第 10 步查询中读取到 未提交的修改，但实际却没有？！

在事务 1 提交后，事务 2 再次查询得到的结果为修改并提交的结果，900。

In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e., PostgreSQL's Read Uncommitted mode behaves like Read Committed. This is because it is the only sensible way to map the standard isolation levels to PostgreSQL's multiversion concurrency control architecture.

官方文档中如是写道

御坂如是说道

即，Postgres 虽然有四个隔离等级，但实际上 Postgres 的 读未提交 与 读已提交 一样。因为我们一般在任何场景下都不会使用 读未提交。(这也是为什么 Postgres 的默认隔离等级为 读已提交)。

2.读已提交

除了脏读，我们还需要验证其他问题是否解决，所以继续演示，并且不再验证脏读。

由于不需要验证脏读，所以事务 1 修改完余额后直接提交（第 10 步）。然后在事务 2 查询，发生 不可重复读（第 11 步，虽然此前没有直接查询 id 为 1 的账户余额，但在查询余额大于 900 的账户中能够看到 id 为 1 的账户余额） 和 幻读（第 12 步，比第 8 步查询结果的记录条数少一条）。

所以同 MySQL 的结果一样，读已提交等级 只解决了脏读问题。

3.可重复读

事务 1 提交后，由事务 2 查询结果知，可重复读等级 解决了不可重复读(第 13 步 和 第 8 步 对比) 和 幻读（第 14 步 和 第 9 步 对比）的问题。

并且在事务 2 中，继续修改余额（第 15 步）抛出错误 ERROR: could not serialize access due to concurrent update，这样避免了像 MySQL 一样 800 - 100 直接变成 600 令人疑惑的操作。

继续验证 Postgres 的 可重复读 是否解决 串行化异常问题：

两个事务都提交后查询结果得到两天一样的记录（id 不同），这是 串行化异常 导致的。如果能够串行化，两个事务的修改将先后进行，所以后一个事务 加和（求 sum）得到的结果就会不一样，一个 sum=2700，另一个 sum=5400。

在添加记录时如果不显示说明 id 的值则会默认为 1 而导致冲突，无法添加。为什么不像 MySQL 中 id 不会自增 😥？ 在事务 2 中添加记录时如果 id 设为 4 会导致冲突，说明事务 1 的修改已经写入了。在演示时就翻过这样的错误，id 不会自增真麻烦...😕

所以 可重复读等级 没有解决 串行化异常的问题。

4.可串行化

提交事务 2 时报错，并给出了提示，在执行一次相同操作即可成功。我试了一下再次 commit，提交成功了，然后查询所有账户，事务 2 添加的那条记录没有添加上去。这两个事务没有插入相同的记录了。

Postgres 使用依赖检测机制来检测潜在的读操作，阻止它们并抛出错误。

总结

隔离等级	脏读	不可重复读	幻读	串行化异常
读未提交	允许，但在 PG(postgres)中不行	可能	可能	可能
读已提交	不可能	可能	可能	可能
可重复读	不可能	不可能	允许，但在 PG 中不行	可能
可串行化	不可能	不可能	不可能	不可能

写在最后

参考：

YouTube-TECH SCHOOL

MySQL document

Postgres document