Isolation as understood by the standard and PostgreSQL

事务隔离及其重要性

每个人或许至少都知道事务的存在，听说过缩写 ACID，听说过隔离级别。但我们仍然碰到过这样的观点，即这是纯理论，在实践中并不是必须的。因此，我准备花一些时间来解释为什么这真的很重要。

如果应用程序从数据库中获取了不正确的数据，或者应用程序将不正确的数据写入数据库，都不会让人感到高兴。

但是什么是所谓的正确数据呢？众所周知，完整性约束，如 not null 或 unique ，这一类是可以在数据库中创建的。如果数据总是满足完整性约束(DBMS保证了这一点)，那么它们就是完整的。

正确性和完整性是一回事吗？并不完全是。并非所有的约束都可以在数据库级指定。有些约束过于复杂，例如，同时包含多个表。即使一般情况下，可以在数据库中定义约束，但由于某些原因没有定义，这并不意味着就可以违反约束。

所以，正确性比完整性更强，但我们不知道这到底意味着什么。我们只能承认，正确的黄金标准是一个应用程序，正如我们愿意相信的那样，是正确编写的，永远不会出错。在任何情况下，如果应用程序没有违反完整性，而是违反了正确性，DBMS不会知道这一点，也不会当场捕获应用程序。

后面我们将使用术语 一致性 来代替 正确性。

然而，让我们假设应用程序只执行正确的操作符序列。如果应用程序是正确的，DBMS的角色是什么？

首先，事实证明，正确的操作序列可以暂时破坏数据一致性，说来也奇怪，这是正常的。一个常见但明显的例子是资金从一个账户转移到另一个账户。一致性规则听起来可能是这样的：一次转账永远不会改变帐户上的总金额 (这个规则很难在SQL中指定为完整性约束，所以它存在于应用程序级别，对DBMS是不可见的)。转账包括两种操作：第一个操作减少一个账户上的资金，第二个操作增加另一个账户上的资金。

基于完整性约束实现以上述的规则是一个很好的练习。

如果第一个操作执行了而第二个操作没有执行怎么办？事实上，在第二次操作期间，可能会发生断电、服务器崩溃、除以零等情况。很明显，一致性会遭到破坏，这是不允许的。一般来说，在应用层面解决此类问题是有可能的，但需要付出巨大的代价，不过幸运的是，这不是必需的：这些是由 DBMS 完成的。但是要做到这一点，DBMS 必须知道这两个操作是一个不可分割的整体，这也就是 事务 。

这很有趣：由于DBMS知道一系列操作构成了一个事务，它通过确保事务是原子性的来帮助维护一致性，而且它在不了解任何特定的一致性规则的情况下就做到了这一点。

但还有一个更微妙的问题。一旦系统中出现多个同时发生的事务 (对于它们各自来说是绝对正确的) ，它们就可能不能正确地一起工作。这是因为操作的顺序混淆了：您不能假设一个事务的所有操作都是先执行，然后再执行另一个事务的所有操作。

关于同时性的注解：实际上，事务可以在具有多核处理器、磁盘阵列的系统上同时运行。但是同样的推理也适用于以分时模式顺序执行命令的服务器：在特定的时钟周期内执行一个事务，在下一个特定周期内执行另一个事务。有时，并发执行该术语只是广义上的。

正确的事务不正确地一起工作的情况称为 并发执行异常anomalies of concurrent execution 。

举个简单的例子：如果一个应用程序想要从数据库中获取正确的数据，它至少不能看到其他未提交事务的变化。否则，不仅可以得到不一致的数据，还可能看到数据库中从未出现过的东西(如果事务被取消)，这种异常现象被称为 脏读dirty-read 。

还有其他更复杂的异常现象，稍后我们将讨论它们。

避免并发执行当然是不可能的：否则我们可以谈论什么样的性能？但是不能使用不正确的数据。

DBMS再一次拯救了我们。我们可以使事务按顺序、一个接一个地执行。换句话说就是彼此隔离。实际上，DBMS可以执行混合的操作，但要确保并发执行的结果与一些可能的顺序执行的结果相同。这样就排除了任何可能的异常。

所以我们进行如下定义：

事务是由应用程序执行的一组操作，它将数据库从一个正确的状态转移到另一个正确的状态 (一致性)，前提是该事务已完成 (原子性) 并且不受其他事务的干扰 (隔离性)。

这个定义结合了ACID的前三个字母。它们彼此紧密地联系在一起，因此只考虑一个而不考虑其他的是没有意义的。事实上，它也很难脱离 D(耐久性)。实际上，当系统崩溃时，它仍然包含有未提交事务所做的更改，您需要做一些事情来恢复数据一致性。

一切本来都很好，但是要实现完全隔离是一项从技术上来说十分困难的任务，会减少系统吞吐量。因此，在实践中经常 (不总是，但几乎总是) 使用弱隔离性，这可以防止一些 (但不是所有) 的异常。这意味着确保数据正确性的部分工作得由应用程序完成。因此，了解系统中使用的隔离级别、它能提供什么保证、不能提供什么，以及如何在这种情况下编写正确的代码是非常重要的。

