SQL 标准定义了四个级别的事务隔离。最严格的级别是串行化（Serializable），在串行化中，一组可序列化事务的并发执行与以某种顺序同一时间运行它们效果是一样的。

其他三个级别是根据并发事务之间的交互所产生的现象来定义的，这些现象不会在每一个级别都发生。在串行化级别中，这些现象都是不会发生的。

现象有：

  • dirty read，脏读。一个事务可以读取到另一个并发事务写入但是未提交的数据。
  • nonrepeatable read，不可重复读。事务重新读取之前读取的数据，但是发现该数据已经被另一个事务（在第一个事务重新读取之前事务提交）修改。
  • phantom read，幻读。事务重新执行满足一定搜索条件的查询，发现查询结果因为另一个提交的事务而发生了更改。
  • serialization anomaly，序列化异常。成功提交一组事务的结果与将事务一个一个地一次运行一个事务的顺序不一致。

下表中描述了 SQL 标准和 PostgreSQL 实现的事务隔离级别。

隔离级别 脏读 不可重复度 幻读 序列化异常
Read uncommitted SQL 标准允许，但 PG 不允许 可能 可能 可能
Read committed 不可能 可能 可能 可能
Repeatable read 不可能 不可能 SQL 标准允许，但 PG 不允许 可能
Serializable 不可能 不可能 不可能 不可能

在 PostgreSQL 中，你可以使用四个标准事务隔离级别中的任何一个，但是内部只实现了三个隔离级别，即 PostgreSQL 的 Read Uncommitted 模式的行为类似于 Read Committed。这是将标准隔离级别映射到 PostgreSQL 的多版本并发控制体系结果的唯一合理方法。

上表中还显示 PostgreSQL 的可重复读隔离级别是不会发生幻读的，但是在 SQL 标准中这是允许发生的。更高标准的保证是可以接受的。

注意：

  • 一些 PostgreSQL 数据类型和函数有关于事务的特殊规则。特别是，对序列的更改对其他事务都是立即可见的，并且如果进行更改的事务终止是不会回滚的。
  • 在 9.0 及更低版本中，Repeatable read 级别被当作 Serializable，因为它不会出现 ANSI SQL-92 标准中定义的三种异常。但 9.1 版中 可串行化快照隔离（Serializable Snapshot Isolation，SSI） 的实现引入了真正的 Serializable 级别，该级别已被改称为 Repeatable read

一、Read Committed

Read Committed 是 PostgreSQL 中默认的隔离级别。当事务使用此隔离级别时，select 查询语句（不带 for update/share 子句）只能看到在查询开始前提交的数据；在执行查询过程中，看不到未提交的数据或并发事务提交的更改。实际上，select 查询语句看到的是截止到执行查询前的数据库的快照。

However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed.

然而，select 查询语句可以看到在其事务中的之前执行的更新的数据，即使该事务未提交。

还要注意，如果其他事务在第一个 select 启动之后和第二个 select 启动之前提交更改，则两个连续的 select 查询可以看到不同的数据，即使他们在同一个事务中。

update、delete、select for update 和 select for share 命令在搜索目标行方面的行为与 select 是相同的：它们将只查找在命令开始执行之前提交的行。但是，当发现这些目标行时，它可能已经被另一个并发事务更新（或删除或锁定）了。这种情况下，待更新者将等待第一个更新事务提交或回滚（如果它仍在进行中）。如果第一个更新者回滚，它的影响是无效的，第二个更新者可以继续更新最初找到的行。如果第一个更新者提交的是删除操作，则第二个更新程序将忽略第一个更新程序删除的行；如果第一个更新者提交的是更新操作，则第二个更新程序将继续尝试执行其操作。操作的搜索条件（where子句）会被重新评估以确认更新后的行是否还符合搜索条件。如果符合，第二个更新程序将使用更新后的行继续其操作。在 select for update 和 select for share 两种情况下，这意味着被锁定并返回给客户端的是行被更新后的版本。

insert ... on conflict do update ... 子句具有类似的行为。在 Read Committed 行为下，每条数据都会被插入或更新，除非有错误发生，否则这两种结果之一肯定会发生。如果冲突来自于另外一个事务，且该事务对 insert 操作来说结果尚不可见，那么 update 子句仍然会作用于那一行数据，即使按照常规来说，该行的任何版本对当前命令都是不可见的。

在 Read Committed 行为下，insert ... on conflict do nothing 子句的 insert 可能会因为另一个事务的结果（该事务的效果对 insert 快照不可见）而导致插入无法进行。

merge 允许用户指定 insert、update 和 delete 子命令的各种组合。一个同时包含 insert 和 update 子命令的 merge 命令看起来与 insert ... on conflict do update 命令相似，但是并不会保证 insert 和 update 一定会执行。待补充。。。

