事务隔离

一致性

关系型数据库的关键特性是它们能够确保数据的一致性，也就是数据的正确性。

众所周知，在数据库层面，可以创建完整性约束，如NOT NULL或UNIQUE。数据库系统确保这些约束不会被破坏，所以数据的完整性不会受到影响。

如果所有需要的约束都可以在数据库层面上制定，那么一致性就会得到保证。但是有些条件太复杂了，例如，它们会同时涉及到几个表。此外，即使一个约束可以在数据库中定义，但由于某些原因并没有被定义，这并不意味着这个约束条件可能被违反。

因此，数据的一致性比完整性更严格，但是数据库系统不知道"一致性“到底是什么意思。如果一个应用程序在不破坏完整性的情况下破坏了一致性，那么数据库系统是没有办法发现的。因此，必须由应用程序来制定数据一致性的标准，而且我们必须相信它是正确的，绝不会有任何错误。

但是，如果应用程序总是只执行正确的操作序列，那么数据库系统在哪里发挥作用？

首先，一个正确的操作序列可以暂时破坏数据的一致性，虽然看起来很奇怪，这是很正常的。

一个老套但清晰的例子是将资金从一个账户转移到另一个账户。一致性规则可能听起来像这样：汇款决不能改变受影响账户的总余额。在SQL中制定这个完整性规则是相当困难的（尽管有可能），所以我们假设它是在应用层面上定义的，对数据库系统保持不透明。转账由两个操作组成：第一个操作是从一个账户中提取一些钱，而第二个操作则是将这笔钱添加到另一个账户。第一个操作破坏了数据的一致性，而第二个操作则恢复了数据的一致性。

如果第一个操作成功了，但第二个操作没有成功（因为某些故障），数据一致性就会被破坏。这种情况是不可接受的，但是需要花费大量的精力在应用层面上检测和解决这些问题。幸运的是，这并不需要——这个问题可以完全由数据库系统自己解决，只要数据库系统提前知道这两个操作构成一个不可分割的整体，即一个事务。

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

真正的事务同步执行只能在拥有合适硬件的系统上实现：多核处理器、磁盘阵列等等。但同样的推理也适用于在分时模式下按顺序执行命令的服务器。为了概括起见，这两种情况有时都被称为并发执行。

正确的事务在一起并发执行时表现得不正确，会导致并发异常。

下面是一个简单的例子。为了从数据库中获得一致的数据，应用程序至少不能看到其他未提交的事务所做的任何改变。否则（如果一些事务被回滚），它将看到从来没有存在过的数据库状态。这样的反常现象被称为脏读。还有很多其他的异常情况更加复杂。

当并发地执行事务时，数据库必须保证执行的结果与可能的顺序执行的结果之一相同。换句话说，它必须将事务相互隔离，从而照顾到任何可能的异常。

总而言之，事务是一组操作，它将数据库从一个正确的状态转移到另一个正确的状态（一致性），前提是它被完整地执行（原子性），并且不受其他事务的影响（隔离性）。这个定义结合了ACID缩写的前三个字母所指示的要求。它们紧密交织在一起，以至于把它们放在一起讨论是有意义的。事实上，持久性的要求也很难分割开来：在崩溃之后，系统可能仍然包含一些由未提交的事务所做的改变，你必须对其进行处理以恢复数据一致性。

因此，数据库系统通过考虑事务界限来帮助应用程序保持数据一致性，即使它不知道一致性规则。

不幸的是，完全隔离很难实现，而且会对性能产生负面影响。大多数实际使用的系统使用较弱的隔离级别，可以防止一些异常，但不是所有的异常。这意味着维护数据一致性的工作部分地落在了应用程序上。而这正是为什么需要了解系统中使用的是哪种隔离级别，在这个级别中哪些是可以保证的、哪些不可以，以及如何确保你的代码在这种情况下是正确的，这些是非常重要的。

SQL标准所定义的隔离级别和异常

SQL标准规定了四个隔离级别。这些级别是由在并发事务执行过程中可能或不可能发生的异常所定义的。因此，在谈论隔离级别时，我们必须从异常入手。

我们应该记住，标准是一个理论性的构造：它影响着实践，但实践仍然在很多方面与它相背离。这也是为什么这里所有的例子都是假设性的。在处理银行账户的交易时，这些例子是一目了然的，但我不得不承认，它们与真正的银行业务没有任何联系。

有趣的是，实际的数据库理论也与标准相背离：它是在标准被采用后才发展起来的，而实践已经远远领先。

丢失更新（Lost Update）

