深入了解 MySQL 和 PostgreSQL 中的隔离级别和读取现象

使用时database transactions，我们必须做的一件至关重要的事情就是选择适合isolation level我们应用程序的。虽然有明确的标准，但每个数据库引擎可能选择以不同的方式实现它，因此在每个中的行为可能有所不同isolation level。

MySQL今天，我们将通过运行一些具体的 SQL 查询，深入探讨各个隔离级别的工作Postgres原理。我们还将学习各个隔离级别如何isolation level防止read phenomena诸如dirty read、non-repeatable read、phantom read和之类的问题serialization anomaly。

以下是：

YouTube 上完整系列播放列表的链接
以及它的Github 仓库

事务隔离和读取现象

首先我们来谈谈它的理论。

ACID 属性

正如我们在上一讲中已经学到的，数据库事务必须满足属性，ACID代表，，，和。AtomicityConsistencyIsolationDurability

Isolation是数据库事务的四个属性之一，在最高级别，完美的隔离可确保所有并发事务不会相互影响。

一个事务可能会通过多种方式被与其同时运行的其他事务干扰。这种干扰会导致我们所说的…… read phenomenon。

4 阅读现象

当数据库在较低的事务隔离级别下运行时，可能会出现以下一些读取现象：

首先，dirty read现象。当一个事务读取另一个并发事务写入的尚未提交的数据时，就会发生这种情况。这非常糟糕，因为我们不知道该事务最终会被提交还是回滚。因此，一旦发生回滚，我们最终可能会使用错误的数据。
我们可能遇到的第二种现象是non-repeatable read，当一个事务读取同一条记录两次并看到不同的值时，这是因为该行已被第一次读取后提交的其他事务修改。
Phantom read是一种类似的现象，但会影响搜索多行而不是一行的查询。在这种情况下，相同的查询会被重新执行，但会返回一组不同的行，这是由于其他最近提交的事务所做的一些更改（例如插入新行或删除现有行，而这些行恰好满足当前事务查询的搜索条件）。
另一个涉及分离一组事务的现象是serialization anomaly。如果我们尝试以任何顺序连续运行一组并发提交的事务而不使它们相互重叠，则无法获得它们的结果。

如果你现在还不完全理解这些现象，也不用担心。几分钟后，我们将在 MySQL 和 Postgres 中逐一复现它们。

4个隔离级别

现在为了应对这些现象，美国国家标准协会（ANSI）定义了4个标准隔离级别。

最低隔离级别是read uncommitted。此级别的事务可以看到其他未提交事务写入的数据，从而导致允许dirty read现象发生。
下一个隔离级别是read committed，事务只能查看其他事务已提交的数据。因此，dirty read不再可能。
隔离级别稍微严格一些repeatable read。它确保相同的 select 查询无论执行多少次，始终返回相同的结果，即使其他并发事务已提交满足查询的新更改。
最后，最高隔离级别是serializable。在此级别运行的并发事务保证能够产生与按某种顺序顺序执行相同的结果，一个接一个，不重叠。所以，基本上，这意味着至少存在一种方式来对这些并发事务进行排序，以便如果我们逐个运行它们，最终结果将是相同的。

隔离级别和读现象的关系

好了，现在是时候找出隔离级别和读现象之间的联系了。我们将在 MySQL 和 Postgres 中运行一些不同隔离级别的事务，以找出每个级别可能出现的现象。然后，我们将这些信息填充到以下汇总图表中：

这里我有 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 控制台会话。

还有一个全局隔离级别，它会在所有会话首次启动时应用。我们可以通过在前面的 select 查询中添加 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 isolation，我们将看到它已更改为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，运行这个更新语句，从账户 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。



-- 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;
_

有趣的是，这次更新查询被阻塞了。所以基本上是中的 select 查询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在应用程序中使用隔离级别时，请确保已实施事务重试策略，以防发生超时。

好的，现在我要重新启动它transaction 1，运行选择查询，然后更新帐户 1 的余额。



-- Tx1:
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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)

mysql> update accounts set balance = balance - 10 where id = 1;
_

但这次，我不会让锁等待超时发生。让我们看看如果transaction 2也尝试更新同一个账户 1 的余额会发生什么。



-- Tx2:
mysql> update accounts set balance = balance - 10 where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

-- Tx1:
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (23.59 sec)
Rows matched: 1  Changed: 1  Warnings: 0

现在，发生了死锁，因为现在transaction 2还需要等待来自的选择查询的锁transaction 1。

因此请注意，除此之外lock wait timeout，您还需要注意可能出现的deadlock情况。

现在让我们尝试重新启动这两个交易，然后选择帐户 1。



-- Tx1 + Tx2:
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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)

现在让我们更新账户 1 的余额transaction 1，然后提交transaction 2。



-- Tx1:
mysql> update accounts set balance = balance - 10 where id = 1;
_

-- Tx2:
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

可以看到，我们提交之后transaction 2，锁就立即被释放了，账户1的余额也更新成功了：



-- Tx1:
mysql> update accounts set balance = balance - 10 where id = 1;
Query OK, 1 row affected (3.34 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)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