SQL标准中的隔离级别和异常

SQL标准中对于四种隔离级别进行了很长的描述。这些级别是通过列出在此级别上同时执行的事务允许或不允许出现的异常来进行定义的。因此，要讨论这些级别，有必要了解这些异常。

我要强调的是，在这一部分中，我们讨论的是标准，也就是理论，它是实践的重要基础，但与此同时，它也有很大的分歧。因此，这里所有的例子都是推测性的。他们会对客户的账户进行相同操作：这是相当明显的，尽管不可否认，这与银行业务在现实中是如何组织的无关。

Lost update

让我们从 更新丢失 开始。当两个事务读取表中的同一行，然后一个事务更新该行，然后第二个事务也更新同一行，而不考虑第一个事务所做的更改时，就会发生这种异常。

例如，两笔交易将使同一账户的金额增加 ₽100 (₽是俄罗斯卢布的货币符号)。第一个事务读取到当前值(1000₽)，然后第二个事务读取相同的值。第一个事务增加金额 (为1100₽) 并写入该值。第二个事务的操作方式相同：它获得相同的1100₽并写入该值。结果，这位客户损失了100卢布。

SQL标准在任何级别都不会发生 更新丢失。

Dirty read and Read Uncommitted

脏读Dirty read 我们已经相当熟悉了。当一个事务读取另一个事务尚未提交的更改时，就会发生这种异常。

例如，第一个事务将所有资金从客户的帐户转移到另一个帐户，但不提交更改。另一个事务读取帐户余额，获得0，并拒绝客户提取现金，尽管第一个事务中止并恢复了所作的更改，所以数据库中从未存在过资金变为0的情况。

SQL标准允许 Read Uncommitted 级别存在 脏读 。

Non-repeatable read and Read Committed

当一个事务两次读取同一行时，就会发生 不可重复读 的异常，在读取期间，第二个事务修改 (或删除) 该行并提交了更改。然后第一个事务将得到不同的结果。

例如，首先让一致性规则来禁止客户帐户上的金额变成负数。第一笔交易将使账户上的金额减少100。它检查当前值，1000，并认为减少是可以的。同时，第二个事务将帐户上的金额减少到零，并提交更改。如果第一个事务现在重新检查金额，它将得到0 (但是它已经决定减少金额了，并且帐户进入红色)。

SQL标准允许 Read Uncommitted 和 Read Committed 级别出现 不可重复读 。但是 Read Committed 不允许 脏读 。

Phantom read and Repeatable Read

当一个事务按照相同的条件两次读取一组行时，就会发生 幻读 ，在读取期间，第二个事务添加了满足条件的行 (并提交了更改) 。然后，第一个事务将获得不同的行结果集。

例如，首先让一致性规则来阻止一个客户拥有3个以上的帐户。第一个事务打开了一个新帐户，并检查当前帐户的数量(例如，2)，并决定是否可以打开。与此同时，第二个事务还为客户打开一个新帐户并提交了更改。现在，如果第一个交易重新检查数量，它将得到3个 (但它已经开设了另一个帐户，客户似乎有4个)。

SQL标准允许 Read Uncommitted、Read Committed 和 Repeatable Read 三种级别出现 幻读 。然而，在 Repeatable Read 级别，是不允许 不可重复读 的。

The absence of anomalies and Serializable

SQL标准还定义了一种新的隔离级别 —— Serializable可串行化，这种级别不会导致任何异常。这与禁止 更新丢失 和 脏读 、不可重复读或 幻读 是不同的。

问题是，我们知道的异常比标准中列出的要多得多，还有很多未知的异常。

可串行化 级别必须防止所有的异常。这意味着在这个级别上，应用程序开发人员不需要考虑并发执行。如果事务按照一个正确的顺序分别执行，那么当这些事务并发执行时，数据也会保持一致。

汇总表

现在我们可以提供一个汇总表了。此处在最后增加了一列以展示的更加清晰，这个在SQL标准中是不包含的

为什么仅有这些异常？

为什么SQL标准只列出了众多可能发生的异常现象中的一小部分，而它们又恰恰是这些？

似乎没有人知道确切的答案。但在这里，实践显然比理论超前，所以当时 (对于SQL92标准) 可能没有考虑到其他异常。

此外，还假定 隔离 必须建立在锁的基础之上。广泛使用的 两阶段锁定协议 (2PL) 背后的思想是，在执行期间，一个事务锁住它正在处理的行，并在完成时释放锁。这大大得到了简化，事务获得的锁越多，它与其他事务的隔离效果越好。但是系统的性能也会受到更大的影响，因为事务会开始为相同的行排队，而不是一起工作。

我的观点是，正是所需的锁的数量导致了标准隔离级别之间的差异。

