PostgreSQL的MVCC(1)--隔离

什么是隔离以及隔离为何重要

 可能每个人至少都知道事务的存在，遇到过ACID，并且听说过隔离级别。但是我们仍可能认为这与理论有关，这在实践中是不必要的。因此，我将花一些时间来解释为什么这确实很重要。

如果应用程序从数据库中获取了不正确的数据，或者应用程序将错误的数据写入了数据库，你肯定会不高兴。

但是什么是“正确的”数据？众所周知，可以在数据库级别创建完整性约束，例如NOT NULL或UNIQUE。如果数据始终满足完整性约束，则它们是完整的。

正确和完整同一件事吗？不完全是。并非所有约束都可以在数据库级别上指定。有些约束过于复杂，例如，一次覆盖多个表。即使通常可以在数据库中定义约束，但由于某种原因，它也没有定义，但这并不意味着可以违反该约束。

因此，正确性比完整性要强，但是我们并不确切知道这意味着什么。我们只不过承认正确性的“黄金标准”是应用程序，正如我们希望的那样，它是正确编写的，绝不会出错。 在任何情况下，如果应用程序没有违反完整性，但是违反了正确性，DBMS将不会知道它，也不会“当场”捕获应用程序

此后，我们将使用术语一致性来指代正确性。

让我们假设应用程序仅执行正确的运算符序列。如果应用程序正确无误，DBMS的作用是什么？

首先，事实证明，正确的运算符序列可以暂时破坏数据一致性，这有点奇怪，但是也很正常。一个朴实却清晰的例子是将资金从一个帐户转移到另一个帐户。一致性规则听起来像这样：转账永远不会改变帐户上的总金额（此规则在SQL中很难指定为完整性约束，因此它存在于应用程序级别，并且对于DBMS是不可见的）。转帐包括两个操作：第一个操作减少一个帐户上的资金，第二个操作增加另一个帐户上的资金。第一个操作破坏数据一致性，而第二个操作恢复数据一致性。

如果执行第一个操作而第二个不执行该怎么办？在第二次操作过程中，可能会发生电力故障，服务器崩溃，被零除的情况-无论如何，很明显，一致性将被破坏，并且这是不允许的。通常，可以在应用程序级别解决此类问题，但需要付出大量努力；但是，幸运的是，这不是必需的：这是由DBMS完成的。但是，为此，DBMS必须知道这两个操作是不可分割的整体。即一个事务。

事实证明，这很有趣：因为DBMS知道操作是由事务组成的，所以它通过确保事务是原子的来帮助保持一致性，而这样做却不了解特定的一致性规则。

但是还有第二点，更微妙的一点。一旦几个同时发生的事务（分别绝对正确）出现在系统中，它们可能无法一起正常工作。这是因为操作顺序混了：您不能假定一个事务的所有操作都先执行，然后又执行另一个事务的所有操作。

关于同时性的说明。确实，事务可以在具有多核处理器，磁盘阵列等的系统上同时运行。但是，在分时共享模式下，按顺序执行命令的服务器也可以使用相同的推理：在某些时钟周期内执行一个事务，在接下来的某些周期中，另一个事务执行。有时，术语“并发执行”被用来概括。

正确的事务无法一起正常工作的情况称为并发执行异常。

举一个简单的例子：如果一个应用程序想要从数据库中获取正确的数据，它至少不能看到其他未提交事务的变化。否则，您不仅可以获取不一致的数据，还可以查看数据库中从未存在过的内容（如果取消了事务）。这种异常称为脏读。

还有其他更复杂的异常，我们将在稍后处理。

当然，避免并发执行是不可能的：否则，还有哪种性能可以谈呢？但是你也不能使用错误的数据。

DBMS再次解决了问题。您可以使事务像顺序执行一样，好像一个接一个地执行。换句话说-彼此隔离。实际上，DBMS可以混合执行各种操作，但要确保并发执行的结果与某些可能的顺序执行的结果相同。这样可以消除任何可能的异常情况。

因此，我们得出了以下定义：

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

此定义将首字母缩写ACID的前三个字母组合在一起。它们之间的联系如此紧密，以至于缺乏其中任意一个去考虑是没有意义的。实际上，也难以分离字母D（耐久性）。确实，当系统崩溃时，它仍具有未提交事务的更改，你需要使用这些更改来恢复数据一致性。

一切都会好起来的，但是实现完全隔离是一项技术难题，需要降低系统吞吐量。因此，实际上，经常（不是总是，但几乎总是）使用弱隔离，这可以防止某些但不是全部异常。这意味着确保数据正确性的一部分工作落在应用程序上。因此，了解系统中使用的隔离级别，提供哪些保障，不提供什么以及在这种情况下如何编写正确的代码非常重要。

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