两个事务读取一个相同的表中的一行，然后其中一个事务更新了这个行，最后另一个事务更新了同一个表行而没有考虑到第一个事务造成的的任何变化，就会出现丢失更新的异常。

假设有两个事务将使同一个账户的余额增加$100。第一个事务读取当前值（$1000），然后第二个事务读取了相同的值。第一个事务增加余额（使其成为$1100），并将新值写入数据库。第二个事务做了同样的事情：它在增加余额后得到了$1100，并写入这个值。结果，客户损失了$100。

标准定义的所有隔离级别都禁止丢失更新。

脏读和读未提交

当一个事务读取另一个事务所做的未提交的修改时，就会发生脏读异常。

例如，第一个事务将$100转入一个空账户，但并没有提交。另一个事务读取了账户状态（已更新但未提交）并允许客户取款，但是第一个事务被中断并发生了回滚，所以账户是空的。

标准允许读未提交级别发生脏读。

不可重复读和读已提交

当一个事务两次读取同一条记录，而另一个事务在两次读取之间更新（或删除）这条记录并提交更改时，就会出现不可重复读异常，导致第一个事务得到不同的结果。

例如，假设有一条一致性规则，禁止银行账户中出现负数余额。第一个交易将把账户余额取走$100。它检查了当前值，得到了$1000，发现这个操作是合法的。此时，另一个事务从这个账户中提取了所有的钱，并提交了这个改变。如果第一个事务在这个时候再次检查余额，它将得到$0。但第一个事务取钱的决定已经做出，这个操作将导致透支，余额变为$-100。

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

幻读和可重复读

当一个事务执行两个相同的查询，返回一组满足特定条件的行，而另一个事务又增加了一些满足该条件的行，并在第一个事务查询之间的时间间隔内提交更改，就会发生幻读异常，导致第一个事务得到两组数量不同的记录。

例如，假设有一个一致性规则，禁止一个客户拥有超过三个账户。第一个事务要开一个新的账户，所以它检查当前有多少个账户（假设有两个），发现这个操作是合法的。就在这时，第二个事务也为这个客户开了一个新账户，并提交了更改。如果第一个事务再次检查账户数量，它将得到三个（但它已经在开另一个账户，最终客户将有四个账户）。

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

无异常和可串行化

标准还定义了可串行化级别，它不允许任何异常。这并不表示仅仅禁止丢失更新、脏读、不可重复读和幻读。事实上，已知的异常比标准规定的要多得多，还有一些未知的异常。

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

为了阐明这个概念，我将使用标准中提供的一个众所周知的表格；为了清楚起见，这里增加了最后一列。

	丢失更新	脏读	不可重复读	幻读	其他异常
读未提交	-	yes	yes	yes	yes
读已提交	-	-	yes	yes	yes
可重复读	-	-	-	yes	yes
可串行化	-	-	-	-	-

为什么是这些异常？

在所有可能的异常情况中，为什么标准只提到了一些，为什么就是这些？

似乎没有人确切地知道。但也不是没有可能，在采用标准的第一个版本时，根本没有考虑其他异常情况，因为当时理论远远落后于实践。

此外，人们认为隔离必须基于锁。广泛使用的两阶段锁协议（2PL）要求事务在执行过程中锁住受影响的行，并在完成后释放锁。简而言之，一个事务获得的锁越多，它与其他事务的隔离就越好，系统的性能也就越差，因为事务开始排队获得对相同行的访问，而不是并发执行。

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

如果要更新的行被加了写锁而不是读锁，我们就会得到读未提交隔离级别，它允许在提交之前读取数据。

如果要更新的行被同时加了读锁和写锁，我们就会得到读已提交级别：禁止读取未提交的数据，但如果查询被执行多次，可能返回不同的值（不可重复读）。

对需要被读取或更新的所有行进行加锁，将会得到可重复读隔离级别：重复的请求会返回相同的值。

然而，可串行化级别带来了一个问题：不可能锁定一个还不存在的行。这就为幻读的发生留下了机会：一个事务可以添加一条满足前一个查询条件的行，而这一行会出现在下一个查询结果中。

因此，常规的锁不能提供完全的隔离：为了实现它，我们必须锁定条件（谓词）而不是行。这种谓词锁早在1976年开发System R的时候就就出现了；然而，它们实际仅限于简单的条件，即两个不同的谓词是否会发生冲突是提前明确的。据我所知，预期形式的谓词锁从未在任何系统中实现过。

PostgreSQL中的隔离级别

随着时间的推移，基于锁的事务管理协议被快照隔离协议所取代。这种方法的核心是，每个事务访问在某一特定时间点出现的一致的数据快照。该快照包括在获取快照之前月已经提交的所有更改。