好了，到目前为止，我们已经体验了 MySQL 中的所有 4 个隔离级别以及它们如何帮助防止某些读取现象。

现在让我们看看它们在 Postgres 中是如何工作的！效果会非常相似，但也有一些区别。

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，由于事务 1 尚未提交，因此余额仍为 90 美元。因此在隔离级别下这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 中的序列化异常

到目前为止，我们已经遇到了三种现象：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 美元。然后我们在 accounts 表中插入一条新记录，其中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)

好的，现在我们可以在这个中看到新记录了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 美元。最终将相同的记录插入到 accounts 表中。

好的，现在让我们提交这两个交易，看看会发生什么。



-- 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。

现在让我们尝试最高级别：serializable看看是否可以阻止这种异常。

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。

现在我想尝试这两个事务中查询的不同顺序。

首先开始事务1，选择所有账户记录，选择所有账户余额的总和。



-- Tx1:
mysql> begin;
Query OK, 0 rows 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> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
|         1040 |
+--------------+
1 row in set (0.00 sec)

然后开始事务2，同样选择所有账户，并计算所有账户余额的总和。



mysql> begin;
Query OK, 0 rows 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> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
|         1040 |
+--------------+
1 row in set (0.00 sec)

这次，两笔交易的金额相同，都是 1040 美元。让我们在中插入一个包含这笔金额的新账户transaction 1。



-- Tx1:
mysql> insert into accounts (owner, balance, currency) values ('sum', 1040, 'USD');
_

它被阻止是因为transaction 2它持有共享锁，阻止其他事务更新。

现在如果我们尝试在中插入一个新的总和帐户transaction 2，



-- Tx2:
mysql> insert into accounts (owner, balance, currency) values ('sum', 1040, 'USD');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

我们将会陷入死锁，因为在这种情况下，两个事务必须互相等待。

并且由于transaction 2因死锁而失败，锁被立即释放，从而允许transaction 1完成其插入查询。



mysql> commit;
Query OK, 0 rows affected (0.01 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 |
|  6 | sum   |    1040 | USD      | 2020-09-15 14:41:26 |
+----+-------+---------+----------+---------------------+
6 rows in set (0.00 sec)

因此，提交后transaction 1，我们可以看到成功插入了一个新的 sum 帐户。数据库与 no 保持一致serialization anomaly。

隔离级别与读现象关系总结

现在，在结束之前，让我们快速总结一下 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是最严格的。它可以防止所有四种现象。这要归功于locking mechanism。

在 Postgres 中

Postgres 中的隔离级别产生的结果非常相似。然而，仍然存在一些主要差异。

首先，read uncommitted隔离级别的行为与相同read committed。因此，Postgres 基本上只有 3 个隔离级别，而不是像 MySQL 那样有 4 个。

其次，Postgres 不像locking mechanismMySQL 那样使用，但它使用了更好的dependencies detection技术来停止non-repeatable read、inconsistent concurrent updates和serialization anomaly。

另外，Postgres 中的默认隔离级别仅为read committed，而repeatable readMySQL 中的默认隔离级别为。

记住

使用高隔离级别时，需要牢记的最重要的一点是，可能会出现错误、超时甚至死锁。因此，我们应该谨慎地为事务实现重试机制。

另外，每个数据库引擎实现的隔离级别可能有所不同。因此，在开始编写代码之前，请务必仔细阅读其文档，并先自行尝试。

参考

以下是有关 MySQL 和 Postgres 隔离级别的官方文档链接，供您参考：

MySQL：MySQL 隔离级别文档
Postgres：Postgres 隔离级别文档

今天的文章就到这里，希望对大家有所帮助。

非常感谢您的阅读，下次讲座再见！

如果您喜欢这篇文章，请订阅我们的 Youtube 频道并在 Twitter 上关注我们，以便将来获取更多教程。

如果你想加入我目前在 Voodoo 的优秀团队，请查看我们的职位空缺。你可以远程办公，也可以在巴黎/阿姆斯特丹/伦敦/柏林/巴塞罗那现场办公，但需获得签证担保。

文章来源：https://dev.to/techschoolguru/understand-isolation-levels-read-phenomena-in-mysql-postgres-c2e

深入了解 MySQL 和 PostgreSQL 中的隔离级别和读取现象

深入了解 MySQL 和 PostgreSQL 中的隔离级别和读取现象

目录：

事务隔离和读取现象

ACID 属性

4 阅读现象

4个隔离级别

隔离级别和读现象的关系

MySQL 中的隔离级别

获取 MySQL 中的当前隔离级别

更改 MySQL 中的隔离级别

MySQL 中的读取未提交隔离级别

MySQL 中的读取已提交隔离级别

MySQL中的可重复读隔离级别

MySQL中的可序列化隔离级别

Postgres 中的隔离级别

获取 Postgres 中的当前隔离级别

更改 Postgres 中的隔离级别

Postgres 中的读取未提交隔离级别

Postgres 中的读取已提交隔离级别

Postgres 中的可重复读隔离级别

Postgres 中的序列化异常

Postgres 中的可序列化隔离级别

MySQL 如何处理序列化异常

隔离级别与读现象关系总结

在 MySQL 中

在 Postgres 中

记住

参考