Postgresql 中文操作指南

一些PostgreSQL数据类型和函数对事务行为具有特殊规则。特别是，对序列所做的更改（因此也是使用_serial_声明的列的计数器）对所有其他事务都是立即可见的，并且如果进行更改的事务中止，则不会回滚。请参阅 Section 9.17和 Section 8.1.4。

Read Committed 是 PostgreSQL 中的默认隔离级别。当事务使用此隔离级别时，SELECT 查询（没有 FOR UPDATE/SHARE 子句）只看到在查询开始之前已提交的数据；在查询执行期间，它既看不到未提交的数据，也看不到由并发事务提交的更改。实际上，SELECT 查询会在查询开始运行时看到数据库的一个快照。但是 SELECT 会看到在其自己的事务内执行的先前更新的效果，即使这些更新尚未提交。还需要注意的是，即使在单个事务中，两个连续的 SELECT 命令也可能看到不同的数据，如果其他事务在第一个 SELECT 启动后且在第二个 SELECT 启动前提交更改。

UPDATE、DELETE、SELECT FOR UPDATE 和 SELECT FOR SHARE 命令与 SELECT 在搜索目标行方面表现相同：这些命令只会查找截至命令开始时间已提交的目标行。但是，在找到这样的目标行之前，它可能已被另一个并发事务更新（或删除或锁定）。在这种情况下，准更新者将等待第一个更新事务提交或回滚（如果事务仍处于进行中）。如果第一个更新者回滚，则其效果将被否决，第二个更新者可以继续更新最初找到的行。如果第一个更新者提交，则如果第一个更新者删除了行，第二个更新者将忽略该行，否则，它将尝试对其操作应用到行的更新版本。对命令的搜索条件（WHERE 子句）将被重新评估，以查看该行的更新版本是否仍与搜索条件匹配。如果是，则第二个更新者会使用行的更新版本继续进行其操作。对于 SELECT FOR UPDATE 和 SELECT FOR SHARE，这意味着被锁定并返回给客户端的是该行的更新版本。

附有 ON CONFLICT DO UPDATE 子句的 INSERT 的行为方式类似。在已提交读取模式下，将插入或更新提议的每行。除非有无关错误，否则可确保这两个结果之一。如果冲突起源于另一个事务，而其效果对 INSERT 来说尚不可见，则 UPDATE 子句会影响该行，即使该行的 no 版本通常对该命令可见也是如此。

带有 ON CONFLICT DO NOTHING 子句的 INSERT 可能会导致由于另一个事务的结果而无法对某行进行插入，而对 INSERT 的快照来说该事务的效果不可见。同样，这种情况仅在已提交读取模式下才会发生。

MERGE 允许用户指定 INSERT、UPDATE 和 DELETE 子命令的各种组合。同时具有 INSERT 和 UPDATE 子命令的 MERGE 命令与带有 ON CONFLICT DO UPDATE 子句的 INSERT 类似，但不能保证 INSERT 或 UPDATE 都会发生。如果 MERGE 尝试 UPDATE 或 DELETE，并且该行被同时更新但连接条件仍然通过当前目标和当前源元组，则 MERGE 的行为将与 UPDATE 或 DELETE 命令相同，并对行的更新版本执行其操作。但是，由于 MERGE 可以指定多个操作且这些操作可以是条件，因此会在行的更新版本上重新评估每个操作的条件，从第一个操作开始，即使最初匹配的操作出现在操作列表的后面也是如此。另一方面，如果该行是同时更新或删除的，以致连接条件失败，则 MERGE 将接着评估该条件的 NOT MATCHED 操作，并执行第一个成功的操作。如果 MERGE 尝试 INSERT，且存在唯一索引并同时插入了一行重复数据，则会引发唯一性违例错误；MERGE 不会通过重新启动对 MATCHED 条件的评估来尝试避免此类错误。

由于上述规则，更新命令有可能看到不一致的快照：它可以查看并发更新命令对其尝试更新的相同行所产生的效果，但它不会看到这些命令对数据库中的其他行的影响。这一行为使得已提交读取模式不适合涉及复杂搜索条件的命令；不过，它非常适用于较为简单的案例。例如，考虑使用如下事务更新银行余额：

如果两个此类事务同时尝试更改帐号 12345 的余额，我们显然希望第二个事务从该帐号行的更新版本开始。因为每个命令仅影响一个预定的行，所以让它看到该行的更新版本不会产生任何麻烦的不一致。

在已提交读取模式下，更为复杂的用法可能会产生不良结果。例如，考虑一个 DELETE 命令，它对另一个命令同时从其限制条件中添加和删除的数据进行操作，例如，假设 website 是一个包含两行的表，且 website.hits 等于 9 和 10：

即使在 UPDATE 之前和之后都有 website.hits = 10 行，DELETE 也不会产生任何效果。之所以会发生这种情况，是因为预更新的行值 9 被跳过了，而在 UPDATE 完成且 DELETE 获取锁时，新行值不再是 10 而是 11，这不再与该条件匹配。