快照隔离将所需锁的数量降到最低。事实上，只有并发更新才会锁定一行。在所有其他情况下，操作都可以并发执行：写永远不会锁定读，而读永远不会锁定任何操作。

PostgreSQL使用多版本的快照隔离协议。多版本并发控制意味着在任何时候，数据库系统都可能包含同一行的多个版本，因此PostgreSQL可以在快照中包含一个适当的版本，而不是中止那些试图读取陈旧数据的事务。

基于快照，PostgreSQL的隔离与标准中规定的不同——事实上，它更加严格。脏读在设计上是被禁止的。严格说来，你可以指定读未提交级别，但是它的行为将与读已提交相同，所以我不打算提到这个级别。可重复读既不允许不可重复也不允许幻读（尽管它不能保证完全隔离）。但是在某些情况下，在读已提交级别有丢失修改的风险。

	丢失更新	脏读	不可重复读	幻读	其他异常
读已提交	yes	-	yes	yes	yes
可重复读	-	-	-	-	yes
可串行化	-	-	-	-	-

在探讨隔离的内部机制之前，让我们从用户的角度来讨论三个隔离级别中的每一个。

为此，我们将创建account表；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);

读已提交

没有脏读。 很容易检查出读取脏数据不被允许。让我们开始一个事务。默认情况下，它使用读取已提交的隔离级别：

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语句解决复杂的任务。尤其是它提供了常见的表表达式（CTE），可以包含运算符INSERT, UPDATE, DELETE和实现以下逻辑的INSERT ON CONFLICT： 如果该行不存在，则插入该行，否则执行更新。

• 使用明确的锁。

  最后的办法是对所有需要的行（SELECT FOR UPDATE）甚至整个表（LOCK TABLE）手动设置一个排他锁。这种方法总是有效的，但它使MVCC的所有优点失效：一些可以并发执行的操作将顺序执行。

读偏斜。 然而并不是那么简单。PostgreSQL的实现允许其他不太为人所知的异常，这些异常并没有被标准所规范。

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

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

同时，另一个交易开始在鲍勃的所有账户中循环查询以计算其总余额。它从第一个账户开始（当然是看到它之前的状态）：

|   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) -- two seconds
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的函数，这个函数执行另一个查询，那么这个嵌套查询所看到的数据将与主查询的结果不一致。

让我们用下面的函数来检查鲍勃账户中的余额：

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默认使用的正是这种隔离级别和这种VOLATILE类别。所以我们不得不承认，这里有个很不易被发现的陷阱。

读偏斜而不是丢失更新。 在更新过程中，读偏移异常也可能发生在单个操作上——尽管是以一种出乎意料的方式。

让我们看看如果两个事务试图修改同一行会发生什么。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操作的执行实际上包括两个阶段。首先，根据提供的条件，选择要更新的行。由于第一个事务还没有提交，第二个事务不能看到它的结果，所以选择应计利息的行不会受到影响。因此，Bob的账户满足条件，一旦UPDATE操作完成，他的余额将会增加$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操作就会重新读取要更新的行（但也只有这一行！）。结果，Bob得到了$9的利息，基于$900的总额。但是，如果他有$900，他的账户一开始就不应该被包括在查询结果中。

因此，我们的事务返回了不正确的数据：不同的行从不同的快照中被读取。我们没有看到丢失更新，而是再次观察到了读取偏移异常。

丢失更新。 然而，如果数据被不同的SQL操作修改，重新读取锁定行的技巧将无助于防止丢失更新。

下面是我们已经看过的一个例子。该应用程序读取并注册（在数据库之外）Alice账户的当前余额：

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有某种关系，所以它不能预防丢失更新异常。在读已提交隔离级别，这段代码是不正确的。

可重复读

没有不可重复读和幻读。 顾名思义，可重复读隔离级别必须保证可重复读。让我们来检查一下，确保幻读也不会发生。为此，我们将启动一个事务，将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.0
|   
|   COMMIT;

第二个事务仍然看到与之前相同的数据：新行和更新行都不可见。在这个隔离级别，你不必担心在操作之间会有什么变化。

串行化失败而不是丢失更新。 正如我们已经看到的，如果两个事务在读已提交级别更新同一行，可能导致读偏斜异常：等待的事务必须重新读被锁定的行，所以它看到这行的状态与其他行相比是不同的时间点。

