哈Ha！ 在本文中，我开始了有关PostgreSQL内部结构的一系列循环（或一系列循环？通常来说，是个好主意）。

该材料将基于我们与Pavel pluzanov一起进行的管理培训课程 。 并非每个人都喜欢看视频（我当然不喜欢），但是即使看了评论，看幻灯片也完全是“错误的”。

当然，文章不会一对一地重复课程的内容。 我将只讨论一切工作原理，省略行政管理本身，但是我将尝试更详细和详细地做。 而且我相信这些知识对应用程序开发人员有用的不亚于管理员。

我将重点介绍那些已经具有使用PostgreSQL的经验的人，至少可以概括地说，可以想象发生了什么。 对于初学者来说，文本会有些沉重。 例如，我不会说任何有关如何安装PostgreSQL和运行psql的信息。

各个版本之间讨论的内容不会有太大变化，但是我将使用当前的第11个“原始” PostgreSQL。

第一个周期专门讨论与隔离和多版本相关的问题，其计划如下：

1. 标准和PostgreSQL理解的隔离（本文）；
2. 层，文件，页面 -在物理级别发生的事情；
3. 行版本，虚拟和嵌套事务 ；
4. 数据快照和行版本，事件范围的可见性 ；
5. 页内清洁和HOT更新 ；
6. 正常清洁 （真空）；
7. 自动清洁 （ 自动真空）；
8. 交易计数器溢出并冻结 。

好吧，走吧。

什么是绝缘，为什么重要？

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

如果应用程序从数据库接收到错误的数据，或者应用程序将错误的数据写入数据库，则您不太可能会感到高兴。

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

是正确且不可或缺的 -一样吗？ 不完全是 并非所有限制都可以在数据库级别制定。 限制的一部分太复杂了，例如，它一次覆盖了多个表。 即使原则上可以在数据库中定义限制，但出于某种原因却没有定义，这并不意味着可以违反该限制。

因此， 正确性比完整性更严格，但我们不知道它到底是什么。 仍然需要认识到，正确性标准是一种应用程序，正如我们希望的那样，它是正确编写的，绝不会出错。 无论如何，如果应用程序没有违反完整性，而是违反了正确性，则DBMS不会知道它，也不会抓住他的手。

从现在开始，我们将正确性称为一致性。

但是，让我们假设该应用程序仅执行正确的语句序列。 如果应用程序正确，那么DBMS的作用是什么？

首先，事实证明，正确的语句序列可以暂时破坏数据一致性，这（很奇怪的是）是正常的。 一个骇人听闻但可以理解的例子是将资金从一个帐户转移到另一个帐户。 一致性规则听起来像这样： 转账永远不会改变帐户中的总金额 （这种规则很难用SQL编写为完整性约束，因此它存在于应用程序级别，并且对于DBMS是不可见的）。 转帐包括两个操作：第一个操作减少一个帐户中的资金，第二个-增加另一个帐户中的资金。 第一个操作违反了数据的一致性，第二个操作-还原。

一个好的练习是在完整性约束级别上实现上述规则。 你很虚弱吗？ ©

如果第一个操作完成而第二个操作没有完成怎么办？ 毕竟，这很容易：在第二次操作中，电力可能会丢失，服务器可能会崩溃，零除可能会发生-但您永远不会知道。 很明显，一致性被破坏，这是不允许的。 原则上，可以在应用程序级别解决这种情况，而付出了令人难以置信的努力，但是幸运的是，这是没有必要的：DBMS会解决这一问题。 但是为此，她必须知道两个操作构成一个不可分割的整体。 那是一笔交易 。

事实证明，这很有趣：DBMS知道操作是一个事务，因此可以通过保证事务的原子性来帮助保持一致性，而对特定的一致性规则一无所知。

但是还有第二点，更微妙的一点。 一旦系统中同时出现几笔绝对正确的并发事务，它们一起就可能无法正常工作。 这是由于操作顺序是混合的：不能假定一个事务的所有操作都首先执行，然后才执行另一事务的所有操作。

关于同时性的说明。 实际上，同时，事务可以在具有多核处理器，磁盘阵列等的系统上运行。但是，对于在分时共享模式下按顺序执行命令的服务器，所有相同的考虑都适用：如此多的周期，一个事务被执行，如此多的周期是不同的。 有时，术语“ 竞争执行力”用于总结。

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

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

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

当然，不可能拒绝同时执行：否则，可以讨论什么样的性能？ 但是您不能使用不正确的数据。

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

因此，我们来定义：

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

此定义结合了首字母缩写ACID的前三个字母。 它们之间的联系如此紧密，以至于没有一个人考虑就没有意义。 实际上，很难撕下字母D（耐用性）。 毕竟，在系统崩溃的情况下，对未提交事务的更改仍保留在其中，您必须执行一些操作才能恢复数据一致性。

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