因为已提交读取模式以包含提交到该即时为止的所有事务的新快照来启动每个命令，所以同一事务中的后续命令无论如何都会看到已提交并发事务的影响。上述问题在于 single 命令是否看到数据库的绝对一致视图。

已提交读取模式提供的部分事务隔离足以满足众多应用程序，且这种模式快速易用；但是，它并不能满足所有情况。执行复杂查询和更新的应用程序可能需要比已提交读取模式提供的更为严格一致的数据库视图。

_Repeatable Read_隔离级别仅看到事务开始前提交的数据；它永远看不到非提交的数据或事务执行期间并发事务提交的更改。（但是，每个查询确实可以看到它自己的事务内执行的先前更新的结果，即使这些更新尚未提交。）这是此隔离级别所要求的比SQL标准更强的保证，并且可以防止 Table 13.1中描述的所有现象，序列化异常除外。如上所述，这是标准明确允许的，该标准只描述了每个隔离级别必须提供的_minimum_保护。

此级别与已提交读取不同之处在于，可重复读取事务中的查询看到的快照截至 transaction 中第一个非事务控制语句开始时的时间，而非截至事务中当前语句的开始时间。因此，single 事务中的连续 SELECT 命令会看到相同的数据，即它们不会看到在自己事务开始后由其他事务提交的更改。

使用此级别的应用程序必须做好由于序列故障而重试事务的准备。

UPDATE、DELETE、MERGE、SELECT FOR UPDATE 和 SELECT FOR SHARE 命令与 SELECT 在搜索目标行方面表现相同：这些命令只会查找截至事务开始时间已提交的目标行。但是，在找到这样的目标行之前，它可能已被另一个并发事务更新（或删除或锁定）。在这种情况下，可重复读取事务将等待第一个更新事务提交或回滚（如果事务仍处于进行中）。如果第一个更新者回滚，则其效果将被否决，可重复读取事务可以继续更新最初找到的行。但如果第一个更新者提交（并且实际上更新或删除了该行，而不只是对其进行了锁定），则可重复读取事务将回滚，并显示以下消息

因为可重复读取事务不能修改或锁定可重复读取事务开始后由其他事务更改的行。

当应用程序收到此错误消息时，它应该中止当前事务，然后从头开始重试整个事务。在第二次尝试中，该事务会看到先前已提交的更改作为其对数据库的初始视图的一部分，因此使用新版本的行作为新事务更新的起点在逻辑上没有冲突。

请注意，可能只有更新事务需要重试；只读事务永远不会有序列冲突。

可重复读取模式提供了一个严格的保证，保证每个事务看到的是数据库完全稳定的视图。然而，此视图并不一定始终与同一级别并发事务的某个序列（一次一个）执行一致。例如，即使在此级别进行只读事务，它也可能会看到控制记录已更新，以显示某个批已完成，但 not 可能会看到属于该批的某个详细记录，因为它读取了控制记录的早期版本。在不仔细明确锁定来阻止冲突事务的情况下，试图通过在此隔离级别运行的事务来强制业务规则不太可能正确起作用。

可重复读隔离级别采用了一种在学术数据库文献和某些数据库产品中称为 Snapshot Isolation 的技术实现。与使用传统锁定技术的系统相比，可能会观察到行为和性能的差异，这些技术降低了并发性。一些其他系统甚至可能会提供可重复读和快照隔离作为具有不同行为的独特隔离级别。区分这两种技术的允许现象在 SQL 标准开发后才由数据库研究人员正式化，并且超出了本手册的范围。有关完整处理，请参见 [id="berenson95",role="bare"]biblio.html#BERENSON95 [id="berenson95"]。

在 PostgreSQL 9.1 版本之前，对可串行化事务隔离级别的请求将提供与此处描述完全相同的行为。为了保留传统的可串行化行为，现在应该请求可重复读。

Serializable isolated level 提供最严格的事务隔离。此级别为所有已提交的事务模拟串行事务执行；就好像事务逐个执行（串行）而不是并发执行一样。但是，就像可重复读级别一样，使用此级别的应用程序必须准备好由于串行化故障而重试事务。事实上，该隔离级别的工作方式与可重复读完全相同，只不过它还监控可能使一组并发可串行化事务的执行行为不一致（与所有可能串行（一次一个）执行这些事务）的条件。此监控不会引入任何阻塞 超出可重复读本身的阻塞，但监控本身会产生一些开销，并且检测条件也可能导致 serialization anomaly，这会触发 serialization failure 。

例如，考虑一个表 mytab，最初包含：

假设可串行化事务 A 计算：

然后，将结果 (30) 作为 value 插入到具有 class = 2 的新行中。同时，可串行化事务 B 计算：

