跳转到主要内容

2 隔离

PostgreSQL 18 · 基于俄文 PG18 原版的简体中文译本

简体中文 · PG14English · PG14简体中文 · PG18Русский · PG18

2.1 一致性

关系型数据库管理系统的重要特征之一，是保证数据的一致性（consistency），也就是数据的正确性。

众所周知，在数据库层面可以创建完整性约束（integrity constraints），例如 NOT NULL 或 UNIQUE。数据库系统确保这些约束永远不会被破坏，从而保证数据的完整性。

如果所有需要的约束都能在数据库层面上制定，那么一致性就能得到保证。但有些条件过于复杂，无法通过数据库层面的约束来实现，例如，它们可能一次涉及多个表。而且即使某个约束可以在数据库中定义，但由于某些原因没有被定义，也并不意味着这个约束条件可以被违反。

因此，数据的一致性比完整性更严格，但是数据库系统并不清楚“一致性”具体意味着什么。如果应用程序在不破坏完整性的情况下破坏了一致性，那么数据库系统是没有办法发现的。因此，一致性实际上由应用程序担保，我们只能相信它编写得正确，且永远不会出错。

但是，如果应用程序始终只执行正确的语句序列，那么数据库系统的作用何在呢？

首先，正确的语句序列可以暂时破坏数据一致性，并且 — 虽然这听起来可能很奇怪 — 这是完全正常的。

一个老套但浅显易懂的例子是将资金从一个账户转移到另一个账户。一致性规则可能是这样的：资金转移绝不能改变受影响账户的总余额。将此规则表述为 SQL 中的完整性约束是相当困难的 (尽管可能)，因此让我们假设它是在应用程序层面定义的，并且对数据库系统保持不透明。转账包括两个操作：第一个操作从其中一个账户中提取一些钱，而第二个操作将这笔钱添加到另一个账户。第一个操作破坏了数据的一致性，但第二个操作恢复了一致性。

如果第一个操作成功了，但第二个操作失败了 (因为一些故障)，数据一致性将被打破。这种情况是无法接受的，但是在应用程序层面检测和解决它们需要付出很大的努力。幸运的是，这并不是必要的 — 如果数据库系统知道这两个操作构成一个不可分割的整体，即一个事务，那么这个问题完全可以由数据库系统本身解决。

但这里还有一个更微妙的方面。事务本身是绝对正确的，但当并行运行时，事务可能会开始不正确地运行。这是因为属于不同事务的操作经常会被混合在一起。如果数据库系统先完成一个事务的所有操作，然后再进行下一个事务，就不会出现此类问题，但顺序执行的性能会低得令人难以置信。

说明

真正同时执行事务，只能在硬件允许的系统中实现，例如使用多核处理器、磁盘阵列等。但同样的推理也适用于以分时方式顺序执行命令的服务器。为便于概括，这两种情况有时统称为并发执行。

原本正确的事务在并发执行时出现不正确行为，这种情况称为并发执行异常。

此处是一个简单的例子。为了从数据库中获取一致的数据，应用程序至少不能看到其他未提交事务所做的任何更改。否则 (如果某些事务回滚)，它将看到从未存在过的数据库状态。这种异常称为脏读，还有很多其他更为复杂的异常。

当并发运行事务时，数据库必须保证这种执行的结果与某种可能的顺序执行结果相同。换句话说，它必须将事务彼此隔离，从而处理任何可能的异常。

总而言之，事务是一组将数据库从一个正确状态转变到另一个正确状态 (一致性) 的操作，前提是它完全执行 (原子性) 并且不受其他事务的影响 (隔离性)。这个定义涵盖了 ACID 缩写中前三个字母所代表的要求：原子性（Atomicity）、一致性（Consistency）和隔离性（Isolation）。它们紧密相关，分别讨论并没有意义。事实上，持久性（Durability）要求也很难与它们分开：在系统崩溃之后，系统中可能仍然包含一些由未提交事务所做的更改，你必须采取措施来恢复数据的一致性。 （第 211 页）

因此，即使数据库系统对隐含的一致性规则一无所知，它也通过考虑事务边界来帮助应用程序维护数据一致性。

不幸的是，完全隔离很难实现，并且会对性能产生负面影响。大多数实际系统使用较弱的隔离级别，这可以防止一些异常，但并非全部。这意味着维护数据一致性的部分工作落在了应用程序上。因此，必须了解系统使用的隔离级别提供哪些保证、不提供哪些保证，以及如何在这些条件下编写正确的代码。

