PostgreSQL的MVCC — 1.隔离级别

本文来自https://mp.weixin.qq.com/s/1VJ-a3BN4zcC4XECLXDvng

1. 什么是隔离级别，为什么很重要？

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

如果应用程序从数据库中获取不正确的数据，或者应用程序向数据库中写入不正确的数据，您可能不会高兴。

但什么是“正确的”数据？众所周知，可以在数据库级别创建完整性约束，例如NOT NULL或UNIQUE。如果数据总是满足完整性约束（这是因为DBMS保证了它），那么它们是完整的。

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

所以，正确性比完整性强，但我们不知道这到底意味着什么。我们只能承认，正确性的“黄金标准”是一个应用程序如我们所希望的那样，它写得“正确”，而且从未出错。在任何情况下，如果应用程序没有违反完整性，但违反了正确性，DBMS将不会知道，也不会当场捕获应用程序。

此外，我们将使用术语“一致性”来表示正确性。

让我们假设应用程序只执行正确的操作顺序。如果应用程序是正确的，DBMS的作用是什么？

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

如果执行了第一个操作而未执行第二个操作，该怎么办？事实上，不费吹灰之力：在第二次操作期间，可能会发生电力故障，服务器崩溃，被零除-无论什么。很明显，这种一致性将被打破，这是不允许的。一般来说，可以在应用程序级别解决此类问题，但需要付出巨大的努力；然而幸运的是，这不是必需的：这是由DBMS完成的。但要做到这一点，DBMS必须知道这两个操作是不可分割的整体。也就是一个事务。

结果很有趣：因为DBMS知道操作构成了一个事务，所以它通过确保事务是原子的来帮助保持一致性，并且它在不知道任何特定一致性规则的情况下做到了这一点。

但还有第二个更微妙的问题。一旦系统中同时出现多个单独绝对正确的事务，它们可能无法一起正常工作。这是因为操作顺序混淆了：您不能假设先执行一个事务的所有操作，然后再执行另一个事务的所有操作。

关于并发的解释。事实上，事务可以在具有多核处理器、磁盘阵列等的系统上同时运行。但对于以分时模式顺序执行命令的服务器，同样的推理也适用：在某些时钟周期内，执行一个事务，在下一个特定周期内，执行另一个事务。

正确的事务不正确地协同工作的情况称为并发执行的“异常”。

举个简单的例子：如果应用程序希望从数据库中获取正确的数据，那么它至少不能看到其他未提交事务的更改。否则，您不仅会获得不一致的数据，还会看到数据库中从未出现过的数据（如果事务被取消）。这种异常称为“脏读”。

还有其他更复杂的异常情况，我们稍后会说。

当然，避免并发执行是不可能的：否则，如何谈性能？但您不能使用不正确的数据。

DBMS再次出手相救。您可以使事务按顺序执行，一个接一个地执行。换言之，相互隔离。实际上，DBMS可以执行混合的操作，但要确保并发执行的结果与某些可能的顺序执行的结果相同。这样消除了任何可能的异常。

所以我们得出了定义：

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

该定义将ACID的前三个字母统一起来。它们彼此之间的关系如此密切，以至于没有其中任何一个都是没有意义的。事实上，字母D（耐久性）也很难分离。当系统崩溃时，它仍然会更改未提交的事务，您需要采取一些措施来恢复数据一致性。

实现完全隔离在技术上是一项困难的任务，需要降低系统的吞吐量。因此，在实践中，经常（几乎总是）使用弱化隔离，这可以防止某些异常，但并非所有异常。这意味着确保数据正确性的一部分工作落在应用程序上。正是出于这个原因，了解系统中使用了哪一级别的隔离，它提供了什么保证，不提供什么保证，以及如何在这种情况下编写正确的代码，这一点非常重要。

 

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

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

我强调，在这一部分中，我们讨论的是标准，也就是一种理论，实践在很大程度上是以这种理论为基础的，但同时又与之有很大的分歧。因此，这里所有的例子都是推测性的。他们将在客户账户上使用相同的操作：这相当具有示范性，尽管不可否认，这与银行业务在现实中的组织方式无关。