二、Repeatable read

可重复读隔离级别仅能看到事务开始前已提交的数据，它永远不会在事务执行过程中看到未提交的数据或由并发事务提交的更改。但是，每个查询可以看到在其事务自身内部先前执行的更新，即使这些更新尚未提交。

PostgreSQL 中的此级别对比 SQL 标准中的可重复读隔离级别有更有利的保证，可以防止除了序列化异常之外的所有异常现象。

此级别与 read commited 级别的不同之处在于，可重复读事务中的查询语句会看到事务中第一个非事务控制语句开始时的数据快照，而不是事务内当前语句开始执行时的数据快照。

因此，单个事务内的连续 select 会看到相同的数据，即它们不会看到在其事务开始后提交的其他事务所做的更改。

有序可能会发生序列化异常，使用此隔离级别的应用程序要做好重试事务的准备。

update、delete、select for update 和 select for share 命令在搜索目标行方面的行为与 select 是相同的：它们只会找到在事务开始时间之前已经提交的目标行。然而，当找到这样的目标行时，它可能已经被另一个并发事务更新（或删除或锁定）。此时，可重复读事务将等待第一个更新事务提交或回滚（如果它仍在进行中）。如果第一个更新回滚了，那么它的影响是无效的，可重复读事务可以继续更新最初找到的行。但如果第一个更新者提交了（提交可能是更新或删除而不仅仅是锁定），那么可重复读事务将会被回滚，并有如下异常消息：

1
ERROR:  could not serialize access due to concurrent update

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

当应用程序收到此错误消息时，它应该终止当前事务，并从头开始重试整个事务。第二次执行时，事务会将之前提交的更改视为数据库初始视图的一部分，因此在使用新版本的行作为新事务更新的起点时，不会存在逻辑冲突。

注意：只有更新事务可能会发生重试，只读事务永远不会有序列化冲突。

The Repeatable Read mode provides a rigorous guarantee that each transaction sees a completely stable view of the database. However, this view will not necessarily always be consistent with some serial (one at a time) execution of concurrent transactions of the same level. For example, even a read-only transaction at this level may see a control record updated to show that a batch has been completed but not see one of the detail records which is logically part of the batch because it read an earlier revision of the control record. Attempts to enforce business rules by transactions running at this isolation level are not likely to work correctly without careful use of explicit locks to block conflicting transactions.

The Repeatable Read isolation level is implemented using a technique known in academic database literature and in some other database products as Snapshot Isolation. Differences in behavior and performance may be observed when compared with systems that use a traditional locking technique that reduces concurrency. Some other systems may even offer Repeatable Read and Snapshot Isolation as distinct isolation levels with different behavior. The permitted phenomena that distinguish the two techniques were not formalized by database researchers until after the SQL standard was developed, and are outside the scope of this manual. For a full treatment, please see berenson95.

三、Serializable

Serializable 隔离级别提供了最严格的事务隔离。此级别模拟所有已提交事务的串行执行，就像事务是一个接一个地、串行地执行，而不是并发执行一样。与可重复读级别类似，使用次级别的应用程序必须准备好因序列化失败而重试事务。实际上此级别的工作方式与可重复读级别完全相同，只是它还监控可能导致一组可序列化事务并发执行与这些事务串行执行时所有可能不一致的情况。此监控不会触发可重复读以外的阻塞，但监控会有一点开销，并且检测肯到可能导致序列化异常时会出发序列化失败。

例如，有表 mytable，数据如下：

1
2
3
4
5
6
 class | value
-------+-------
     1 |    10
     1 |    20
     2 |   100
     2 |   200

可序列化事务A：

1
SELECT SUM(value) FROM mytable WHERE class = 1;

然后将结果（30）作为值插入到 class＝2 的新行中。同时，可序列化事务B计算：

1
SELECT SUM(value) FROM mytab WHERE class = 2;

并将结果（300）作为值插入到 class=1 的新行中。两个事务都尝试提交，如果其中一个事务以可重复读隔离级别运行，则允许两者提交；但是由于没有与实际结果一直的串行化执行顺序，使用序列化事务将允许一个事务提交，并将使用如下消息回滚另一个事务：

1
ERROR:  could not serialize access due to read/write dependencies among transactions

因为，如果事务A在事务B之前执行，B将计算总和为330，而不是300。类似地，其他顺序将导致A的计算总和与实际结果不同。

相关链接

tr-95-51.pdf (microsoft.com)

PostgreSQL: Documentation: 16: 13.2. Transaction Isolation

OB tags

#PostgreSQL #事务 #并发