2.2 SQL 标准中的隔离级别与异常

SQL 标准定义了四种隔离级别 1。这些级别由并发事务执行期间可能发生或不发生的异常所定义。因此，在谈论隔离级别时，我们需要从异常开始讲起。

需要记住的是，标准是一种理论概念：它影响了实践，但实践在许多方面与之不同。这就是为什么此处所有的例子都是假设性的。虽然这些例子处理的是银行账户上的事务，它们非常直观，但我必须承认，它们与实际的银行操作无关。

有趣的是，实际的数据库理论也与标准有所偏离：它是在标准被采纳之后发展起来的，而实践已经领先很多。 2

更新丢失

当两个事务读取同一行表数据，然后其中一个事务更新该行，最后另一个事务在不考虑第一个事务所做的任何更改的情况下更新同一行时，就会发生更新丢失（lost update）异常。

假设有两个事务打算使同一账户的余额增加 100 ₽。第一个事务读取了当前值 (1000 ₽)，然后第二个事务读取了相同的值。第一个事务增加了余额 (使余额变为 1100 ₽)，并将新值写入数据库中。第二个事务也是如此：在增加余额后获得 1100 ₽，并写入该值。结果，客户损失了 100 ₽。

在所有隔离级别下，SQL 标准都禁止更新丢失。

脏读与 Read Uncommitted

当一个事务读取了另一个事务所做的未提交的更改时，就会发生脏读（dirty read）异常。

例如，第一个事务将 100 ₽ 转移到一个空账户，但没有提交此更改。另一个事务读取了账户的状态 (已被更新但还未提交)，并允许客户提取这笔钱 — 即使第一个事务中断并且其更改被回滚，所以账户其实是空的。

SQL 标准允许在读未提交隔离级别下发生脏读。

不可重复读与 Read Committed

当一个事务两次读取同一行数据，而在这两次读取之间，另一个事务更新 (或删除) 了这一行并提交了更改时，就会发生不可重复读（non-repeatable read）异常。最终，第一个事务得到了不同的结果。

例如，假设有一条一致性规则，禁止银行账户中出现负余额。第一个事务准备使账户余额减少 100 ₽。它检查当前值，得到 1000 ₽，因此认定此操作是可行的。同时，另一个事务从该账户中取出了所有钱并提交更改。如果第一个事务在此时再次检查余额，它会得到 0 ₽ (但取钱的决定已经做出，那么这个操作会导致透支)。

SQL 标准允许在读未提交和读已提交隔离级别下发生不可重复读。

幻读与 Repeatable Read

当同一个事务执行两次相同的查询，返回一组满足特定条件的行，而在这两次查询期间，另一个事务添加了一些满足此条件的其他行，并提交了此更改，就会出现幻读（phantom read）异常。最终，第一个事务获得了两组不同的行。

例如，假设有一个一致性规则，禁止客户拥有三个以上的账户。第一个事务打算开一个新账户，因此它检查当前有多少个账户可用 (假设有两个)，并认定此操作是可行的。此时，第二个事务也为该客户开了一个新账户并提交了更改。如果第一个事务再次检查打开的账户数量，它会得到三个 (但它已经在开另一个账户，客户最终拥有了四个账户)。

SQL 标准允许在读未提交、读已提交和可重复读隔离级别下发生幻读。

无异常与 Serializable

SQL 标准还定义了可串行化隔离级别，该级别不允许任何异常。这与禁止更新丢失、脏读、不可重复读和幻读不同。事实上，已知异常的数量远远超过标准定义的数量，还有未知数量的未知异常。

可串行化隔离级别必须防止任何异常。这意味着应用程序开发人员不必考虑隔离问题。如果事务在单独运行时执行正确的语句序列，那么并发执行也不能破坏数据的一致性。

为了阐述这个概念，我将使用标准中提供的一个众所周知的表格；为清晰起见，此处还添加了最后一列：

隔离级别更新丢失脏读不可重复读幻读其他异常
Read Uncommitted—是是是是
Read Committed——是是是
Repeatable Read———是是
Serializable—————
表 2.1 SQL 标准中的隔离级别与异常

为什么是这些异常？

为什么标准只提到了某些可能的异常，而且为什么是这些异常呢？

似乎没有人确切知道。但也可能是在制定最初版本的标准时，其他异常根本未被考虑，因为那时理论远远落后于实践。