并得到结果 300，将其插入到具有 class = 1 的新行中。然后，两个事务都尝试提交。如果任何一个事务在可重复读隔离级别下运行，则都将被允许提交；但是，由于没有与结果一致的串行执行顺序，因此使用可串行化事务将允许一个事务提交，而另一个事务将回滚此消息：

这是因为，如果 A 在 B 之前执行，B 将计算总和 330，而不是 300，类似地，另一个顺序将导致 A 计算出不同的总和。

当依靠可串行化事务来防止异常时，非常重要的是，在读取它的事务成功提交之前，从永久用户表读取的任何数据都不被视为有效数据。即使对于只读事务也是如此，只不过在 deferrable 只读事务中读取的数据在读取后即被视为有效，因为此类事务会等到获取一个快照，该快照有保证不受此类问题影响后，才会开始读取任何数据。在所有其他情况下，应用程序都不应依赖于在稍后中止的事务期间读取的结果；而应重试事务，直至成功为止。

为了保证真正的可串行化， PostgreSQL 使用 predicate locking，这意味着它保留了锁，这些锁允许它确定如果写操作先运行，它将如何影响从并发事务的之前读取得到的结果。在 PostgreSQL 中，这些锁不会造成任何阻塞，因此 not 在造成死锁方面发挥任何作用。它们用于识别和标记并发可串行化事务之间的依赖关系，在某些组合中可能会导致串行化异常。相反，Read Committed 或可重复读事务如果需要确保数据一致性，可能需要对整个表进行加锁，这可能会阻塞尝试使用该表的其他用户，或者它可能使用 SELECT FOR UPDATE 或 SELECT FOR SHARE，这不仅会阻塞其他事务，还会导致磁盘访问。

与大多数其他数据库系统一样，PostgreSQL 中的谓词锁基于事务实际访问的数据。这些将显示在 pg_locks 系统视图中，具有 mode 的 SIReadLock 。查询执行期间获取的特定锁将取决于查询使用的计划，并且在事务过程中，多个粒度较小的锁（例如，元组锁）可能会合并为粒度较粗的锁（例如，页面锁），以防止耗尽用于跟踪锁的内存。如果 READ ONLY 事务检测到不会再发生可能导致序列化异常的冲突，则该事务可能在完成之前释放其 SIRead 锁。事实上， READ ONLY 事务通常能够在启动时确定这一事实，并避免获取任何谓词锁。如果您明确请求 SERIALIZABLE READ ONLY DEFERRABLE 事务，它将阻塞，直到可以确定这一事实为止。（这是 only 的情况，其中可序列化事务会阻塞，但可重复读取事务不会。）另一方面，SIRead 锁通常需要保留到事务提交之后，直到重叠的读写事务完成为止。

一致使用可串行化事务可以简化开发。这样做的保障是，任何一组成功提交的并发可串行化事务都将产生与一次执行它们一样的效果，这意味着，如果你可以证明一个事务（按书面形式）在单独运行时会执行正确操作，则可以确信它将在任何可串行化事务组合中执行正确操作，即使没有任何有关其他事务可能做什么的信息，或者它将无法成功提交。非常重要的是，使用此技术的环境应有一种处理串行化故障（始终以 SQLSTATE 值“40001”返回）的通用方法，因为它很难准确预测哪些事务可能导致读/写依赖关系，并且需要回滚以防止串行化异常。读/写依赖关系的监控需要付出成本，以串行化失败终止的事务的重新启动也是如此，但是与使用显式锁定和 SELECT FOR UPDATE 或 SELECT FOR SHARE 相关的成本和阻塞相反，可串行化事务是某些环境中的最佳性能选择。

虽然 PostgreSQL 的可串行化事务隔离级别仅允许并发事务在能够证明存在会产生相同效果的串行执行顺序时进行提交，但它并不能始终防止出现真正的串行执行中不会发生的错误。特别是，即使在明确检查密钥在尝试插入之前是否存在后，也可能出现由与重叠的可串行化事务的冲突引起的唯一约束冲突。可以通过确保 all 首先显式检查是否可以执行此操作的可串行化事务，插入可能发生冲突的密钥来避免此类冲突。例如，想象一个让用户索取新密钥的应用程序，然后尝试通过首先选择它来检查该密钥是否已存在，或者通过选择最大现存密钥并添加一个来生成新密钥。如果一些可串行化事务直接插入新密钥而不遵循此协议，则即使在并发事务的串行执行中不可能发生的情况下，也可能会报告出现唯一约束冲突。

当依靠可串行化事务进行并发控制以获得最佳性能时，应考虑以下问题：

可串行化隔离级别采用了一种在学术数据库文献中称为可串行快照隔离的技术，该技术通过添加串行化异常检查来构建快照隔离。与使用传统锁定技术的其他系统相比，可能会观察到一些行为和性能的差异。有关详细信息，请参见 [id="ports12",role="bare"]biblio.html#PORTS12 [id="ports12"]。