如果事务通过更新而不是读取来锁定要修改的行，那么我们将获得 Read Uncommitted 级别：不允许更新丢失，但是可以读到未提交的数据。
如果一个事务同时通过读取和更新中锁定要修改的行，那么我们会得到 Read Committed 级别：你不能读取未提交的数据，但是当你再次访问该行时，你可以得到一个不同的值 (不可重复读取) 。
如果一个事务同时锁住了准备要读取和要修改的行，并且同时锁住了正在读取和更新的行，那么我们就得到了 Repeatable read 级别：重新读取行会返回相同的值。

但是 Serializable 有一个问题：您不能锁定还不存在的行。因此，幻读 仍然是可能的：另一个事务可能添加 (但不删除) 符合以前执行的查询条件的行，并且下次查询时，该行会被获取到。

因此，要实现Serializable级别，普通的锁是不够的，您还需要锁定条件 (谓词) 而不是行。因此，这种锁称为谓词。它们在1976年被提出，但是它们的实际适用性受到相当简单的条件限制，在这些条件下，很清楚如何连接两个不同的谓词。据我所知，这种锁从未在任何系统中实现过。

Isolation levels in PostgreSQL

随着时间的推移，基于锁的事务管理协议被 快照隔离协议(SI) 所取代。它的思想是，每个事务在特定时间点使用一致性数据快照进行工作，只有在创建快照之前所提交的更改，才会进入到快照。

这种隔离级别会自动禁止 脏读。虽然在形式上可以在PostgreSQL中指定 Read Uncommitted 的级别，但它的工作方式与 Read Committed 完全相同。因此，我们后续不会讨论 Read Uncommitted 级别。

PostgreSQL实现了该协议的一个多版本变种。多版本并发的思想是同一行的多个版本可以在DBMS中共存。这允许您使用现有的数据版本构建快照，并使用最少的锁。实际上，只有对同一行的后续更改才会被锁定。所有其他操作都是同时执行的：写事务从不锁定只读事务，而只读事务从不锁定任何内容。

通过使用数据快照，PostgreSQL中的隔离级别比SQL标准要求的更严格：Repeatable Read级别不仅不允许 non-repeatable reads，而且也不允许 幻读 (尽管它没有提供完全隔离)。这是在不损失效率的情况下实现的。

PostgreSQL：

SQL标准：

我们将在下一篇文章中讨论多版本并发是如何在底层实现的，现在我们将从用户的角度详细研究这三个级别 (如您所知，最有趣的是隐藏在其他异常之后的东西) 。为此，让我们创建一个帐户表。Alice和Bob各有1000卢布，并且Bob有两个账户：

=> CREATE TABLE accounts(
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  number text UNIQUE,
  client text,
  amount numeric
);
=> INSERT INTO accounts VALUES
  (1, '1001', 'alice', 1000.00),
  (2, '2001', 'bob', 100.00),
  (3, '2002', 'bob', 900.00);

Read Committed

The absence of dirty read

很容易确保不会读取到脏数据。我们开始一个事务。默认情况下，它会使用“读已提交”的隔离级别

=> BEGIN;
=> SHOW transaction_isolation;
 transaction_isolation 
-----------------------
 read committed
(1 row)

更准确地说，默认级别是由参数设置的，如果需要，可以更改该参数

=> SHOW default_transaction_isolation;
 default_transaction_isolation 
-------------------------------
 read committed
(1 row)

因此，在打开的事务中，我们从帐户中提取资金，但不提交更改。事务只能看到自己的更改

=> UPDATE accounts SET amount = amount - 200 WHERE id = 1;
=> SELECT * FROM accounts WHERE client = 'alice';
 id | number | client | amount 
----+--------+--------+--------
  1 | 1001   | alice  | 800.00
(1 row)

在第二个会话中，我们将启动另一个具有相同 Read Committed 级别的事务。为了区分两个事务，第二个事务的命令将缩进并以条标记。为了重复上面的命令(这很有用)，您需要打开两个终端，并在每个终端中运行psql。在第一个终端中，您可以输入一个事务的命令，在第二个终端中输入另一个事务的命令。

|  => BEGIN;
|  => SELECT * FROM accounts WHERE client = 'alice';
|   id | number | client | amount 
|  ----+--------+--------+---------
|    1 | 1001   | alice  | 1000.00
| (1 row)

正如预期的那样，另一个事务不会看到未提交的更改，因为PostgreSQL不允许脏读。

Non-repeatable read

现在让第一个事务进行提交，第二个事务重新执行相同的查询。

=> COMMIT;

| => SELECT * FROM accounts WHERE client = 'alice';
|   id | number | client | amount 
|  ----+--------+--------+--------
|    1 | 1001   | alice  | 800.00
| (1 row)
|  => COMMIT;

查询已经获得了最新的数据，这是 不可重复读 异常，这在 Read Committed 的级别是允许的。

实际的结论是：在事务中，您不能基于前一个操作读取的数据来做出决策，因为操作符的执行之间可能会发生变化。下面是一个示例，它的变体在应用程序代码中经常出现，因此被认为是一个经典的反面教材：

     IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
        UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
      END IF;