此外，当时的假设是隔离必须基于锁。广泛使用的两阶段锁定协议 (2PL) 要求事务在执行期间锁定受影响的行，并在完成时释放锁。简单来说，事务获取的锁越多，它与其他事务的隔离程度就越好。相应地，系统的性能就越差，因为事务开始排队访问同一行，而不是并发运行。

我相信，在很大程度上，标准隔离级别之间的区别是由实现它们所需的锁数量来定义的。

如果要更新的行对写入锁定但对读取不锁定，我们便会得到读未提交隔离级别，允许在提交前读取数据。

如果要更新的行对读取和写入都锁定，我们便会得到读已提交隔离级别：禁止读取未提交的数据，但如果查询多次运行，可能返回不同的值 (不可重复读)。

在所有操作中锁定要读取和更新的行，我们便会得到可重复读隔离级别：重复读取同一行会得到相同的值。

然而，可串行化隔离级别带来了一个问题：无法锁定尚不存在的行。这为幻读的发生留下了可能性：一个事务可以添加满足先前查询条件的行，并且这一行将出现在下一个查询结果中。

因此，常规的锁无法提供完全隔离：要实现这一点，我们必须锁定条件 (谓词) 而不是行。谓词锁早在 1976 年 System R 开发时就被引入了；然而，它们的实际适用范围有限，只适用于那些很容易判断两个不同谓词是否可能冲突的简单条件。据我所知，在任何系统中从未实现过预期形式的谓词锁。 （第 298 页）

2.3 PostgreSQL 中的隔离级别

随着时间的推移，基于锁的事务管理协议被快照隔离（Snapshot Isolation，SI）协议所取代。这种方法的思想是，每个事务访问的是在特定时间点呈现的数据一致性快照。这个快照包括在快照生成之前提交的所有当前更改。

快照隔离最大限度地减少了所需的锁数量。实际上，只有在尝试并发更新时，行才会被锁定。在所有其他情况下，操作均可以并发执行：写操作不会锁定读操作，读操作也永远不会锁定任何东西。 （第 268 页）

PostgreSQL 使用了 SI 协议的多版本形式。多版本并发控制意味着，在任何时候数据库系统都可以包含同一行的多个版本，因此 PostgreSQL 可以将适当的版本包含在快照中，而不是中止尝试读取老版本数据的事务。

基于快照，PostgreSQL 的隔离级别与标准中指定的要求有所不同 — 总体上更加严格。脏读从设计上是禁止的。技术上，你可以指定读未提交级别，但其行为与读已提交相同，因此我将不再提及这个级别。可重复读既不允许不可重复读，也不允许幻读 (尽管它不保证完全隔离)。但在某些情况下，读已提交隔离级别存在更新丢失的风险。 （第 161 页）

隔离级别更新丢失脏读不可重复读幻读其他异常
Read Committed是—是是是
Repeatable Read————是
Serializable—————
表 2.2 PostgreSQL 中的隔离级别与异常

在探讨隔离的内部机制之前，让我们从用户的角度分别讨论这三个隔离级别。 （第 97 页）

为此，我们将创建一个账户表；Alice 和 Bob 分别拥有 1000 ₽，但 Bob 拥有两个账户：

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

Read Committed

不会脏读。很容易验证无法读取脏数据。让我们开启一个事务。默认情况下，它使用读已提交 3 隔离级别：

=> 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 | client | amount
----+--------+--------
  1 | alice  | 800.00
(1 row)

在第二个会话中，我们启动另一个事务，此事务也在读已提交级别下运行：

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

可以预见的是，第二个事务看不到任何未提交的更改 — 因为脏读是被禁止的。

不可重复读。现在提交第一个事务的更改。然后第二个事务执行相同的查询：

=> COMMIT;
=> SELECT * FROM accounts WHERE client = 'alice';
 id | client | amount
----+--------+--------
  1 | 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;

如果一旦重新排列语句就出现了问题，代码便是不正确的。不要自欺欺人地认为永远不会遇到这种麻烦：任何可能出错的事情都会出错。这类错误很难复现，因此，修复它们是一项真正的挑战。

你该如何修正此代码？有几个选项：

  • 用声明式代码替换过程式代码。

    例如，在这个特定案例中，很容易将 IF 语句转换为 CHECK 约束：

ALTER TABLE accounts ADD CHECK amount >= 0;

现在你不需要在代码中进行任何检查：只需简单地运行命令，并处理在尝试违反完整性约束时引发的异常就足够了。

  • 使用单个 SQL 语句。

    如果一个事务在另一个事务的语句之间的时间间隙内提交，那么数据一致性可能会受到影响，从而改变数据的可见性。如果只有一个语句，则不存在这样的间隙。