SQL标准很早就描述了四个隔离级别。 通过列出在此级别同时执行事务时允许或不允许的异常来定义这些级别。 因此，要谈论这些级别，有必要了解异常。

我强调，在这一部分中，我们谈论的是标准，即关于一种理论的理论，实践是基于该理论基础的，但与此同时，实践却大相径庭。 因此，这里的所有示例都是推测性的。 他们将在客户帐户上使用相同的操作：这是相当有示范性的，尽管这样，这与现实中银行操作的组织方式无关。

更新丢失

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

例如，两个事务将使同一帐户上的金额增加100₽（₽是俄罗斯卢布的货币符号）。 第一个事务读取当前值（₽1000），然后第二个事务读取相同的值。 第一个事务增加金额（amount1100）并写入该值。 第二个事务的行为方式相同：它获得₽1100并写入此值。 结果，客户损失了100₽。

SQL标准不允许在任何隔离级别丢失更新。

脏读和未提交读

脏读是我们已经熟悉的内容。 当一个事务读取另一个事务尚未提交的更改时，就会发生此异常。

例如，第一笔交易将所有资金从客户的帐户转移到另一个帐户，但不提交更改。 另一笔交易读取了帐户余额，得到₽0，并拒绝向客户提取现金，尽管第一笔交易中止并还原了其更改，因此数据库中从来没有0的值。

SQL标准允许在“未提交读”级别进行脏读取。

不可重复读和已提交读

当一个事务两次读取同一行，并且在两次读取之间，第二个事务修改（或删除）该行并提交更改时，将发生不可重复的读取异常。 然后，第一个事务将获得不同的结果。

例如，让一致性规则禁止客户帐户上出现负数。 第一个事务将使帐户中的金额减少100₽。 它检查当前值，得到₽1000，并确定可以减小。 同时，第二个事务将帐户上的金额减少为零并提交更改。 如果现在第一笔交易重新核对了金额，它将得到₽0（但是它已经决定减少该金额，并且帐户“goes into the red”）。

SQL标准允许不可重复的读取在“已提交读”和“未提交读”级别。 但是“已提交读”不允许脏读。

幻读和可重复读

当事务两次按相同条件读取一组行时，就会发生幻像读取，并且在两次读取之间，第二个事务会添加满足该条件的行（并提交更改）。 然后，第一个事务将获得不同的行集。

例如，让一致性规则阻止客户拥有三个以上的帐户。 第一个事务将开设一个新帐户，检查当前帐户数（例如2），并确定可以开设。 同时，第二个事务还将为客户开设一个新帐户并提交更改。 现在，如果第一个事务重新检查了该数字，它将得到3（但它已经在开设另一个帐户，并且客户似乎拥有4个）。

SQL标准允许幻读处于“未提交读”，“已提交读”和“可重复读”级别。 但是，在“可重复读”级别上不允许进行不可重复读。

无异常和可串行化

该标准定义了另一个级别-Serializable-不允许任何异常。 这与禁止丢失更新以及脏的，不可重复的或幻像的读取不同。

事实是，已知异常比标准中列出的要多得多，并且未知数量也未知。

可串行化级别绝对防止所有异常。这意味着在此级别上，应用程序开发人员无需考虑并发执行。 如果事务执行正确的操作符序列（分别工作），则在执行这些事务时数据也将保持一致

总结

现在我们可以提供一个众所周知的表。但是为了清晰起见，这里增加了标准中缺少的最后一列。

	Lost changes	Dirty read	Non-repeatable read	Phantom read	Other anomalies
Read Uncommitted	—	Yes	Yes	Yes	Yes
Read Committed	—	—	Yes	Yes	Yes
Repeatable Read	—	—	—	Yes	Yes
Serializable	—	—	—	—	—

为什么会出现这些异常呢?

为什么标准仅列出许多可能的异常中的几个，为什么它们又是列出的这些异常？

似乎没有人知道这一点。但是这里的做法显然比理论要先进，因此在那时（SQL：92标准）可能还没有想到其他异常。

另外，隔离必须建立在锁上。广泛使用的两阶段锁定协议（2PL）背后的思想是，在执行过程中，事务将锁定正在使用的行，并在完成时释放锁定。相当简化，一个事务获取的锁越多，它与其他事务的隔离性就越好。但是，系统的性能也会受到更大的影响，因为事务不是一起工作，而是开始排队等待相同的行。

我的感觉是，这只是所需的锁数说明了标准的隔离级别之间的差异。