在检查和更新期间，其他事务可以以任何方式更改帐户的状态，所以这样的检查不会有任何保障。在一个事务的操作期间，其他事务的任何其他操作都可以楔入，例如，如下所示

      IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
       -----
      |   UPDATE accounts SET amount = amount - 200 WHERE id = 1;
      |   COMMIT;
       -----
        UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
      END IF;

如果重新排列操作会破坏一切，那么代码就写错了。不要欺骗自己说这样的巧合不会发生，肯定会的。

但是如何正确地编写代码呢？可选项如下：

不写代码：这并不是一个玩笑。例如，在本例中，很容易使用完整性约束进行检查：
```
ALTER TABLE accounts ADD CHECK amount >= 0;
```
这样就不需要检查：只需执行操作，并在必要时处理试图违反完整性约束时发生的异常。
使用单条SQL语句。一致性问题的出现是因为在操作之间的时间间隔内，另一个事务可能会完成，这会改变可见的数据。如果只有一个算子即一条SQL，那么就没有所谓的时间间隔。PostgreSQL有足够的技术用一条SQL语句解决复杂的问题。比如通用表达式(CTE)，其中，可以使用 INSERT/UPDATE/DELETE 语句，以及 INSERT ON CONFLICT 语句，它可以实现 “插入，如果该行已经存在则更新” 的操作。
锁：最后一种方法是在所有必需的行(SELECT FOR UPDATE)甚至整个表(lock table)上手动设置排他锁。这总是有效的，但是会抵消多版本并发性的好处：一些操作会顺序执行而不是并发执行。

Inconsistent read

在进入下一个隔离级别之前，您必须承认，它并不像听起来那么简单。PostgreSQL的实现是这样的，它允许其他的、不太为人所知并且不在标准规范里的异常。

假设第一个事务开始，将资金从一个Bob的帐户到另一个Bob的帐户

=> BEGIN;
=> UPDATE accounts SET amount = amount - 100 WHERE id = 2;

同时，另一个事务计算Bob的余额，并在所有Bob的帐户上循环执行计算。实际上，事务从第一个帐户开始(并且，很明显，看到了以前的状态)

|  => BEGIN;
|  => SELECT amount FROM accounts WHERE id = 2;
|   amount 
|  --------
|   100.00
|  (1 row)

此时，第一个事务成功完成

=> UPDATE accounts SET amount = amount + 100 WHERE id = 3;
=> COMMIT;

另一个读取第二个帐户的状态(并且已经看到了新值)

|  => SELECT amount FROM accounts WHERE id = 3;
|   amount 
|  ---------
|   1000.00
|  (1 row)
|  => COMMIT;

因此，第二个事务总共得到1100，即不正确的数据。这是一个 Inconsistent read 的异常。

如何在读已提交的级别内避免这样的异常？当然可以，使用一个操作符即可。例如

SELECT sum(amount) FROM accounts WHERE client = 'bob';

到这里为止，我断言数据可见性只能在操作符之间改变，但这有那么明显吗？如果查询需要很长时间，它是否可以看到处于一种状态的一部分数据和处于另一种状态的一部分数据？

让我们检查一下。一种简便的方法是通过调用pg_sleep函数在操作符中使其延迟。以秒为单位指定延迟时间。

=> SELECT amount, pg_sleep(2) FROM accounts WHERE client = 'bob';

在执行这个操作时，我们将资金转回另一笔事务中

|  => BEGIN;
|  => UPDATE accounts SET amount = amount + 100 WHERE id = 2;
|  => UPDATE accounts SET amount = amount - 100 WHERE id = 3;
|  => COMMIT;

结果显示，操作符看到的数据处于操作符开始执行时的状态。这无疑是正确的。

 amount  | pg_sleep 
---------+----------
    0.00 | 
 1000.00 | 
(2 rows)

但这里也没那么简单。PostgreSQL允许自定义函数，而函数具有 稳定性 的概念。如果在一个查询中调用了一个VOLATILE函数，并且在该函数中执行了另一个查询，函数内部的查询将看到与主查询中的数据不一致的数据。

=> CREATE FUNCTION get_amount(id integer) RETURNS numeric AS $$
  SELECT amount FROM accounts a WHERE a.id = get_amount.id;
$$ VOLATILE LANGUAGE sql;

=> SELECT get_amount(id), pg_sleep(2)
FROM accounts WHERE client = 'bob';

|  => BEGIN;
|  => UPDATE accounts SET amount = amount + 100 WHERE id = 2;
|  => UPDATE accounts SET amount = amount - 100 WHERE id = 3;
|  => COMMIT;

在这种情况下，我们得到了不正确的数据，丢失了100

 get_amount | pg_sleep 
------------+----------
     100.00 | 
     800.00 | 
(2 rows)

我要强调的是，这种效果仅在 Read Committed 隔离级别上可能出现，而且仅在 VOLATILE 函数上。问题是，在默认情况下，使用的正是这个隔离级别和这个 稳定性 。小心不要掉入陷阱！