这种异常在可重复读隔离级别是不被允许的，如果它真的发生了，事务只能以串行化失败的方式被中止。让我们执行应计利息的场景来检查一下：

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的可重复读隔离级别的实现可以防止标准中描述的所有异常。但并不是所有可能的异常：没人知道到底有多少种异常存在。然而，一个重要的事实是可以肯定的：快照隔离并不能仅仅避免两个异常，不管还有多少其他异常。

第一个写偏斜。

让我们定义以下的一致性规则：只要总余额不是负数，就允许在客户的一些账户中出现负数。

第一个事务获取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的余额：

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的账户的状态）。

但让我们想象一下，就在这个时候，我们启动了一个只读事务，查询一个不受前两个事务影响的账户：

|   BEGIN ISOLATION LEVEL REPEATABLE READ; -- 3
|   SELECT * FROM accounts WHERE client = 'alice';
|    id | client | amount 
|   ----+--------+--------
|     1 | alice  | 1000.00
|   (1 rows)

而现在，第一个事务才被提交：

COMMIT;

此时第三个事务应该看到什么状态？在启动之后，它可以看到第二个事务（已经提交）所做的修改，但看不到第一个事务的（还没有提交）。但正如我们早已明确的，第二个事务应该被视为在第一个事务之后启动的。第三个事务看到的任何状态都是不一致的——这正是只读事务异常的意思：

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

可串行化

可串行化隔离级别禁止所有可能的异常。这个级别实际上是建立在快照隔离之上的。那些在可重复读隔离级别中不会发生的异常（如脏读、不可重复读或幻读）也不能在可串行化级别中发生。而另外两种可能发生的异常（写偏移和只读事务异常）会以一种特殊的方式被检测到，从而中止事务，造成之前提到的串行化失败。

没有异常。 让我们确保写偏移的情况最终会以串行化失败而结束：

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.00
|     3 | bob    |   0.00
|   (2 rows)
|   COMMIT;

因此，如果应用程序使用可串行化隔离级别，它必须准备好重做因串行化失败而结束的事务。(可重复读级别需要同样的方法，除非应用程序只执行只读事务)。

可串行化隔离级别带来了编程的便利，但代价是异常检测和强制终止某些事务所带来的开销。你可以在声明只读事务时使用READ ONLY来降低这种开销。但主要的问题是，被中止的事务的比例有多大——因为这些事务将不得不被重做。如果PostgreSQL只中止那些导致数据冲突和不兼容的事务，那么就不会太糟糕。但是这样的方法将不可避免地过于耗费资源，因为它将涉及到对每一行的追踪。

目前的实现允许误判：PostgreSQL可以中止一些绝对安全的事务，这是随机的。是否被中止取决于很多因素，例如是否存在适当的索引或可用的RAM数量，所以实际的行为很难事先预测。

如果你使用可串行化级别，它必须被应用程序的所有事务所遵守。当与其他级别结合时，可串行化的行为与可重复读一样。因此，如果你决定使用可串行化级别，最好相应地修改default_transaction_isolation参数——尽管仍然可以通过明确设置不同的级别来覆盖它。

还有一些其他的限制；例如，在可串行化级别运行的查询不能在逻辑复制上执行。尽管这个级别的功能在不断改进，但目前的限制和开销使其不太诱人。

该使用哪个隔离级别？

读已提交是PostgreSQL的默认隔离级别，显然绝大多数的应用都使用这个级别。这个级别很方便，因为它只允许在发生故障的情况下中止事务；它不会中止任何事务以保持数据一致性。换句话说，串行化失败是不会发生的，所以你不需要考虑事务重做。

这个级别的缺点是有大量可能的异常，上面已经详细讨论过了。开发者必须时刻牢记这些问题，编码时避免其发生。如果不能在单一的SQL语句中定义所有需要的操作，就必须显式地采用锁的方式。最困难的是，代码很难测试与数据不一致有关的错误；这种错误会以不可预测和几乎不可复制的方式出现，所以也很难修复。

可重复读隔离级别消除了一些不一致性的问题，但不是所有的。因此，你不仅要记住哪些异常不会被消除，还要修改应用程序以正确处理串行化失败，这很不方便。然而对于只读事务来说，这个级别是对已提交级别的完美补充；它对于一些情况非常有用，比如构建涉及多个SQL查询的复合查询。

最后，可串行化隔离级别允许你完全不担心数据的一致性，这在很大程度上简化了代码的编写。应用程序唯一需要的能力是重做因串行化失败而中止的任何事务。然而，中止的事务数量和相关的开销会大大降低系统的吞吐量。你还应该记住，可串行化不支持在逻辑复制上使用，也不能与其他隔离级别结合使用。