PostgreSQL 提供了足够的能力，可以用一个 SQL 语句解决复杂任务。特别是，它提供了可以包含 INSERT、UPDATE、DELETE、MERGE 等语句的通用表表达式（CTE），以及以原子方式实现“如果行不存在则插入，否则更新”逻辑的 INSERT ON CONFLICT 语句。（v. 15）

  • 使用显式锁定。

最后的手段是手动对所有需要的行（SELECT FOR UPDATE）甚至整张表（LOCK TABLE）设置排他锁。这种方法总是有效，但会抵消 MVCC 的所有优势：一些本来可以并发执行的操作将改为串行执行。 （第 268 页） （第 261 页）

不一致读。然而，事情并非总是那么简单。PostgreSQL 的实现方式允许一些其他鲜为人知的异常，这些异常并不受标准约束。

假设第一个事务已经开始在 Bob 的账户之间转移资金：

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

与此同时，另一个事务开始遍历 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 ₽，因为它读取了不正确的数据。这种异常称为不一致读（read skew）。

在读已提交级别该如何避免这种异常？答案很明显：使用单个语句。例如，像这样：

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

到目前为止，我一直在强调数据的可见性只能在语句之间发生变化，但事实真的如此吗？如果查询运行了很长时间呢？在这种情况下，它能否看到处于不同状态的数据的不同部分？

让我们试试。一种便捷的方式是通过调用 pg_sleep 函数为语句添加延迟。然后立即读取第一行，但第二行将不得不等待两秒钟：

=> SELECT amount, pg_sleep(2) -- 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)

但情况也并非那么简单。如果查询中包含一个声明为 VOLATILE 的函数，而这个函数执行了另一个查询，那么这个嵌套查询看到的数据将与主查询看到的数据不一致。

让我们使用以下函数检查 Bob 账户中的余额：

=> 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。问题在于，PostgreSQL 默认使用的恰好就是这种隔离级别和易变性类别。因此，我们不得不承认，这个陷阱设置得非常狡猾。

不一致读而不是更新丢失。 在同一个语句内，也可能发生不一致读异常，即使发生方式有些出人意料。

让我们看看如果两个事务试图修改同一行时会发生什么。目前 Bob 的两个账户中总共有 1000 ₽：

=> SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+--------
  2 | bob    | 200.00
  3 | 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 | client |  amount
----+--------+----------
  2 | bob    | 202.0000
  3 | bob    | 707.0000
(2 rows)

一方面，UPDATE 命令不应看到另一个事务所做的修改；但另一方面，也不应丢失另一个事务已提交的修改。

一旦锁被释放，UPDATE 语句重新读取要更新的行 (但仅限这一行！)。结果，基于总额 900 ₽，Bob 获得了 9 ₽ 的利息。但如果他有 900 ₽，他的账户本就不应该包含在查询结果中。 （第 279 页）

因此，这个事务读到了不正确的数据：有些行处于一个时间点的状态，另一些行却处于另一个时间点的状态。我们再次观察到不一致读异常，而不是更新丢失。

更新丢失。但是，如果数据被不同的 SQL 语句修改，那么重新读取锁定行的技巧将无法防止更新丢失。

此处是一个我们已经见过的例子。应用程序读取并记录 (在数据库外) Alice 账户的当前余额： （第 50 页）

=> BEGIN;
=> SELECT amount FROM accounts WHERE id = 1;
 amount
--------
 800.00
(1 row)

同时，另一个事务也做同样的事情：

=> BEGIN;
=> SELECT amount FROM accounts WHERE id = 1;
 amount
--------
 800.00
(1 row)

第一个事务将先前记录的值增加了 100 ₽，并提交了这个更改：

=> UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1
RETURNING amount;
 amount
--------
 900.00
(1 row)
UPDATE 1
=> COMMIT;

第二个事务也做同样的事情：

=> UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1
RETURNING amount;
 amount
--------
 900.00
(1 row)
UPDATE 1
=> COMMIT;

不幸的是，Alice 丢失了 100 ₽。数据库系统不知道记录的 800 ₽ 值与 accounts.amount 有某种关联，因此无法防止更新丢失异常。在读已提交隔离级别下，这段代码是不正确的。

Repeatable Read

不会出现不可重复读和幻读。顾名思义，可重复读隔离 4 级别必须保证可重复读取。让我们确认一下，确保幻读也不会发生。为此，我们开启一个事务，将 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, 'charlie', 100.00);
=> SELECT * FROM accounts ORDER BY id;
 id | client  | amount