Inconsistent read in exchange for lost changes

在更新期间，我们还可以在单个操作符中获取到 Inconsistent read ，尽管方式有些出人意料。

让我们看看当两个事务试图修改同一行时会发生什么。现在Bob在两个账户上总共有1000

=> SELECT * FROM accounts WHERE client = 'bob';
 id | number | client | amount 
----+--------+--------+--------
  2 | 2001   | bob    | 200.00
  3 | 2002   | bob    | 800.00
(2 rows)

现在开启一个事务，减少Bob的账户余额

=> BEGIN;
=> UPDATE accounts SET amount = amount - 100 WHERE id = 3;

同时，在另一笔交易中，所有总余额等于或大于1000英镑的账户都会产生利息

|  => UPDATE accounts SET amount = amount * 1.01
|  WHERE client IN (
|    SELECT client
|    FROM accounts
|    GROUP BY client
|    HAVING sum(amount) >= 1000
|  );

UPDATE操作的执行由两部分组成。首先，真正去执行SELECT，获取出满足条件的要更新的行。因为没有提交第一个事务中的更改，所以第二个事务无法看到它，并且更改不会影响到会产生利息的行。那么此时，Bob的账户是符合条件的，一旦执行更新，他的余额应该增加10。

执行的第二阶段是逐个更新所选的行。此时，第二个事务被强制挂起，因为id = 3的行已经被第一个事务锁定。

同时，第一个事务提交更改

=> COMMIT;

结果会怎样？

=> SELECT * FROM accounts WHERE client = 'bob';
 id | number | client | amount 
----+--------+--------+----------
  2 | 2001   | bob    | 202.0000
  3 | 2002   | bob    | 707.0000
(2 rows)

一方面，UPDATE操作不应该看到第二个事务的更改。但另一方面，它不应该丢失在第二个事务中提交的更改。

一旦锁被释放，UPDATE操作会重新读取它试图更新的行(但仅是这一行)。结果，Bob在900的基础上增加了9卢布。但是，如果Bob有900卢布，那么他的账户根本就不应该被选上。

因此，事务获取到了不正确的数据：一些行在某个时间点是可见的，而另一些行在另一个时间点是可见的。我们再次碰到了不一致读Inconsistent read 的异常，而不是更新丢失Lost update。

细心的读者可能注意到，在应用的帮助下，即使在Read Committed级别，也可能 更新丢失。例如
 x := (SELECT amount FROM accounts WHERE id = 1);
 UPDATE accounts SET amount = x + 100 WHERE id = 1;
这不能怪数据库：数据库获得了两条SQL语句，却并不知道 x + 100 的值与账户的金额有某种关系。尽量避免以这种方式写代码。

Repeatable Read

The absence of non-repeatable and phantom reads

该隔离级别的名称假设是可重复读Repeatable Read 的，让我们来检查一下，同时确保没有 幻读 。为此，在第一个事务中，我们将Bob的帐户恢复到以前的状态，并为Charlie创建一个新帐户

=> BEGIN;
=> UPDATE accounts SET amount = 200.00 WHERE id = 2;
=> UPDATE accounts SET amount = 800.00 WHERE id = 3;
=> INSERT INTO accounts VALUES
  (4, '3001', 'charlie', 100.00);
=> SELECT * FROM accounts ORDER BY id;
 id | number | client | amount 
----+--------+---------+--------
  1 | 1001   | alice   | 800.00
  2 | 2001   | bob     | 200.00
  3 | 2002   | bob     | 800.00
  4 | 3001   | charlie | 100.00
(4 rows)

在第二个会话中，我们通过在BEGIN命令中指定 Repeatable Read 级别来启动事务 (第一个事务的级别是无关紧要的)。

|  => BEGIN ISOLATION LEVEL REPEATABLE READ;
|  => SELECT * FROM accounts ORDER BY id;
|   id | number | client | amount 
|  ----+--------+--------+----------
|    1 | 1001   | alice  |   800.00
|    2 | 2001   | bob    | 202.0000
|    3 | 2002   | bob    | 707.0000
|  (3 rows)

现在，第一个事务提交更改，第二个事务重新执行相同的查询。

=> COMMIT;

| => SELECT * FROM accounts ORDER BY id;
|   id | number | client | amount 
|  ----+--------+--------+----------
|    1 | 1001   | alice  | 800.00
|    2 | 2001   | bob    | 202.0000
|    3 | 2002   | bob    | 707.0000
|  (3 rows)
|  => COMMIT;

第二个事务仍然看到与开始时完全相同的数据：对现有行或新行的更改是不可见的。在这个级别上，您可以无需担心两个操作符之间可能发生的变化。

Serialization error in exchange for lost changes

我们在前面讨论过，当两个事务在 Read Committed 级别更新同一行时，可能会发生读取不一致的异常。这是因为等待的事务会重新读取锁定的行，因此在同一时间点不会看到它与其他的行。