如果事务锁定了要更新的行而不是读取的行，我们将获得“未提交读”级别：不允许出现更新丢失，但可以读取未提交的数据。

如果事务锁定了要修改的行，使其无法读取和更新，则将获得“已提交”级别：您无法读取未提交的数据，但是当您再次访问该行时，您可以获得一个不同的值（不可重复读）。

如果事务锁定了要读取和修改的行以及读取和更新的行，我们将获得“可重复读”级别：重新读取该行将返回相同的值。

但是Serializable有一个问题：不能锁定不存在的行。因此，幻象读取仍然是可能的：另一个事务可以添加（但不能删除）满足先前执行的查询条件的行，并且该行将包含在重新选择中。

因此，要实现可串行化级别，普通锁不能满足要求-需要锁定条件（谓词）而不是行。因此，此类锁称为谓词。它们是在1976年提出的，但是它们的实际适用性受到相当简单的条件的限制，对于这些条件，很明显如何将两个不同的谓词结合在一起。据我所知，到目前为止，此类锁从未在任何系统中实现。

PostgreSQL中的隔离级别

随着时间的流逝，基于锁的事务管理协议已被快照隔离协议（SI）取代。其想法是，每个事务在某个时间点都使用数据的一致快照，并且只有那些在快照创建之前提交的才会进入快照。

这种隔离会自动防止脏读。你可以在PostgreSQL中指定Read Uncommitted级别，但是它的工作方式与Read Committed完全相同。因此，进一步，我们将不再谈论“未提交读”级别。

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

通过使用数据快照，PostgreSQL中的隔离比标准要求的严格：“可重复读”级别不仅不允许不可重复的读取，而且也不允许幻读（尽管它不提供完全隔离）。并且这在不损失效率的情况下实现。

	Lost changes	Dirty read	Non-repeatable read	Phantom read	Other anomalies
Read Uncommitted	—	—	Yes	Yes	Yes
Read Committed	—	—	Yes	Yes	Yes
Repeatable Read	—	—	—	—	Yes
Serializable	—	—	—	—	—

在下一篇文章中，我们将讨论如何在多版本并发是如何实现的，现在，我们将以用户的眼光详细地研究这三个级别中的每个级别（如您所知，最有趣的是隐藏在“其他异常背后”）。为此，我们创建一个帐户表。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隔离级别:

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

在第二个会话中，我们将启动具有相同已提交级别的另一个事务。为了区分事务，第二个事务的命令将被缩进并用竖条标记。

为了重复上面的命令(这很有用)，您需要打开两个终端并在每个终端上运行psql。在第一个终端中，您可以输入一个事务的命令，在第二个终端中，您可以输入另一个事务的命令。

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

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

不可重复读

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

=> 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语句
　　出现一致性问题是因为在操作之间的时间间隔内，另一个事务可以完成，这将更改可见数据。而且，如果只有一个操作，则没有时间间隔。PostgreSQL有足够的技术可以用一条SQL语句解决复杂的问题。让我们注意一下通用表表达式（CTE），在其余表中，您可以使用INSERT / UPDATE / DELETE语句，以及INSERT ON CONFLICT语句，该语句实现了在一个语句中“插入，但如果该行已经存在，则更新“ 。
·自定义锁
　　最后的方法是在所有必需的行（SELECT FOR UPDATE）上甚至在整个表（LOCK TABLE）上手动设置排他锁。这始终有效，但是多版本并发的好处将无法使用：某些操作将顺序执行，而不是并发执行。

非一致性读

在进入下一个隔离级别之前，你必须承认这并不像听起来那么简单。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,也就是说,不正确的数据。这是一种非一致性读异常。

如何避免这种异常，同时保持已提交读隔离级别?当然，使用一个操作。例如:

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允许你定义函数，而函数具有*volatility category*的概念。如果在查询中调用了一个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函数中才可能出现这种效果。问题是，在默认情况下，使用的正是这个隔离级别和这个volatility category 。不要掉入陷阱!

Inconsistent read in exchange for lost changes

在更新期间，我们还可以在单个操作中获得不一致的读取，尽管是以某种出乎意料的方式。

让我们看看当两个事务尝试修改同一行时会发生什么。现在鲍勃₽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，那么他的帐户根本就不会出现在选择中。

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

细心的读者注意到，在应用程序的帮助下，即使在已提交读的级别上，也可能会丢失更新。例如:

x := (SELECT amount FROM accounts WHERE id = 1);
UPDATE accounts SET amount = x + 100 WHERE id = 1;

这不是数据库的错:它获得两条SQL语句，却不知道x + 100的值与账户余额有某种关系。要避免用这种方式编写代码。