2.1 丢失更新

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

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

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

 

2.2 脏读和读未提交

“脏读”是我们已经熟悉的东西。当一个事务读取另一个事务尚未提交的更改时，就会发生这种异常。

例如，第一个事务将所有资金从客户的帐户转移到另一个帐户，但不提交更改。另一个事务读取帐户余额，得到₽0，并拒绝向客户提取现金，尽管第一个事务中止并恢复其更改，因此数据库中从未存在0的值。

该标准允许在读未提交级别进行脏读。

 

2.3 不可重复读取和读已提交

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

例如，让一致性规则禁止客户帐户上有负金额。第一个事务将减少账户金额₽100；它检查当前值为₽1000，并决定有可能减少。同时，第一个事务将账户金额减至零，并提交更改。如果第一笔交易现在重新检查了金额，它将得到₽0（但它已经决定减少该值，并且该帐户“出现赤字”）。

该标准允许在读未提交和读已提交级别进行不可重复读。但读已提交不允许脏读。

 

2.4 幻读和可重复读

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

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

该标准允许在读未提交、读已提交和可重复读级别进行幻读。但是，不允许在可重复读级别进行不可重复读。

 

2.5 无异常和可串行化

该标准还定义了一个级别-可串行化-不允许任何异常。这与禁止丢失更新和脏的、不可重复的或幻读不同。

问题是，已知的异常比标准中列出的要多得多，而且还有未知数量的异常。

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

 

2.6 总结表

 

 

 

2.7 为什么会出现这些异常？

为什么标准只列出了许多可能异常中的一小部分，为什么它们正是这些？

似乎没有人确切知道这一点。但在这个问题上，实践显然领先于理论，因此在当时（SQL:92标准）可能没有想到其他异常。

此外，假设隔离必须建立在锁上。广泛使用的两阶段锁协议（2PL）背后的思想是，在执行期间，事务锁定它正在处理的行，并在完成时释放锁。非常简单，事务获得的锁越多，它就越能与其他事务隔离。但系统的性能也受到了更大的影响，因为事务开始排队等待相同的行，而不是一起工作。

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

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

如果事务在读取和更新时都锁定了待修改的行，那么我们将获得读已提交级别：您不能读取未提交的数据，但当您再次访问该行时，会获得不同的值（不可重复读）。

如果事务同时锁定了待读取和修改的行以及正在读取和更新的行，那么我们得到了可重复的读取级别：重新读取该行将返回相同的值。

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

因此，要实现可串行化级别，普通锁是不够的-您需要锁定条件（谓词）而不是行。因此，这种锁被称为谓词锁。它们是1976年提出的，但它们的实际适用性受到相当简单的条件限制：如何连接两个不同的谓词。据我所知，迄今为止，这种锁从未在任何系统中实现过。

 

3. PostgreSQL中的隔离级别

随着时间的推移，基于锁的事务管理协议被快照隔离协议（SI）所取代。它的思想是，每个事务在某个时间点与数据的一致快照一起工作，只有那些更改才会进入快照，并在提交之前创建。

这种隔离自动防止脏读。形式上，您可以在PostgreSQL中指定读未提交级别，但其工作方式与读已提交完全相同。因此，进一步来说，我们将不讨论读未提交级别。

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

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

 

 

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

3.1 读已提交

3.1.1 不存在脏读

确保无法读取脏数据很容易。我们开始事务。默认情况下，它将使用读已提交隔离级别：

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

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

 

3.1.2 不可重复读取

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

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

查询已经获取了新数据，这是“不可重复读”异常，在读提交级别允许。

实际结论：在事务中，您无法根据前一个操作读取的数据做出决策，因为在执行操作之间可能会发生变化。下面是一个示例，其变化在应用程序代码中频繁出现，因此被视为经典的反模式：

     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;

如果通过重新排列操作可以破坏一切，那么代码编写就是不正确的。

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

• 不编写代码。

  这不是玩笑。例如，在这种情况下，CHECK很容易变成完整性约束：

  ALTER TABLE accounts ADD CHECK amount>=0

  现在不需要检查：只需执行操作，如有必要，处理尝试违反完整性时将发生的异常。