在 可重复读 的级别，不允许出现这种异常，但如果发生这种情况，则无法执行任何操作，因此事务将以 序列化错误serialization error 终止。让我们通过同样的增长利息的例子来进行验证：

=> SELECT * FROM accounts WHERE client = 'bob';
 id | number | client | amount 
----+--------+--------+--------
  2 | 2001 | bob | 200.00
  3 | 2002 | bob | 800.00
(2 rows)
=> BEGIN;
=> UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;

|  => BEGIN ISOLATION LEVEL REPEATABLE READ;
|  => UPDATE accounts SET amount = amount * 1.01
|  WHERE client IN (
|    SELECT client
|    FROM accounts
|    GROUP BY client
|    HAVING sum(amount) >= 1000
|  );

=> COMMIT;

|  ERROR: could not serialize access due to concurrent update
|  => ROLLBACK;

数据是保持一致的：

=> SELECT * FROM accounts WHERE client = 'bob';
 id | number | client | amount 
----+--------+--------+--------
  2 | 2001   | bob    | 200.00
  3 | 2002   | bob    | 700.00
(2 rows)

同样的错误也会发生在有其他竞争去更新该行的情况下，即使我们所关注的列实际上并没有发生更改。

实际结论是：如果应用程序对写事务使用 Repeatable Read 的隔离级别，那么它必须准备好重新执行 报序列化错误serialization error 的事务。对于只读事务，这个结果是不可能的。

Inconsistent write (write skew)

因此，在PostgreSQL中，在 可重复读 的隔离级别上，SQL标准中描述的所有异常都被阻止了。但并非所有的异常都是普遍存在的。结果发现有两种异常仍然可能存在。(这不仅适用于PostgreSQL，也适用于其他快照隔离的实现)

第一个异常是不一致写Inconsistent write。

假设让以下一致性规则成立：如果客户所有账户的总金额不为负数，则允许客户账户上存在负数的金额。

第一笔交易得到Bob账户上的金额为900卢布。

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT sum(amount) FROM accounts WHERE client = 'bob';
  sum 
--------
 900.00
(1 row)

第二个交易获得相同的金额。

|  => BEGIN ISOLATION LEVEL REPEATABLE READ;
|  => SELECT sum(amount) FROM accounts WHERE client = 'bob';
|    sum 
|  --------
|   900.00
| (1 row)

第一笔交易正确地认为一个账户的金额可以减少600。

=> UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;

而第二笔交易也得出了同样的结论。但它减少了另一个账户的金额

|  => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
|  => COMMIT;

=> COMMIT;
=> SELECT * FROM accounts WHERE client = 'bob';
 id | number | client | amount 
----+--------+--------+---------
  2 | 2001   | bob    | -400.00
  3 | 2002   | bob    | 100.00
(2 rows)

我们想方设法使Bob的余额变成红色，尽管每个事务都单独正常工作。

Read-only transaction anomaly

这是可重复读级别上可能出现的第二个也是最后一个异常。为了演示，需要三个事务，其中两个事务会更改数据，第三个事务仅读取数据。但首先让我们恢复Bob的帐户状态：

=> UPDATE accounts SET amount = 900.00 WHERE id = 2;
=> SELECT * FROM accounts WHERE client = 'bob';
 id | number | client | amount
----+--------+--------+--------
  3 | 2002   | bob    | 100.00
  2 | 2001   | bob    | 900.00
(2 rows)

在第一笔交易中，Bob所有账户的可用金额会产生利息。利息存入他的一个帐户

=> BEGIN ISOLATION LEVEL REPEATABLE READ; -- 1
=> UPDATE accounts SET amount = amount + (
  SELECT sum(amount) FROM accounts WHERE client = 'bob'
) * 0.01
WHERE id = 2;

然后，另一个交易从另一个Bob的帐户中提取资金，并提交其更改

|  => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2
|  => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
|  => COMMIT;

如果此时提交了第一笔交易，则不会发生异常：我们可以假设首先执行第一笔交易，然后是第二笔(但反之则不然，因为第一笔交易在第二笔交易更改帐户之前看到了 id = 3 的帐户状态)。

但是假设此时第三个 (只读) 事务开始了，它读取到不受前两个事务影响的某个帐户的状态

|  => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 3
|  => SELECT * FROM accounts WHERE client = 'alice';
|   id | number | client | amount 
|  ----+--------+--------+--------
|    1 | 1001   | alice  | 800.00
|  (1 row)

只有在那之后，第一个事务才完成

=> COMMIT;

第三个交易现在应该看到什么状态？

|    SELECT * FROM accounts WHERE client = 'bob';

一旦启动，第三个事务可以看到第二个事务 (已经提交) 的更改，但不能看到第一个事务 (还没有提交) 的更改。另一方面，我们在上面已经确定，第二个事务应该在第一个事务之后启动。无论第三个事务看到的是什么状态，都将是不一致的，这只是只读事务的异常。但是在可重复读取级别是允许的

|    id | number | client | amount
|   ----+--------+--------+--------
|     2 | 2001   | bob    | 900.00
|     3 | 2002   | bob    | 0.00
|   (2 rows)
|   => COMMIT;