SQL隔离级别和异常

SQL标准很早就描述了四个隔离级别。 通过列出在该级别执行事务时允许或不允许的异常来确定这些级别。 因此，要谈论这些级别，您需要熟悉异常情况。

我强调，在这一部分中，我们谈论的是标准，即某种理论，实践高度依赖该理论，但与此同时却背道而驰。 因此，这里的所有示例都是推测性的。 他们将对客户帐户使用相同的操作：这很明显，尽管诚然，这与银行业务的实际安排无关。

更新失败

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

例如，两次交易将使同一帐户上的金额增加100₽。 第一个事务读取当前值（1000₽），然后第二个事务读取相同的值。 第一笔交易增加了金额（结果为1100₽）并写入了该值。 第二个事务执行相同的操作-获得相同的1,100₽并将其写入。 结果，客户损失了100英镑。

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

脏读和未提交读

带着脏读，我们已经在上面见过。 当一个事务读取另一个事务所做的未决更改时，会发生此异常。

例如，第一笔交易将所有资金从客户的帐户转移到另一个帐户，但不记录更改。 另一笔交易读取帐户状态，收到0₽并拒绝向客户发行现金-尽管事实上第一笔交易被中断并取消了其更改，所以值0在数据库中不存在。

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

不可重复读和已提交读

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

例如，让一致性规则禁止客户帐户中出现负数 。 第一笔交易将使帐户中的金额减少100。 她检查当前值，得到1000₽并确定有可能减小。 此时，第二笔交易会将帐户中的金额减少为零并记录更改。 如果现在第一笔交易重新检查了金额，她将收到0分（但她已经决定减少该金额，并且帐户“减”）。

标准允许在“读取未提交”和“读取已提交”级别进行非重复读取。 但是脏读Read Committed不允许阅读。

幻像读取和可重复读取

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

例如，假设一致性规则禁止客户拥有三个以上的帐户 。 第一笔交易将开设一个新帐户，检查其当前号码（例如2），并确定可以开设。 此时，第二笔交易还将为客户开设一个新帐户并记录更改。 如果现在第一笔交易仔细检查了数量，它将收到3（但已经在开设另一个帐户，客户有4）。

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

缺少异常和可序列化

该标准定义了另一个级别-Serializable-不允许出现异常。 这与禁止丢失更新以及禁止脏的，非重复的和幻像的读取完全不同。

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

可序列化一般应防止所有异常。 这意味着在此级别上，应用程序开发人员无需考虑同时运行。 如果事务单独执行正确的语句序列，则数据将与这些事务的同时操作保持一致。

摘要板

现在，您可以为大家带来一张知名的桌子。 但是在这里，为清楚起见，最后一列被添加到其中，这不在标准中。

为什么正是这些异常？

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

显然，似乎没有人知道这一点。 但是这里的实践肯定超过了理论，因此很可能我们没有考虑其他异常（有关SQL标准的言论：92）。

此外，假设绝缘应建立在互锁上。 广泛使用的两阶段阻塞协议 （2PL）的思想是，在事务处理期间，事务处理将阻塞正在使用的行，并在完成后释放锁。 大大简化，一个事务捕获的锁越多，它与其他事务的隔离性就越好。 但是，系统的性能遭受的损失更大，因为交易不再协同工作，而是开始排成一行。

在我看来，该标准的隔离级别之间的差异是由必需的锁的数量精确解释的。

如果一个事务阻止修改后的行发生更改，但又阻止了其读取，则得到“读取未提交”级别：不允许丢失丢失的更改，但可以读取未提交的数据。

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

如果一个事务同时阻止了可读行和可变行的读取和更改，我们将获得“可重复读取”级别：重复读取该行将产生相同的值。

但是Serializable有一个问题：无法锁定尚不存在的行。 因此，仍然存在幻像读取的可能性：另一个事务可能会添加（但不能删除）属于先前执行的查询条件的行，并且将重新获取该行。

因此，要实现可序列化的级别，普通锁是不够的-您不需要阻塞行，而是阻塞条件（谓词）。 这种锁称为谓词 。 它们是在1976年提出的，但是它们的实际适用性受到相当简单的条件的限制，对于这些条件，很显然如何组合两个不同的谓词。 据我所知，在任何系统中都没有实现这种锁。

PostgreSQL隔离级别

随着时间的流逝，快照隔离取代了阻塞事务管理协议 。 他的想法是，每个事务在特定的时间点都使用一致的数据快照，其中只有那些在创建快照之前记录的更改会落在其中。