• 使用单个SQL语句。

  由于在操作间隔可以完成另一个事务，这将改变可见数据，因此会出现一致性问题。如果只有一个操作，那么就没有间隔。

  PostgreSQL有足够的技术可以用一条SQL语句解决复杂的问题。让我们注意一下公共表表达式（CTE），在其中您可以使用INSERT/UPDATE/DELETE语句，以及INSERT ON CONFLICT语句，它在一条语句中实现了“insert，但如果行已经存在，则update”的逻辑。

• 自定义锁。

  最后一种方法是在所有必要的行（SELECT FOR UPDATE）甚至整个表（LOCK TABLE）上手动设置独占锁。这总是可行的，但却抵消了多版本并发的好处：一些操作将按顺序执行，而不是并发执行。

 

3.1.3 读不一致

在进入下一个隔离级别之前，你必须承认这并不像听起来那么简单。PostgreSQL的实现允许标准未规定的其他鲜为人知的异常。

假设第一个事务开始从一个Bob账户向另一个Bob账户转账：

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

同时，另一个事务统计Bob的余额，并在Bob的所有帐户上循环执行计算。事务从第一个帐户（2）开始（显然，可以看到之前的状态）：

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

此时，第一个事务成功提交：

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

另一个事务读取第二个帐户（3）的状态（并且已经看到新值）：

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

我要强调的是，只有在读已提交隔离级别和VOLATILE函数中才可能出现这种效果。问题是，在默认情况下，使用的正是这个隔离级别和这个volatility category 。不要掉入陷阱!

 

3.1.4 不一致的读取以交换丢失更新

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

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

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

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

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

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

3.2 可重复读

3.2.1 不可重复读和幻读的消失

隔离级别的名称本身就假定读是可重复的。让我们检查一下，同时确保没有幻读。为此，在第一个事务中，我们将Bob的帐户恢复到以前的状=> BEGIN;

 

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

第二个事务仍然看到与开始时完全相同的数据:已存在的数据没有变化，新加入的行也不可见。

在这个级别上，你可以避免担心两个操作之间可能发生的变化。

3.2.2 替换丢失的更新时出现串行化错误

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

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

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

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

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

 

3.2.3 写入不一致（写入倾斜）

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

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

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

第一个事务得到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的余额出现赤字，尽管每个事务单独执行是正常的。

 

3.2.4 只读事务异常

这是在可重复读级别上可能出现的第二个也是最后一个异常。为了演示它，将需要三个事务，其中两个将更改数据，第三个将只读取数据。

但首先让我们恢复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;

3.3 串行化

可串行化级别防止了所有可能的异常。实际上，可串行化构建在快照隔离的基础上。那些不发生在可重复读(如脏读、不可重复读或幻读)中的异常也不会发生在可串行化级别。检测到出现的异常(写入不一致和只读事务异常)，事务就会中止——出现常见的串行化错误: could not serialize access。

3.3.1 写入不一致（写入倾斜）

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

=> 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数量。此外，还有其他一些（相当严格的）实现限制，例如，“可串行化”级别的查询将不适用于复制，并且它们将不使用并行执行计划。尽管改进实施的工作仍在继续，但是现有的限制使这种隔离级别的吸引力降低了。

并行计划最早将出现在PostgreSQL 12（补丁）中。并且可以在PostgreSQL 13（另一个补丁）中开始查询副本。

 

3.3.2 只读事务异常

为了使只读事务不会导致异常，也不会因此而受到影响，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;

另一个重要提示:如果使用可串行化隔离，应用程序中的所有事务都必须使用此级别。不能将已提交读(或可重复读)事务与可串行化事务混合使用。也就是说，可以混合使用，但是可串行化的行为就像可重复读一样，不会出现任何警告。我们将在稍后讨论实现时讨论为什么会发生这种情况。

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

ALTER SYSTEM SET default_transaction_isolation = 'serializable';

 

4. 如何选择隔离级别

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

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

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

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