Serializable

Serializable 级别可以防止所有可能的异常。事实上，Serializable 是构建在快照隔离之上的。那些在可重复读中不会发生的异常 (例如脏读、不可重复读或幻读) 也不会在可序列化级别上发生。并且检测到那些发生的异常(不一致写和只读事务异常)，事务将会中止，也就是熟悉的序列化错误：could not serialize access.

Inconsistent write (write skew)

为了说明这一点，让我们用一个不一致写异常的情况重复这个场景

=> BEGIN ISOLATION LEVEL SERIALIZABLE;
=> SELECT sum(amount) FROM accounts WHERE client = 'bob';
   sum 
----------
 910.0000
(1 row)

|   => BEGIN ISOLATION LEVEL SERIALIZABLE;
|   => SELECT sum(amount) FROM accounts WHERE client = 'bob';
|      sum 
|   ----------
|    910.0000
|   (1 row)

=> UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;

|   => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
|   => COMMIT;

=> 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.

与 Repeatable Read 级别一样，使用 Serializable 隔离级别的应用程序必须重新执行报序列化错误的事务，正如错误消息提示的那样。

我们获得了编程的简单性，但这样做的代价是被迫终止一些事务，并需要重复执行。问题是，这个比例有多大。如果那些与其他事务不兼容的事务被终止，那就好了。但是这样的实现必然是资源密集型和低效的，因为您必须跟踪每一行上的操作。

实际上，PostgreSQL的实现是这样的，它允许误报：一些绝对正常的事务，只是运气不好，也会中止。正如我们稍后将看到的，这取决于许多因素，如适当索引的可用性或可用RAM的数量。此外，还有一些其他(相当严格的)实现限制，例如，Serializable 级别的查询不能在副本standby上工作，并且它们不会使用并行执行计划。虽然改进实现的工作仍在继续，但现有的限制使这种级别的隔离缺乏吸引力。

Parallel plans will appear as early as in PostgreSQL 12 (patch). And queries on replicas can start working in PostgreSQL 13 (another patch).

Read-only transaction anomaly

为了使只读事务不会导致异常并且不会受到影响，PostgreSQL 提供了一个有趣的技术：这样的事务可以被锁定，直到它的执行是安全的。这是唯一一种可以通过行更新锁定 SELECT 运算符的情况。

=> UPDATE accounts SET amount = 900.00 WHERE id = 2;
=> UPDATE accounts SET amount = 100.00 WHERE id = 3;
=> SELECT * FROM accounts WHERE client = 'bob' ORDER BY id;
 id | number | client | amount 
----+--------+--------+--------
  2 | 2001   | bob    | 900.00
  3 | 2002   | bob    | 100.00
(2 rows)

=> BEGIN ISOLATION LEVEL SERIALIZABLE; -- 1
=> UPDATE accounts SET amount = amount + (
  SELECT sum(amount) FROM accounts WHERE client = 'bob'
) * 0.01
WHERE id = 2;

|  => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 2
|  => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
|  => COMMIT;

第三个事务显式声明为READ ONLY和DEFERRABLE

|   => BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3
|   => SELECT * FROM accounts WHERE client = 'alice';

当尝试执行查询时，事务将被锁定，因为不然的话会导致异常。

=> COMMIT;

只有在提交了第一个事务之后，第三个事务才会继续执行

|    id | number | client | amount
|   ----+--------+--------+--------
|     1 | 1001   | alice  | 800.00
|   (1 row)

|   => SELECT * FROM accounts WHERE client = 'bob';

|    id | number | client | amount 
|   ----+--------+--------+----------
|     2 | 2001   | bob    | 910.0000
|     3 | 2002   | bob    | 0.00
|   (2 rows)

|   => COMMIT;

另一个重要的注意事项：如果使用 Serializable 隔离级别，应用程序中的所有事务都必须使用此级别。不能将 Read committed(或Repeatable Read )事务与 Serializable 事务混合使用。也就是说，你可以混合使用，但是 Serializable 的行为就像 Repeatable Read 一样，没有任何警告。稍后在讨论实现时，我们将讨论为什么会发生这种情况。因此，如果您决定使用 Serializble ，最好全局设置默认级别 (当然，这不会阻止您显式地指定一个错误的级别)

ALTER SYSTEM SET default_transaction_isolation = 'serializable';

You can find a more rigorous presentation of the issues related to transactions, consistency and anomalies in the book and lecture course by Boris Novikov “Fundamentals of database technologies” (available in Russion only).

该使用什么样的隔离级别？

在PostgreSQL中默认使用 Read Committed 隔离级别，并且在绝大多数应用程序中很可能都使用这个级别。这个默认设置很方便，因为在这个级别上，只有在失败的情况下事务中止才可能发生，而不是作为防止不一致的一种手段。换句话说，序列化错误不会发生。

