哈Ha！ 在本文中，我开始了有关PostgreSQL内部结构的一系列系列（或一系列系列？总之，这个想法太夸张了）。

该材料将基于Pavel pluzanov和我正在创建的管理培训课程 （俄语）。 并非每个人都喜欢看视频（我绝对不喜欢），并且即使有评论也无法阅读幻灯片。

不幸的是，目前唯一可用的英语课程是PostgreSQL 11 2天简介 。

当然，文章与课程内容将不会完全相同。 我将只谈论一切的组织方式，省略了政府本身，但是我将尝试更详细，更彻底地做到这一点。 而且我相信这样的知识对应用程序开发人员和管理员一样有用。

我将针对那些已经具有使用PostgreSQL经验并且至少总体上了解什么的人。 对于初学者来说，文字太难了。 例如，我不会对如何安装PostgreSQL和运行psql一言不发。

各个版本之间的问题并没有太大的区别，但是我将使用当前的第11个原始PostgreSQL。

第一个系列处理与隔离和多版本并发相关的问题，该系列的计划如下：

1. 标准和PostgreSQL理解的隔离（本文）。
2. 叉子，文件，页面 -在物理级别发生了什么。
3. 行版本 ，虚拟事务和子事务。
4. 数据快照和行版本的可见性； 事件范围。
5. 页内真空和HOT更新 。
6. 正常真空 。
7. 自动抽真空 。
8. 交易ID环绕和冻结 。

出发吧！

在开始之前，我要感谢Elena Indrupskaya将文章翻译成英文。

什么是隔离，为何如此重要？

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

如果应用程序从数据库中获取了不正确的数据，或者应用程序将错误的数据写入了数据库，则您不太可能感到高兴。

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

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

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

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

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

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

一个好的练习是在完整性约束级别上实施上述规则。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

损失更新

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

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

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

脏读和未提交读

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

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

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

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

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

例如，让一致性规则禁止客户帐户上出现负数 。 第一笔交易将使帐户中的金额减少100英镑。 它检查当前值，得到₽1000，并确定有可能减小。 同时，第二笔交易将帐户上的金额减少为零并提交更改。 如果第一笔交易现在重新检查了金额，则将获得₽0（但已决定减少该金额，并且帐户“变成红色”）。

该标准允许不可重复的读取处于“读取未提交”和“读取已提交”级别。 但是Read Committed不允许脏读。

幻像读取和可重复读取

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

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

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

没有异常和可序列化

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

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

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

汇总表

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

为什么正是这些异常？

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

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

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

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

如果事务锁定了要修改的行，使其不能进行更新，而不能进行读取，则将获得“读取未提交”级别：不允许丢失丢失的更改，但是可以读取未提交的数据。

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

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

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

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

PostgreSQL中的隔离级别

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

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

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

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

在下一篇文章中，我们将讨论如何在后台“实现”多版本并发，现在，我们将以用户的眼光详细地研究这三个级别中的每个级别（如您所知，最有趣的是隐藏在“其他异常背后”）。 ”）。 为此，我们创建一个帐户表。 爱丽丝和鲍勃各有1000英镑，但鲍勃有两个已开设的帐户：

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

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

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

  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的实现允许其他不受标准规范的鲜为人知的异常。

假设第一笔交易开始将资金从一个鲍勃的帐户转移到另一个帐户：

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

因此，第二笔交易的总金额为data1100，即数据不正确。 这是不一致的读取异常。

在保持“读取已提交”级别时如何避免这种异常？ 当然，请使用一个运算符。 例如：

  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) 

我强调指出，只有在“读已提交”隔离级别和VOLATILE函数中，这种效果才可能实现。 问题在于，默认情况下，恰好使用了此隔离级别和此波动类别。 不要掉入陷阱！

读取不一致以换取丢失的更改

在更新过程中，尽管以某种出乎意料的方式，我们也可能在单个运算符中读取不一致的内容。

让我们看看当两个事务试图修改同一行时会发生什么。 现在鲍勃在两个帐户上有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; 

同时，在另一笔交易中，所有客户帐户的总余额等于或大于1,000英镑，产生利息：

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

UPDATE运算符的执行包括两个部分。 首先，实际上是执行SELECT，它选择要更新的符合适当条件的行。 因为第一笔交易中的更改未提交，所以第二笔交易看不到它，因此该更改不会影响应计利息行的选择。 好了，鲍勃的帐户满足条件，一旦执行更新，他的余额应增加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将重新读取它尝试更新的行（但仅此行）。 结果，鲍勃根据900英镑的金额累计了9英镑。 但是如果鲍勃有900英镑，那么他的帐户根本就不会出现在选择中。

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

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

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

该数据库不应该受到指责：它获得两个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; 

第二个事务仍然看到与开始时完全相同的数据：看不到现有行或新行的任何更改。

在此级别上，您可以避免担心两个操作员之间可能会发生变化的事情。

序列化错误以换取丢失的更改

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

在“可重复读取”级别，不允许出现此异常，但是如果发生此异常，则无法进行任何操作-因此事务会因序列化错误而终止。 让我们通过重复产生应计利息的相同场景来检查它：

 => 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;<span/> | => UPDATE accounts SET amount = amount * 1.01<span/> | WHERE client IN (<span/> | SELECT client<span/> | FROM accounts<span/> | GROUP BY client<span/> | HAVING sum(amount) >= 1000<span/> | );<span/> 

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

即使一行的任何其他竞争性更改没有发生，即使连续更改任何其他竞争性更改，也会发生相同的错误。

实际结论 ：如果您的应用程序对写入事务使用“可重复读取”隔离级别，则它必须准备好重复因序列化错误而终止的事务。 对于只读事务，此结果是不可能的。

写不一致

因此，在PostgreSQL中，在“可重复读取”隔离级别上，可以防止标准中描述的所有异常。 但并非所有异常都一般。 事实证明，仍然存在两种可能的异常情况。 （这不仅适用于PostgreSQL，而且适用于快照隔离的其他实现。）

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

让以下一致性规则成立： 如果该客户所有帐户上的总金额保持非负数，则允许该客户帐户上的负金额 。

第一笔交易在鲍勃的帐户上获得的金额为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的余额变成了红色。

只读事务异常

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

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

然后，另一笔交易从另一个鲍勃的帐户中提取了资金并进行了更改：

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

可序列化

可序列化级别可防止所有可能的异常。 实际上，可序列化是建立在快照隔离之上的。 可重复读取不会发生的那些异常（例如脏读，不可重复读取或幻像读取）也不会在可序列化级别上发生。 并且检测到那些发生的异常（不一致的写入和只读事务异常），并且事务中止-发生熟悉的序列化错误： 无法序列化访问 。

写不一致

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

 => 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（ patch ）中出现。 并且对副本的查询可以在PostgreSQL 13（ 另一个补丁 ）中开始工作。

只读事务异常

为了使只读事务不会导致异常并且不会遭受异常的困扰，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的行为将类似于Repeatable Read，而没有任何警告。 我们将在稍后讨论实现时，讨论为什么会发生这种情况。

因此，如果您决定使用Serializble，则最好全局设置默认级别（尽管这当然不会阻止您明确指定不正确的级别）：

 ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

您可以在Boris Novikov的“数据库技术基础” 一书和讲座中找到有关交易，一致性和异常问题的更加严格的介绍（仅在Russion中可用）。

使用什么隔离级别？

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

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

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

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

继续阅读 。