Go大师课程系列将学习
- Go大师课程（一）: 安装使用pgsql并使用golang-migrate 实现数据库 schema 迁移、sqlc 实现CRUD
- Go大师课程（二）: 数据库事务
- Go大师课程（三）: 解决死锁
- Go大师课程（四）: 避免死锁
- Go大师课程（五）: 深入了解 MySQL 和 PostgreSQL 中的隔离级别
事务隔离和读取现象
首先我们来谈谈它的理论。
ACID 属性
正如我们在上一讲中已经学到的，数据库事务必须满足ACID属性，代表Atomicity、Consistency、Isolation和Durability。
Isolation是数据库事务的四个属性之一，在最高级别，完美的隔离可确保所有并发事务不会相互影响。
有几种方式可以干扰一个事务，这些干扰会同时运行其他事务。这种干扰会导致我们称之为的现象read phenomenon。
4 阅读现象
当数据库在较低的事务隔离级别下运行时，可能会发生以下一些读取现象：
- 首先是
dirty read现象。当一个事务读取其他并发事务写入的尚未提交的数据时，就会发生这种情况。这非常糟糕，因为我们不知道其他事务最终是否会提交或回滚。因此，如果发生回滚，我们最终可能会使用错误的数据。 - 我们可能遇到的第二种现象是
non-repeatable read。当一个事务两次读取同一条记录并看到不同的值时，这是因为该行已被第一次读取后提交的其他事务修改。 Phantom read是一种类似的现象，但会影响搜索多行而不是一行的查询。在这种情况下，会重新执行相同的查询，但会返回一组不同的行，这是由于其他最近提交的事务所做的某些更改（例如插入新行或删除现有行，而这些行恰好满足当前事务查询的搜索条件）。- 另一个涉及分离一组事务的现象是
serialization anomaly。如果我们尝试以任何顺序连续运行一组并发提交的事务而不使它们相互重叠，则无法实现它们的结果。
4 个隔离级别
现在为了应对这些现象，美国国家标准协会（ANSI）定义了4个标准隔离级别。
这里我有 2 个正在运行的 docker 容器，一个是 Postgres 版本 12，另一个是 MySQL 版本 8。在这些容器中，我还准备了简单的银行数据库模式，其中包含一些初始数据，就像我们在之前的讲座中处理的那样。
❯ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
35f16aed1206 mysql:8 "docker-entrypoint.s…" 8 days ago Up 50 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql8
f9cdf61fcb0a postgres:12-alpine "docker-entrypoint.s…" 3 weeks ago Up 54 seconds 0.0.0.0:5432->5432/tcp postgres12
MySQL 中的隔离级别
让我们连接到 MySQL 控制台并访问该simple_bank数据库。
❯ docker exec -it mysql8 mysql -uroot -psecret simple_bank
mysql>
获取 MySQL 中的当前隔离级别
要获取当前会话的事务隔离级别，我们可以运行
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
默认情况下，正如repeatable read我们在这里看到的。此级别仅适用于此特定的 MySQL 控制台会话。
还有一个全局隔离级别，它应用于所有会话首次启动时。我们可以通过在前面的选择查询中添加 global 来获取它的值。
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
1 row in set (0.00 sec)
默认情况下，它也是repeatable read。
更改 MySQL 中的隔离级别
现在要改变当前会话的隔离级别，我们可以使用这个查询：
-- Tx1:
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
您可以将其替换read uncommitted为您要设置的隔离级别的名称。
此后，如果我们再次运行 select transaction integration，我们将看到它已更改为read uncommitted。
-- Tx1:
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
1 row in set (0.00 sec)
请注意，此更改仅对当前会话的所有未来事务产生影响，但不会对在 MySQL 控制台的另一个会话上运行的事务产生影响。
MySQL 中的读取未提交隔离级别
好的，现在为了演示两个并发事务之间的干扰，
我将打开另一个终端窗口，将其与这个窗口并排放置，并在其中启动一个新的 MySQL 控制台。
然后让我们也把这个会话的隔离级别设置为read uncommitted。
-- Tx2:
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
1 row in set (0.00 sec)
好的，现在两个会话都在隔离级别运行read uncommitted。我们现在可以开始新的事务了。
在 MySQL 中，我们可以使用start transaction语句，或者简单地使用begin语句作为替代。
-- Tx1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- Tx2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
好的，2 笔交易已开始。让我们在 中运行一个简单的帐户选择查询transaction 1。
-- Tx1
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 100 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
目前有 3 个账户的余额都是 100 美元。那么在 中transaction 2，我们选择第一个 id 为 1 的账户。
-- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
好的，我们得到了余额为 100 美元的帐户。现在让我们返回transaction 1并运行此更新语句，从帐户 1 中减去 10 美元。
-- Tx1
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查询OK，所以如果我们在中选择账户1 transaction 1，我们将看到余额已更改为90美元。
-- Tx1
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 90 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)
但是如果我们在事务 2 中运行相同的选择语句会怎样？
-- Tx2
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 90 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)
它还会看到余额的修改值：90 美元。请注意，transaction 1尚未提交，但transaction 2仍会看到所做的更改transaction 1。
所以这是一个dirty-read，发生这种情况是因为我们使用了read-uncommitted隔离级别。
好的，现在让我们提交这两个事务并尝试更高的隔离级别。
-- Tx1:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-- Tx2:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
MySQL 中的读取已提交隔离级别
这次，我们将为read committed两个事务设置隔离级别：
-- Tx1 + Tx2
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED |
+-------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
好的，现在让我们选择所有记录来查看帐户表的当前状态transaction 1：
-- Tx1:
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 90 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.01 sec)
现在账户1的余额为90美元，其他2个账户均有100美元。
就像我们之前所做的一样，在中transaction 2，让我们选择 ID 为 1 的账户。
-- Tx2:
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 90 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)
然后在中transaction 1，我们通过从中减去 10 美元来更新该帐户的余额。
-- Tx1:
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 80 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)
好的，这笔交易的余额已成功更改为80美元。让我们看看这个变化是否可见transaction 2。
sql
-- Tx2:
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 90 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)
现在可以看到，当我们选择中的账户1时transaction 2，它的余额还是一样：和以前一样，90美元。
这是因为我们使用了read-committed隔离级别，并且由于事务 1 尚未提交，因此其他事务无法看到其写入的数据。
因此read-committed隔离级别可以防止dirty read这种现象。non-repeatable和怎么样phantom read？
在 中transaction 2，让我们再运行一次从余额大于或等于 90 美元的账户中进行选择。然后返回transaction 1并提交。
-- Tx2:
mysql> select * from accounts where balance >= 90;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 90 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)
-- Tx1:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
好的，现在如果我们再次读取帐户1 transaction 2，我们可以看到余额已变为80美元。
-- Tx2:
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 80 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)
因此，获取帐户 1 的相同查询返回不同的值。这就是non-repeatable read现象。
另外，如果我们重新运行查询以获取余额至少为 90 美元的所有账户：
-- Tx2:
mysql> select * from accounts where balance >= 90;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
2 rows in set (0.00 sec)
这次我们只得到了2条记录，而不是像之前的3条，因为账户1的余额在transaction 1提交后减少到了80。
执行了相同的查询，但返回了不同的行集。其中一行由于其他已提交的事务而消失。这称为phantom-read现象。
所以现在我们知道read-committed隔离级别只能防止dirty read，但仍然允许non-repeatable read和phantom-read现象。
让我们提交这笔交易并进入更高的层次看看会发生什么。
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
MySQL 中的可重复读隔离级别
现在我要将两个会话的事务隔离级别都设置为repeatable read。然后开始 2 个新的transactions。
-- Tx1 + Tx2
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
现在让我们选择 中的所有账户transaction 1。然后选择 中 ID 为 1 的账户transaction 2。还要选择余额至少为 80 美元的所有账户。这将用于验证幻读是否仍然发生。
-- Tx1:
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 80 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)
-- Tx2:
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 80 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)
mysql> select * from accounts where balance >= 80;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 80 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)
好的，现在返回transaction 1并从其余额中减去 10。然后获取所有帐户以查看其当前状态transaction 1。
-- Tx1:
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 70 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)
现在我们可以看到账户1的余额已经减少到70美元。
我们知道脏读在较低的隔离级别（读已提交）中已经被阻止了。因此，我们不需要在此级别检查它，因为有以下规则：
任何在较低隔离级别上被阻止的现象都不会有机会在较高级别上发生。
因此让我们提交这个事务 1，然后转到事务 2 来查看它是否可以读取事务 1 所做的新更改。
-- Tx1:
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
-- Tx2:
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 80 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.01 sec)
现在这个选择查询返回帐户 1 的旧版本，余额为 80 美元，尽管事务 1 已将其更改为 70 并已成功提交。
这是因为repeatable-read隔离级别确保所有读取查询都是可重复的，这意味着，它总是返回相同的结果，即使其他已提交的事务做出了更改。
话虽如此，让我们重新运行选择至少有 80 美元的账户的查询：
-- Tx2:
mysql> select * from accounts where balance >= 80;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 80 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)
可以看到，它仍然返回与之前相同的 3 条记录。因此，phantom read此隔离级别也阻止了这种现象repeatable-read。太棒了！
但是，我想知道如果我们也运行更新查询来从帐户 1 的余额中减去 10，会发生什么transaction 2？它会将余额更改为 70、60 还是引发错误？让我们尝试一下！
-- Tx2:
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 60 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)
没有错误，账户余额现在为 60 美元，这是正确的值，因为transaction 1之前已经提交了将余额修改为 70 美元的更改。
但是，从这个transaction 2角度来看，这毫无意义，因为在最后一个选择查询中，它看到余额为 80 美元，但在从账户中减去 10 美元后，现在它得到了 60 美元。这里的数学运算不起作用，因为此事务仍受到来自其他事务的并发更新的干扰。
我不知道 MySQL 为什么选择以repeatable read这种方式实现隔离级别，但在这种情况下，通过引发错误来拒绝更改以确保事务数据的一致性更有意义。稍后我们将看到这正是 Postgres 在此隔离级别下处理此类并发更新的方式。
现在，让我们回滚该事务，并尝试转到最高隔离级别，看看是否可以避免此问题。
-- Tx2:
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
MySQL 中的可序列化隔离级别
好的，让我们将两个会话的隔离级别设置为serializable并开始事务。
-- Tx1 + Tx2:
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE |
+-------------------------+
1 row in set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
好的，两个事务都已启动。现在让我们选择 中的所有帐户transaction 1，并仅选择 中的帐户 1 transaction 2。
-- Tx1:
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 70 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)
-- Tx2:
mysql> select * from accounts where id = 1;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 70 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
1 row in set (0.00 sec)
接下来，返回transaction 1并从其余额中减去 10 美元。
-- Tx1:
mysql> update accounts set balance = balance - 10 where id = 1;
_
有趣的是，这次更新查询被阻止了。因此，基本上transaction 2是 中的选择查询阻止了 中的更新查询transaction 1。
原因是，在serializable隔离级别下，MySQL 隐式地将所有普通SELECT查询转换为SELECT FOR SHARE。并且持有锁的事务SELECT FOR SHARE只允许其他事务访问READ行，但不允许UPDATE或DELETE它们。
所以有了这个锁机制，我们之前看到的数据不一致的情况就不会再发生了。
但是，此锁具有超时期限。因此，如果第二个事务未在该期限内提交或回滚以释放锁，我们将看到锁等待超时错误，如下所示：
mysql> update accounts set balance = balance - 10 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
因此，当您在应用程序中使用serializable隔离级别时，请确保已实施事务重试策略，以防发生超时。
Postgres 中的隔离级别
首先，让我们在这两个终端窗口上启动两个 PostgreSQL 控制台。
# Tx1 + Tx2
❯ docker exec -it postgres12 psql -U root -d simple_bank
psql (12.3)
Type "help" for help.
simple_bank>
获取 Postgres 中的当前隔离级别
在 postgres 中，为了获取当前隔离级别，我们运行以下命令：
simple_bank> show transaction isolation level;
transaction_isolation
-----------------------
read committed
(1 row)
默认情况下是read committed。所以比MySQL中默认的隔离级别低1级。
更改 Postgres 中的隔离级别
更改隔离级别的方式也不同。在 MySQL 中，我们在开始事务之前设置整个会话隔离级别。
但是在Postgres中，我们只能在事务内设置隔离级别，并且它只会对那一个特定事务产生影响。
那么让我们开始transaction 1，并将其隔离级别设置为read uncommitted。
-- Tx1:
simple_bank> begin;
BEGIN
simple_bank> set transaction isolation level read uncommitted;
SET
现在如果我们显示事务隔离级别，我们可以看到它已被更改为read uncommitted。
-- Tx1:
simple_bank> show transaction isolation level;
transaction_isolation
-----------------------
read uncommitted
(1 row)
在 Postgres 中读取未提交的隔离级别
让我们在另一个控制台上做同样的事情transaction 2：
-- Tx2:
simple_bank> begin;
BEGIN
simple_bank> set transaction isolation level read uncommitted;
SET
simple_bank> show transaction isolation level;
transaction_isolation
-----------------------
read uncommitted
(1 row)
好的，现在进入transaction 1，让我们选择所有帐户。
-- Tx1:
simple_bank> select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 100 | USD | 2020-09-06 15:06:44.666424+00
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
(3 rows)
目前有 3 个账户的余额均为 100 美元。在 中transaction 2，我们仅选择 ID 为 1 的账户。
-- Tx2:
simple_bank> select * from accounts where id = 1;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 100 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
然后返回transaction 1并更新其余额。
-- Tx1:
simple_bank> update accounts set balance = balance - 10 where id = 1 returning *;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 90 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
UPDATE 1
这里账户 1 的余额已更改为 90 美元。现在我们在 中再次选择该账户transaction 2：
-- Tx2:
simple_bank> select * from accounts where id = 1;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 100 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
奇怪的是，它仍然是 100 美元！这出乎意料，因为我们使用的是read-uncommitted级别，所以transaction 2应该能够看到未提交的数据transaction 1，对吗？
事实上，如果我们看一下Postgres 的文档，我们就会发现read uncommittedPostgres 的行为与完全相同read committed。
因此，基本上，我们可以说 Postgres 只有 3 个隔离级别，最低级别是。这是有道理的，因为通常我们在任何情况下read committed都不想使用。read uncommitted
好的，让我们继续提交。然后再次transaction 1选择帐户 1 。transaction 2
-- Tx1:
simple_bank> commit;
COMMIT
-- Tx2:
simple_bank> select * from accounts where id = 1;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 90 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
现在它看到已提交的余额：90 美元，正如预期的那样。好的，让我们提交此事务并转到下一个隔离级别。
-- Tx2:
simple_bank> commit;
COMMIT
Postgres 中的读取已提交隔离级别
我将开始 2 个新交易，并将其隔离级别设置为read committed：
-- Tx1 + Tx2:
simple_bank> begin;
BEGIN
simple_bank> set transaction isolation level read committed;
SET
simple_bank> show transaction isolation level;
transaction_isolation
-----------------------
read committed
(1 row)
现在就像之前一样，让我们选择中的所有帐户transaction 1，然后选择中的帐户 1 transaction 2。
-- Tx1:
simple_bank> select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 90 | USD | 2020-09-06 15:06:44.666424+00
(3 rows)
-- Tx2:
simple_bank> select * from accounts where id = 1;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 90 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
simple_bank> select * from accounts where balance >= 90;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 90 | USD | 2020-09-06 15:06:44.666424+00
(3 rows)
除了脏读现象，我们还想看看它如何处理幻读，所以让我们找出所有余额大于或等于 90 美元的账户。目前，所有 3 条记录都满足此搜索条件。
现在让我们回过头来transaction 1从账户 1 的余额中减去 10 美元。
-- Tx1:
simple_bank> update accounts set balance = balance - 10 where id = 1 returning *;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 80 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
-- Tx2:
simple_bank> select * from accounts where id = 1;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 90 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
如果我们在中选择帐户 1 transaction 2，则它仍为 90 美元，因为事务 1 尚未提交。因此在隔离级别dirty read上是不可能的。read-committed
让我们看看如果我们承诺的话会发生什么transaction 1。
-- Tx1:
simple_bank> commit;
COMMIT
-- Tx2:
simple_bank> select * from accounts where id = 1;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 80 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
这次transaction 2可以看到更新后的余额为 80 美元。现在如果我们再次运行查询来搜索至少有 90 美元的账户，我们将只看到 2 条记录，而不是像以前那样看到 3 条。
-- Tx2:
simple_bank> select * from accounts where balance >= 90;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
(2 rows)
更新后的账户 1 的余额不再满足搜索条件，因此它已从结果集中消失。此隔离级别phantom read中发生了这种情况。read-committed
这与 MySQL 中的行为相同。让我们提交此事务并上移 1 级。
-- Tx2:
simple_bank> commit;
COMMIT
Postgres 中的可重复读隔离级别
我将开始 2 个新交易，然后将它们的交易隔离级别设置为repeatable read。
-- Tx1 + Tx2:
simple_bank> begin;
BEGIN
simple_bank> set transaction isolation level repeatable read;
SET
simple_bank> show transaction isolation level;
transaction_isolation
-----------------------
repeatable read
(1 row)
好的，现在让我们选择 中的所有账户transaction 1，然后仅选择 中 ID 为 1 的账户transaction 2。同时搜索余额至少为 80 美元的账户。
-- Tx1:
simple_bank> select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 80 | USD | 2020-09-06 15:06:44.666424+00
(3 rows)
-- Tx2:
simple_bank> select * from accounts where id = 1;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 80 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
simple_bank> select * from accounts where balance >= 80;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 80 | USD | 2020-09-06 15:06:44.666424+00
(3 rows)
现在返回transaction 1并从其余额中减去 10 美元。
-- Tx1:
simple_bank> update accounts set balance = balance - 10 where id = 1 returning *;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
UPDATE 1
此交易中余额已更新为 70 美元。让我们提交它并看看会发生什么transaction 2。
-- Tx1:
simple_bank> commit;
COMMIT
-- Tx2:
simple_bank> select * from accounts where id = 1;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
1 | one | 80 | USD | 2020-09-06 15:06:44.666424+00
(1 row)
现在如果我们选择中的帐户 1 transaction 2，它仍然像以前一样是 80 美元，尽管transaction 1已经提交了更改。
这是因为我们使用了可重复读隔离级别，所以相同的选择查询应该始终返回相同的结果。Non-repeatable read在这种情况下不会发生这种现象。
另外，如果我们重新运行查询来搜索至少有 80 美元的账户：
-- Tx2:
simple_bank> select * from accounts where balance >= 80;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 80 | USD | 2020-09-06 15:06:44.666424+00
(3 rows)
我们仍然得到与之前相同的 3 条记录。因此，phantom read此repeatable read隔离级别也阻止了这种情况。
现在我要尝试运行这个更新帐户余额查询以查看它的行为：
-- Tx2:
simple_bank> update accounts set balance = balance - 10 where id = 1 returning *;
ERROR: could not serialize access due to concurrent update
在 MySQL 的repeatable read隔离级别中，我们已经看到它允许将余额更新为 60 美元。但在 Postgres 中，我们遇到了一个错误：
错误：由于并发更新，无法序列化访问
我认为抛出这样的错误比允许修改余额要好得多，因为它避免了令人困惑的状态，其中交易看到从 80 中减去 10 得到 60。所以向 Postgres 致敬！
-- Tx2:
simple_bank> rollback;
ROLLBACK
Postgres 中的序列化异常
到目前为止，我们已经遇到了 3 种现象：dirty read、non-repeatable read和phantom read。但我们还没有遇到过serialization anomaly。所以这次，让我们看看它会是什么样子。
让我们开始 2 个新事务，并将它们隔离级别设置为repeatable-read。
-- Tx1 + Tx2:
simple_bank> begin;
BEGIN
simple_bank> set transaction isolation level repeatable read;
SET
simple_bank> show transaction isolation level;
transaction_isolation
-----------------------
repeatable read
(1 row)
然后在 中transaction 1，我们选择所有账户记录。
-- Tx1:
simple_bank=# select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00
(3 rows)
现在想象我们有一个用例，我们必须计算所有账户余额的总和，然后使用该总余额创建一个新账户。
因此让我们运行这个命令transaction 1：
-- Tx1:
simple_bank> select sum(balance) from accounts;
sum
-----
270
(1 row)
是 270 美元。然后我们在账户表中插入一条新记录，其中owner“sum”balance是 270，而currency“USD”是。
-- Tx1:
simple_bank=# insert into accounts(owner, balance, currency) values ('sum', 270, 'USD') returning *;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
5 | sum | 270 | USD | 2020-09-15 14:18:31.612735+00
(1 row)
INSERT 0 1
simple_bank=# select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00
5 | sum | 270 | USD | 2020-09-15 14:18:31.612735+00
(4 rows)
OK，现在我们可以在这个中看到新记录了transaction 1。但是，如果transaction 2我们也想执行这个操作该怎么办呢？
由于我们使用了repeatable-read隔离级别，选择查询transaction 2将只能看到原始帐户列表，而看不到事务 1 刚刚插入的新记录。
simple_bank> select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00
(3 rows)
simple_bank> select sum(balance) from accounts;
sum
-----
270
(1 row)
simple_bank> insert into accounts(owner, balance, currency) values ('sum', 270, 'USD') returning *;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
6 | sum | 270 | USD | 2020-09-15 14:14:15.677416+00
(1 row)
INSERT 0 1
因此，它将获得相同的账户余额总和值，即 270 美元。因此，最终将相同的记录插入到账户表中。
好的，现在让我们提交这两个交易，看看会发生什么。
-- Tx1:
simple_bank> commit;
COMMIT
-- Tx2:
simple_bank> commit;
COMMIT
simple_bank> select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00
5 | sum | 270 | USD | 2020-09-15 14:18:31.612735+00
6 | sum | 270 | USD | 2020-09-15 14:14:15.677416+00
(5 rows)
两次提交均成功。并且有 2 条重复的 sum 记录，余额均为 270 美元。
这是一个序列化异常！
为什么？
因为如果这两笔交易是连续运行的，一个接一个，那么我们就不可能有两条总和为 270 的记录。
无论事务 1 还是事务 2 先运行，我们都应该有 1 条 270 美元的记录和另一条 540 美元的记录。
好的，这就是隔离级别serialization anomaly中发生的情况repeatable-read
Postgres 中的可序列化隔离级别
我将开始 2 个新交易，然后将它们的隔离级别设置为serializable。
-- Tx1 + Tx2:
simple_bank> begin;
BEGIN
simple_bank> set transaction isolation level serializable;
SET
simple_bank> show transaction isolation level;
transaction_isolation
-----------------------
serializable
(1 row)
现在让我们选择中的所有账户transaction 1，计算所有余额的总和，并插入一个余额等于该总和的新账户。
-- Tx1:
simple_bank> select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00
5 | sum | 270 | USD | 2020-09-15 14:18:31.612735+00
6 | sum | 270 | USD | 2020-09-15 14:14:15.677416+00
(5 rows)
simple_bank> select sum(balance) from accounts;
sum
-----
810
(1 row)
simple_bank> insert into accounts(owner, balance, currency) values ('sum', 810, 'USD') returning *;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
7 | sum | 810 | USD | 2020-09-15 14:25:20.091212+00
(1 row)
INSERT 0 1
simple_bank> select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00
5 | sum | 270 | USD | 2020-09-15 14:18:31.612735+00
6 | sum | 270 | USD | 2020-09-15 14:14:15.677416+00
7 | sum | 810 | USD | 2020-09-15 14:25:20.091212+00
(6 rows)
现在如您所见，已在中插入了一条新的总计 810 美元的记录transaction 1。让我们转到transaction 2并运行同一系列的查询。
-- Tx2:
simple_bank> select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00
5 | sum | 270 | USD | 2020-09-15 14:18:31.612735+00
6 | sum | 270 | USD | 2020-09-15 14:14:15.677416+00
(5 rows)
simple_bank> select sum(balance) from accounts;
sum
-----
810
(1 row)
simple_bank> insert into accounts(owner, balance, currency) values ('sum', 810, 'USD') returning *;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
8 | sum | 810 | USD | 2020-09-15 14:25:33.060027+00
(1 row)
INSERT 0 1
simple_bank> select * from accounts;
id | owner | balance | currency | created_at
----+-------+---------+----------+-------------------------------
2 | two | 100 | USD | 2020-09-06 15:06:44.666424+00
3 | three | 100 | USD | 2020-09-06 15:06:44.666424+00
1 | one | 70 | USD | 2020-09-06 15:06:44.666424+00
5 | sum | 270 | USD | 2020-09-15 14:18:31.612735+00
6 | sum | 270 | USD | 2020-09-15 14:14:15.677416+00
8 | sum | 810 | USD | 2020-09-15 14:25:33.060027+00
(6 rows)
经过此选择查询后，我们可以看到两个交易中的账户列表几乎完全相同（除了 ID）。
让我们尝试同时提交它们两个。
-- Tx1:
simple_bank=# commit;
COMMIT
-- Tx2:
simple_bank> commit;
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.
已transaction 1成功提交。但是，transaction 2抛出错误：
错误：由于事务之间的读/写依赖关系，无法序列化访问
Postgres 提示我们，如果我们重试，事务可能会成功。
这很好！这种情况serializable anomaly完全被阻止了。两个并发事务不再像以前那样创建重复记录。
我们可以得出结论，Postgres 使用一种dependencies detection机制来检测潜在的错误read phenomena并通过抛出错误来阻止它们。
MySQL 如何处理序列化异常
另一方面，MySQL 选择使用 来locking mechanism实现类似的结果。让我们看看它如何处理serialization anomaly！
让我们打开正在处理的 2 个 MySQL 控制台会话，并将它们事务隔离级别设置为serializable。
-- Tx1 + Tx2:
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| SERIALIZABLE |
+-------------------------+
1 row in set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
现在在中transaction 1，让我们选择所有账户记录，计算所有账户余额的总和，并将包含该总和的新记录插入到账户表中。
-- Tx1:
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 60 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
+----+-------+---------+----------+---------------------+
3 rows in set (0.00 sec)
mysql> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
| 260 |
+--------------+
1 row in set (0.00 sec)
mysql> insert into accounts (owner, balance, currency) values ('sum', 260, 'USD');
Query OK, 1 row affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 60 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
| 4 | sum | 260 | USD | 2020-09-15 14:36:20 |
+----+-------+---------+----------+---------------------+
4 rows in set (0.00 sec)
然后切换到transaction 2并运行查询以选择所有帐户。
-- Tx2
mysql> select * from accounts;
_
可以看到，这个查询被阻塞了，需要等待transaction 1释放锁才能继续。
但一旦我们承诺transaction 1，
-- Tx1:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-- Tx2:
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 60 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
| 4 | sum | 260 | USD | 2020-09-15 14:36:20 |
+----+-------+---------+----------+---------------------+
4 rows in set (46.29 sec)
释放锁，并transaction 2立即获取其查询的结果。
现在我们可以继续在此事务中运行求和并插入查询，并最终提交它。
-- Tx2:
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 60 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
| 4 | sum | 260 | USD | 2020-09-15 14:36:20 |
+----+-------+---------+----------+---------------------+
4 rows in set (46.29 sec)
mysql> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
| 520 |
+--------------+
1 row in set (0.00 sec)
mysql> insert into accounts (owner, balance, currency) values ('sum', 520, 'USD');
Query OK, 1 row affected (0.00 sec)
mysql> select * from accounts;
+----+-------+---------+----------+---------------------+
| id | owner | balance | currency | created_at |
+----+-------+---------+----------+---------------------+
| 1 | one | 60 | USD | 2020-09-06 15:09:38 |
| 2 | two | 100 | USD | 2020-09-06 15:09:38 |
| 3 | three | 100 | USD | 2020-09-06 15:09:38 |
| 4 | sum | 260 | USD | 2020-09-15 14:36:20 |
| 5 | sum | 520 | USD | 2020-09-15 14:39:21 |
+----+-------+---------+----------+---------------------+
5 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
没有重复的 sum 记录。因此 MySQL 也成功地serialization anomaly用其阻止了locking mechanism。
隔离级别与读取现象的关系总结
现在，在结束之前，让我们快速总结一下 MySQL 和 Postgres 中的隔离级别和读取现象之间的关系。
在 MySQL 中
在MySQL中，最低的隔离级别read uncommitted允许所有4种现象发生。
而下一级别：read committed只能防止dirty read。其余 3 种现象仍然可能发生。
MySQL 中的级别repeatable read阻止了前 3 种现象：dirty read、non-repeatable read和phantom read。但它仍然保留了serialization anomaly，甚至一些inconsistent concurrent updates。
最高隔离级别：serializable是最严格的。它可以防止所有 4 种现象。这要归功于locking mechanism。
在 Postgres 中
Postgres 中的隔离级别产生的结果非常相似。但是，仍然存在一些重大差异。
首先，read uncommitted隔离级别的行为与 相同read committed。因此，Postgres 基本上只有 3 个隔离级别，而 MySQL 有 4 个。