另一方面是大量可能的异常，这些都已经在上面详细讨论过了。软件工程师必须时刻记住它们，并编写代码以防止它们的出现。如果不能在单个SQL语句中编写必要的操作，就必须求助于显式锁定explicit locking 。最麻烦的是，代码很难测试到不一致数据的相关错误，而且错误本身可能是不可预测和无法复现的，因此很难修复。

可重复读Repeatable Read 隔离级别消除了一些不一致问题，但是，并不是全部。因此，您不仅必须记住其余的异常，还必须修改应用程序，使其正确地处理序列化错误。这当然不方便。但是对于只读事务，这个级别完美地补充了 Read Committed ，并且非常方便，例如，用于生成使用多个SQL查询的报告。

最后， Serializable 级别可以使你完全不必担心发生不一致，这极大地简化了代码。应用程序唯一需要的是在获得序列化错误时需要重复执行事务。但是部分中止的事务、额外的开销以及无法并行化查询会显著降低系统吞吐量。还请注意，Serializable级别不适用于standby，并且不能与其他隔离级别混合使用。

小结

对于 Inconsistent read 一小节函数稳定性的一点说明，自己按照流程来复现可能不太好复现，其实理解一下

STABLE and IMMUTABLE functions use a snapshot established as of the start of the calling query, whereas VOLATILE functions obtain a fresh snapshot at the start of each query they execute.

意思就是，volatile的函数会每次在查询之前获取一次快照snapshot，所以为什么会获取出100和800呢，因为第一次查询，查询出100，然后第一个事务进行了提交，也就是如下的SQL提交了，然后此时根据 id = 3 进行了第二次查询，获取了新的快照，所以获取出来的值变成了800，少了100，也就是文中所说的不一致读Inconsistent read 。

|  => BEGIN;
|  => UPDATE accounts SET amount = amount + 100 WHERE id = 2;
|  => UPDATE accounts SET amount = amount - 100 WHERE id = 3;
|  => COMMIT;

我进行了改造，如下：

CREATE or replace FUNCTION get_amount(id integer) RETURNS void
AS $$
declare
	tmp numeric;
begin
	SELECT amount into tmp FROM accounts a WHERE a.id = get_amount.id; 
	raise notice '%',tmp;
	perform pg_sleep(5);
end;
$$ VOLATILE LANGUAGE plpgsql;

postgres=# SELECT get_amount(id) FROM accounts WHERE client = 'bob';
NOTICE:  100.00
NOTICE:  800.00
 get_amount 
------------


(2 rows)

这样的话，就更容易可以复现出来了。其实就是类似于Read commited的隔离级别，每次SQL开始前获取一次snapshot。关于函数稳定性，多唠几句，Oracle创建pl/sql函数时，有一个参数DETERMINISTIC，含义时只要输入的参数一样，返回的结果一定一样。PostgreSQL里面，就对应到函数稳定性了。

immutable,超级稳定,任何时候调用,只要函数的参数不变结果就不变。如果参数为常量或者无参数,在生成执行计划时,直接将这类函数替换为常量。
stable,稳定,在一个事务中调用时,只要函数的参数不变结果就不变。同样的参数值, stable函数多次执行返回的结果应该一致.
volatile指函数可以修改数据库(比如update), 函数参数值相同的情况下, 可以返回不同的结果, 所以volatile函数在执行过程中优化器对它的处理是每一行都需要执行一次volatile函数.

函数的稳定性会影响数据库的一些行为：

绑定变量,immutable函数(包含常量参数或不包含参数时)计算一次。stable函数每次bind的时候要重算。
生成执行计划,stable, immutable函数作为where条件时,可以被用于索引am。(即允许采用索引优化)
排除分区表不需要访问的分区,stable, immutable函数作为where条件时,可用于过滤不需要访问的子表。
是否可用于创建索引,只有immutable函数或操作符,可以用于创建表达式索引。
这里要注意的是volatile, stable, immutable这几种函数, 对数据的修改的可见性分两种情况。volatile , 调用该函数的SQL对数据的修改, 可见；stable, immutable , 调用该函数的SQL对数据的修改, 不可见.

强烈建议各位按照流程全部跑一遍，会对并发异常这些事更加熟悉！

前文译自：https://habr.com/en/company/postgrespro/blog/467437/

PostgreSQL mvcc之并发异常那点事

Isolation as understood by the standard and PostgreSQL

事务隔离及其重要性

SQL标准中的隔离级别和异常

Lost update

Dirty read and Read Uncommitted

Non-repeatable read and Read Committed

Phantom read and Repeatable Read

The absence of anomalies and Serializable

汇总表

为什么仅有这些异常？

Isolation levels in PostgreSQL

Read Committed

The absence of dirty read

Non-repeatable read

Inconsistent read

Inconsistent read in exchange for lost changes

Repeatable Read

The absence of non-repeatable and phantom reads

Serialization error in exchange for lost changes

Inconsistent write (write skew)

Read-only transaction anomaly

Serializable

Inconsistent write (write skew)

Read-only transaction anomaly

该使用什么样的隔离级别？

小结