----+---------+--------
  1 | alice   | 900.00
  2 | bob     | 200.00
  3 | bob     | 800.00
  4 | charlie | 100.00
(4 rows)

在第二个会话中，让我们开启另一个事务，在 BEGIN 命令中明确指定可重复读级别 (第一个事务的隔离级别并不重要)：

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

现在，第一个事务提交其更改，第二个事务重复相同的查询：

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

第二个事务仍然看到和之前相同的数据：新行与更新的行都不可见。在这个隔离级别下，你不必担心在语句之间会发生某些变化。

序列化失败而不是更新丢失。 正如我们已经看到的，在 Read Committed 隔离级别下，两个事务更新同一行可能导致不一致读异常：等待的事务必须重新读取锁定的行，因此它看到该行的状态与其他行相比处于不同的时间点。 （第 61 页）

在可重复读隔离级别下，不允许出现这种异常，如果确实发生了，事务只能因序列化失败而中止。我们可以通过重复带利息计算的场景来确认这一点：

=> SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+--------
  2 | bob    | 200.00
  3 | 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 | client | amount
----+--------+--------
  2 | bob    | 200.00
  3 | bob    | 700.00
(2 rows)

对同一行的任何其他并发修改，即使没有涉及我们关注的列，也会引发同样的错误。

如果我们尝试基于之前存储的值更新余额，我们也会遇到这个错误：

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT amount FROM accounts WHERE id = 1;
 amount
--------
 900.00
(1 row)
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT amount FROM accounts WHERE id = 1;
 amount
--------
 900.00
(1 row)

=> UPDATE accounts SET amount = 900.00 + 100.00 WHERE id = 1
RETURNING amount;
 amount
---------
 1000.00
(1 row)
UPDATE 1
=> COMMIT;
    => UPDATE accounts SET amount = 900.00 + 100.00 WHERE id = 1
    RETURNING amount;
    ERROR:  could not serialize access due to concurrent update
    => ROLLBACK;

一个实用的见解是：如果你的应用程序对写事务使用可重复读隔离级别，那么必须准备好重试那些由于序列化失败而结束的事务。对于只读事务，这种结果是不会发生的。

不一致写入。 正如我们已经看到的，PostgreSQL 在 Repeatable Read 隔离级别的实现可以防止标准中描述的所有异常，但并不是所有可能的异常——没有人知道异常究竟有多少。不过，有一个重要事实已经得到证明：无论还有多少其他异常，快照隔离都恰好留下两种异常。

其中第一种异常是不一致写入（write skew）。

假设有这样一条一致性规则：只要同一客户所有账户的总余额非负，就允许该客户的个别账户余额为负。

第一个事务获取 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 | client | amount
----+--------+---------
  2 | bob    | -400.00
  3 | bob    |  100.00
(2 rows)

Bob 的总余额现在是负数，尽管如果两个事务分开运行，都是正确的。

只读事务异常。 只读事务异常是 Repeatable Read 隔离级别允许的第二种、也是最后一种异常。要演示它，需要运行三个事务，其中两个事务更新数据，第三个事务只读。

首先让我们先恢复 Bob 的余额：

=> UPDATE accounts SET amount = 900.00 WHERE id = 2;
=> SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+--------
  3 | bob    | 100.00
  2 | 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 账户状态）。

不过，设想此时启动第三个（只读）事务，查询 Bob 各账户的余额。它可以看到第二个事务（已经提交）的更改，但看不到第一个事务（尚未提交）的更改：

=> SELECT * FROM accounts WHERE client = 'bob'; -- 3
 id | client | amount
----+--------+--------
  2 | bob    | 900.00
  3 | bob    |   0.00
(2 rows)

另一方面，我们此前已经确定，第二个事务应当被视为在第一个事务之后启动。这个异常的后果要到第一个事务完成后才会显现：

=> COMMIT;

此时，Bob 的账户总余额是 900 ₽（这是只读事务看到的结果），但利息却是按 1000 ₽ 的总额计算的：

=> SELECT * FROM accounts WHERE client = 'bob';
 id | client |  amount
----+--------+----------
  2 | bob    | 910.0000
  3 | bob    |     0.00
(2 rows)

Serializable