这不是数据库的错:它获得两条SQL语句，却不知道x + 100的值与账户余额有某种关系。避免用这种方式编写代码。

可重复读

不可重复读和幻读的消失

隔离级别的名称本身就假定读是可重复的。让我们检查一下，同时确保没有幻读。为此，在第一个事务中，我们将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命令中指定可重复读级别来启动事务(第一个事务的级别并不重要)。

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

我们前面已经讨论过，当两个事务在已提交读隔离级别上更新同一行时，可能会出现读取不一致的异常。这是因为等待的事务重新读取锁定的行，因此不会将其视为与其他行相同的时间点。

在可重复读隔离级别上，不允许出现这种异常，但如果出现这种异常，则什么也不能做——因此事务以串行化错误终止。让我们通过重复相同的情形来检验它的利息应计:

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

对于一行的任何其他竞争性更改，即使我们关心的列实际上没有更改，也会发生同样的错误。

实用结论:如果应用程序对写事务使用可重复读隔离级别，那么它必须准备好重复以串行化错误终止的事务。对于只读事务，此结果是不可能的。

实用结论:如果应用程序对写事务使用可重复读隔离级别，那么它必须准备好重复以串行化错误终止的事务。对于只读事务，此结果是不可能的。

Inconsistent write (write skew)

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

第一个异常是不一致的写入。

保持以下一致性规则:如果客户所有账户上的总金额保持非负数，则允许客户账户上的负数金额。

第一个事务得到Bob账户上的金额：

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

如果第一个事务被提交在这个时间点上,没有异常发生:我们可以假设第一个事务首先被执行,然后第二个。

但是想象一下，此时第三个(只读)事务开始了，它读取不受前两个事务影响的某个帐户的状态:

|  => 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构建在快照隔离的基础上。那些不发生在可重复读(如脏读、不可重复读或幻读)中的异常也不会发生在可串行化级别。检测到出现的异常(写入不一致和只读事务异常)，事务就会中止——出现常见的串行化错误:无法串行化访问。

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.

就像在“可重复读取”级别上一样，使用“可串行化”隔离级别的应用程序必须重复以串行化错误终止的事务，因为错误消息会提示我们。

我们可以简化编程，但是这样做的代价是强制终止部分交易，并且需要重复执行。当然，问题是这个部分有多大。如果仅那些终止的事务与其他事务矛盾地地重叠，那就太好了。但是，由于你必须跟踪每一行的操作，因此这种实现不可避免地会占用大量资源并且效率低下。

实际上，PostgreSQL的实现允许错误的否定：一些显然是常规事务也会“不幸”的被中止。稍后我们将看到，这取决于许多因素，例如适当索引的可用性或可用的RAM数量。此外，还有其他一些（相当严格的）实现限制，例如，“可串行化”级别的查询将不适用于复制，并且它们将不使用并行执行计划。尽管改进实施的工作仍在继续，但是现有的限制使这种隔离级别的吸引力降低了。
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;

第三个事务被显式声明为只读和可延迟:

|   => 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隔离，应用程序中的所有事务都必须使用此级别。不能将已提交读(或可重复读)事务与可串行化事务混合使用。也就是说，可以混合使用，但是Serializable的行为就像Repeatable Read一样，不会出现任何警告。我们将在稍后讨论实现时讨论为什么会发生这种情况。

所以，如果你决定使用Serializble，最好是全局设置默认级别:

ALTER SYSTEM SET default_transaction_isolation = 'serializable';

如何选择隔离级别

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

该级别的另一面是大量可能的异常，上面已经详细讨论过。软件工程师必须始终牢记它们并编写代码，以免它们出现。如果无法在单个SQL语句中编写必要的操作，则必须诉诸显式锁。最麻烦的是，代码很难测试与获取不一致的数据相关的错误，并且错误本身可能以不可预测和不可重现的方式发生，因此难以修复。

可重复读取隔离级别消除了一些不一致的问题，但可惜的是，并不是全部。因此，您不仅必须记住剩余的异常情况，还必须修改应用程序以使其正确处理串行化错误。当然不方便。但是对于只读事务，此级别完美地补充了“已提交读”操作，并且非常方便，例如，用于构建使用多个SQL查询的报表。

最后，串行化级别使你完全不必担心不一致，这大大简化了编码。应用程序唯一需要的是在发生串行化错误时能够重复任何事务。但是中止事务的比例，额外的开销以及无法并行化查询会显着降低系统吞吐量。还要注意，可串行化级别不适用于复制，并且不能与其他隔离级别混合。

原文地址：https://habr.com/en/company/postgrespro/blog/467437/