这种隔离不会自动导致脏读。 正式地，在PostgreSQL中，您可以指定“读取未提交”级别，但是它的工作方式与“读取已提交”一样。 因此，我们将不再进一步讨论“读取未提交”级别。

PostgreSQL实现了该协议的多种版本。 多版本化的想法是，同一字符串的多个版本可以在DBMS中共存。 这使您可以使用可用版本构建数据的快照，并以最少的锁获取安全。 实际上，只有重复更改同一行才会被阻止。 所有其他操作都同时执行：写事务永远不会阻止读取事务，读事务永远不会阻止任何人。

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

我们将在以下文章中讨论如何在“幕后”实现多版本化，现在我们将通过用户的眼光详细介绍这三个级别（您知道，最有趣的地方隐藏在“其他异常”之后）。 为此，创建一个帐户表。 爱丽丝和鲍勃各有$ 1,000，但鲍勃有两个未结帐户：

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

假设第一笔交易开始将资金从一个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允许您定义函数，而函数具有可变性类别的概念。 如果在一个请求中调用了一个volatile函数（具有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) 

我强调指出，只有在“读取已提交”隔离级别以及“挥发性”可变性类别下，这种效果才可能发生。 问题在于默认情况下使用了这种隔离级别和这种可变性类别，因此我必须承认-耙子非常好。 不要踩！

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

在更新期间，可能会以某种出乎意料的方式获得一个操作员框架内的不一致读数。

让我们看看当您尝试通过两个事务更改同一行时会发生什么。 Bob现在在两个帐户上有1000 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 interest：

 | => 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的值与account.amount有关。 不要以这种方式编写代码。

可重复读

缺乏非重复和幻像的阅读

隔离级别本身的名称指示读取是可重复的。 我们将对此进行验证，同时我们将确信没有幻像读数。 为此，在第一笔交易中，将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; 

第二个事务继续看到与开始时完全相同的数据：现有行的更改和新行均不可见。

在此级别上，您不必担心两个操作员之间的变化。

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

上面我们说过，当在Read Committed级别使用两个事务更新同一行时，可能会出现读取不一致的异常。 这是由于以下事实：挂起的事务会重新读取锁定的行，因此看不到与其余行相同的时间点。

在“可重复读取”级别，不允许出现这种异常，但是如果发生这种异常，则无法进行任何操作-因此，事务以序列化错误结束。 我们通过用百分比重复相同的场景来进行验证：

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

在其他竞争性行更改的情况下，即使我们感兴趣的列实际上没有更改，也会发生相同的错误。

实际结论 ：如果应用程序使用“可重复读取”隔离级别来写入事务，则应准备好重复以序列化错误结尾的事务。 对于只读事务，这样的结果是不可能的。

输入不一致

因此，在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的帐户状态：

 => 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可实现为基于数据快照的隔离加载项。 在可重复读取期间不会发生的那些异常（例如脏的，不可重复的，幻像读取）不会在可序列化级别上发生。 并且检测到那些出现的异常（不一致的记录和仅读取事务的异常）并中止该事务-已经熟悉的序列化错误无法序列化访问。

输入不一致

为了说明，我们以记录不一致的异常重复该场景：

 => 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中获得（ 另一个补丁 ）。

只读异常

因此，只有读取事务不会导致异常并且不会遭受异常的困扰，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; 

仅由读取器显式声明第三个事务（只读），然后将其延迟（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的行为就像可重复读，而不会发出警告。为什么会这样，我们将在稍后讨论实现时考虑。

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

  ALTER SYSTEM SET default_transaction_isolation = 'serializable'; 

在Boris Asenovich Novikov的书和讲座课程 “数据库技术基础”中，可以找到与事务，一致性和异常有关的更严格的陈述。

我应该使用什么级别的绝缘？

PostgreSQL默认使用Read Committed隔离级别，并且似乎在大多数应用程序中都使用了该级别。这样做很方便，因为只有在发生故障的情况下才可能在该事务上中断事务，但不能防止不一致。换句话说，不会发生序列化错误。

硬币的反面是上面已详细讨论的大量可能的异常情况。开发人员必须时刻牢记它们，并以防止它们发生的方式编写代码。如果不可能在单个SQL语句中制定必要的操作，则必须诉诸显式设置锁。最不愉快的是，代码很难测试与获取不一致的数据相关的错误，并且错误本身可能以无法预测和不可再现的方式发生，因此难以修复。

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

最后，可序列化级别完全消除了对一致性的需求，使代码编写变得更加容易。应用程序唯一需要做的就是在收到序列化错误时能够重复任何事务。但是，中断的事务所占的比例，额外的开销以及无法并行化请求的行为会大大降低系统吞吐量。还要注意，可序列化级别不适用于副本，并且不能与其他隔离级别混合。

待续。