在 Serializable 5 隔离级别下，所有可能的异常都会被防止。Serializable 实际上是在快照隔离之上构建的。Repeatable Read 下不会发生的异常（例如脏读、不可重复读和幻读）在 Serializable 下同样不会发生。而 Repeatable Read 下确实可能发生的两种异常（不一致写入和只读事务异常）会被特殊检测，并在这种情况下中止事务，从而产生我们熟悉的序列化失败。

没有异常。 让我们确认不一致写入场景最终会以序列化失败结束： （第 67 页）

=> 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
=> 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 提供了一个有趣的解决方案：可以推迟此事务，直到其执行变得安全。这是 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 | client | amount
----+--------+--------
  2 | bob    | 900.00
  3 | 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 | client | amount
----+--------+---------
  1 | alice  | 1000.00
(1 row)
=> SELECT * FROM accounts WHERE client = 'bob';
 id | client |  amount
----+--------+----------
  2 | bob    | 910.0000
  3 | bob    |     0.00
(2 rows)
=> COMMIT;

因此，如果应用程序使用可串行化隔离级别，那么必须准备好重试那些由于序列化失败而结束的事务。(可重复读级别也需要相同的方式，除非应用程序仅限于只读事务。)

Serializable 隔离级别简化了编程，但代价是检测异常和强制中止部分事务的开销。声明只读事务时显式使用 READ ONLY，可以降低这种影响。但真正的问题当然是被中止事务的比例有多大，因为这些事务必须重试。如果 PostgreSQL 只中止那些确实在数据上发生不兼容交叉的事务，情况就会好得多。然而，这种实现不可避免地会消耗过多资源，因为它必须跟踪每一行上的操作。

当前的实现允许误判：PostgreSQL 可以中止一些绝对安全的事务，这些事务仅仅是运气不佳。它们的“运气”取决于许多因素，例如是否存在适当的索引或可用的 RAM 量，因此很难提前预测实际行为。 （第 298 页）

如果使用 Serializable 隔离级别，它必须应用于应用程序的所有事务。把不同隔离级别的事务混用时，Serializable 会在不发出任何警告的情况下表现为 Repeatable Read。因此，使用 Serializable 时，最好修改参数 default_transaction_isolation 的值（默认值： read committed），当然，仍然可以显式指定错误的级别。

实现还有其他限制，例如，在 Serializable 隔离级别运行的查询不能在副本上执行。尽管这一功能仍在不断改进，但当前的限制和开销降低了这个隔离级别的吸引力。（v. 12）

2.4 该使用哪种隔离级别？

PostgreSQL 中的默认隔离级别是读已提交，显然绝大多数的应用程序都使用该级别。这个级别很方便，因为它只允许在失败的情况下中止事务；它不会为了保持数据一致性而中止任何事务。换句话说，不会发生序列化失败，因此你无需关心事务重试。

这个级别的缺点是可能发生大量异常，这在上面已经详细讨论过。开发人员必须始终牢记这些异常，并以防止它们发生的方式编写代码。如果无法在单个 SQL 语句中定义所有需要的操作，那么你必须使用显式锁定。最困难的部分是，代码很难测试与数据不一致相关的错误；这些错误可能以不可预测且几乎无法重现的方式出现，因此也很难修复。

可重复读隔离级别消除了一些不一致问题，但遗憾的是，并非全部。因此，你不仅要谨记剩余的异常，还要修改应用程序以正确处理序列化失败，这当然很不方便。但是，对于只读事务，此级别是对读已提交的完美补充；它对于生成涉及多个 SQL 查询的报告等场景非常有用。

最后，可串行化隔离级别使你完全不用担心数据一致性，这在很大程度上简化了代码的编写。应用程序唯一需要做的是能够重试因序列化失败而中止的任何事务。然而，被中止事务的数量和相关开销可能显著降低系统吞吐量。同时你还应该记住，备库上不支持可串行化隔离级别，并且不能与其他隔离级别结合使用。

  1. postgrespro.ru/docs/postgresql/18/transaction-iso. ↩︎

  2. postgrespro.ru/education/books/dbtech. ↩︎

  3. postgrespro.ru/docs/postgresql/18/transaction-iso#XACT-READ-COMMITTED. ↩︎

  4. postgrespro.ru/docs/postgresql/18/transaction-iso#XACT-REPEATABLE-READ. ↩︎

  5. postgrespro.ru/docs/postgresql/18/transaction-iso#XACT-SERIALIZABLE. ↩︎

PostgreSQL 18 изнутри（俄文原版），© Текст, оформление, ООО «ППГ», 2026; © Издание, ДМК Пресс, 2026，依 Original publication notice 授权使用，详见归属声明。