Part I: 隔离和多版本并发控制(Isolation and MVCC)

2. 隔离(Isolation)

2.1 一致性(Consistency)

关系数据库的重要特性是它们确保数据的一致性，那就是数据的正确性。

众所周知，数据库级别，创建完整性限制，如NOT NULL或者UNIQUE是可能的。数据库系统能确保这些限制从不会被打破，因此数据完整性永远不会受到损害。

如果所有的约束都能在数据库级别实施起来，一致性将得到保障。但是某些条件太复杂，例如，它们同时接触好几张表。即使可以在数据库中定义一个约束，但由于某种原因不能这样做，也不意味着可能会违反这个约束。

这样看来，数据一致性比完整性更加严格，但是数据库系统并不知道”一致性“具体指的是什么。如果应用程序违反了一致性，而没有违反完整性，数据库系统也没办法找到这种违规。结果，应用程序必须遵守数据一致性的一些约定，而我们不得不相信它被正确的编写，永远 不会犯错。

2.2 隔离级和SQL标准中定义的反常(Anomalies)

2.3 PG中定义的隔离级

随着时间的过去，事务管理中基于锁的协议就被SI（快照隔离）协议所取代。这个方法背后的思想就是每个事务访问数据的一个一致性快照，如同在某一个特定时间点出现的一样。快照包含了所有在快照产生以前的当前提交的变化。

快照隔离会把所需锁的数量最小化。事实上，只有在并发更新试图发生时，才会有行锁。换句话说，操作可以并发地执行：写永远不会锁住读，读永远不会锁其它操作。

PG使用了多版本，而不是直接使用SI协议。多版本并发控制，意味着，在任意时刻，数据库系统可以包含同一数据行的多个版本，这样PG在尝试读取坏死的数据时，可以包含该行一个合适的版本到快照里，而不是终止事务。

基于快照，PG的隔离与标准中规定的要求相比有区别 --- 事实上，它来的更严格。在设计里，脏读是被禁止的。技术上，您可以指定读未提交隔离级，但是它的行为与读已提交是一样的，因此我不会再提到这个隔离级了。可重复读级别不允许非可重复读，也不允许幻像读（即使它不能确保完全隔离）。但是在某些情况下，在读已提交隔离级下有丢失改变的风险。

在探索隔离级的内部原理之前，我们从用户的角度讨论下三种隔离级。

为此目的，我们创建accounts表；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);

读已提交

无脏读. 禁止读脏数据，这个容易检查。我们开启一个事务，默认情况下，它使用读已提交^[1]隔离级：

internals=# BEGIN;
BEGIN
internals=*# SHOW transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 row)

要更精确，默认级别可以通过下边的set，可以按需要进行更改：

=> SHOW default_transaction_isolation;
default_transaction_isolation
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
read committed
(1 row)

刚开启的事务会从客户的帐户取一部分钱，但是并不提交这个变化。它会看到自身的变化，也总是被允许的：

internals=*# UPDATE accounts SET amount = amount - 200 WHERE id = 1;
UPDATE 1
internals=*# SELECT * FROM accounts WHERE client = 'alice';
 id | client | amount
----+--------+--------
  1 | alice  | 800.00
(1 row)

在第2个会话里，我们开启另一个事务，也运行在读已提交隔离级：

internals=# BEGIN;
BEGIN
internals=*# SELECT * FROM accounts WHERE client = 'alice';
 id | client | amount
----+--------+---------
  1 | alice  | 1000.00
(1 row)

如我们所料，第2个事务没有看到任何未提交的变化 --- 脏读是被禁止的。

不可重复读. 现在第1个事务提交改变。接着第2个事务重复相同的查询：

internals=*# COMMIT; -- 会话1
COMMIT

-- 会话2
internals=*# SELECT * FROM accounts WHERE client = 'alice';
 id | client | amount
----+--------+--------
  1 | alice  | 800.00
(1 row)

该查询接收到数据的更新以后的版本 --- 也能准确的理解不可重复读的反常，在读已提交级别是允许的。

实践观点：在一个事务里，您不能基于前一个操作读到的数据做任何决定，因为在这期间每样东西都可能发生改变。这有一个例子，应用代码中这样的变动经常出现，以至于它被当作一个经典的反-模式（反例）：

IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
 UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
END IF;

在检查和更新操作的中间时间，其它事务可以随意改变account的状态，因此这样的“检查”绝对是无用的。为了更好的理解，您可以想像其它事务的一个随意操作刚好“挤进”当前事务当中。例如，像这样：

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;

当操作重新编排时，如果所有的结果都变坏，那么代码就是错误的。不要欺骗(delude)自己，说您永远不会陷入困境：您能犯到错误的地方永远是会出错的。这样的错误很难重现，并且，最后如何去修复它实际上也是一个挑战。

您将如何改正这段代码？这里有几个选项：

• 将过程代码替换成声明式的

  例如，在这个例子里，很容易将IF语句换成CHECK约束：

  ALTER TABLE accounts
  ADD CHECK amount >= 0;
  

  现在您不必在代做检查了：如果试图违反完整性约束，只需运行该命令并处理将引发的异常就足够了。

• 使用单个SQL操作符

  如果事务刚好处在另一个事务操作符的时间间隔以内，数据一致性可以让步，这样改变了数据的可见性。如果只有一个操作符，就不存在这样的时间间隙。

  PG完全可以用单个SQL语句来解决复杂的任务。特别是，它提供了CTE（公共表表达式），可以包含像INSERT,UPDATE,DELETE，也包含INSERT ON CONFLICT操作符，来实现下边的逻辑：插入一行如果它不存在，或者执行一个更新。

• 使用显式的锁

  最后一种方法是在所有的行上手动设置排它锁（SELECT FOR UPDATE）或者甚至在整个表上加锁（LOCK TABLE）。这个方法总是能工作，但是它没能用上MVCC的优点：有些操作本来可以并发执行，现在不得不串行执行了。

Read skew. (读歪斜) 但是，事实上并不总是那么简单。PG实现允许其它的，不常见的反常，在标准里头也没有正式提及。

假定第1个事务启动了一笔Bob帐户的转帐：

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

同时，另一个事务开始遍历Bob的帐户，来计算他们的总的收入。它从第一个帐户开始（看它以前的状态）：

=> BEGIN;
=> SELECT amount FROM accounts WHERE id = 2;
amount
−−−−−−−−
100.00
(1 row)

这个时候，第一个事务成功完成：

=> UPDATE accounts SET amount = amount + 100 WHERE id = 3;
=> COMMIT;

第2个事务读第2个帐户的状态（看到已更新的状态）：

=> SELECT amount FROM accounts WHERE id = 3;
amount
−−−−−−−−−
1000.00
(1 row)
=> COMMIT;

结果，第2个事务得到了 $1100，因为它读到了错误的数据。这样的反常被称为读歪斜。

在读已提交级别下如何避免这种反常？答案是明显的：使用单个操作符。如：

SELECT sum(amount) FROM accounts WHERE client = 'bob';

到现在为止，我已经阐明了，数据可见性在操作符之间是可以发生改变的，但事实果真如此吗？如果查询要花很长时间呢？这个例子当中，在不同的状态下能看到数据的不同部分吗？

我们不妨试一下。一种简便的方式是为操作符添加一个延迟，通过调用函数pg_sleep来实现。接着第1行会立即被读，但是第2行必须要等2秒：

=> 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的函数，并且这个函数执行了另一个查询，那么被这个嵌套查询看到的数据将与主查询看到的结果不太一致。

我们使用下边的函数看下Bob的帐户总额：

=> 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的。问题是PG默认的就是这个隔离级，并且默认用的就是VOLATILE。所以我们不得不承认这个陷阱是用一种非常狡猾的(cunning)方式设置的。

读歪斜代替丢失更新.  读歪斜的反常在单个操作符更新期间偶尔也会发生 --- 即使以有些意外的方式。

我们看下，如果两个事务尝试修改相同的一行数据。Bob当前在两个帐户共有 $1000：

internals=# 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操作符的执行实际上由两阶段组成。第1阶段， 要更新的行基于提供的条件被选出来。既然第1个事务还没有提交，第2个事务也看不到它的结果，因此被选出来的用于计算利息的行不受影响。这样，Bob的帐户满足那个条件，他的存款会增加 $10，一旦UPDATE操作完成。

在第2阶段，选上的行会一个个更新。第2个事务必须等待，因为id=3的行被锁住了：它正在被第1个事务更新。

与此同时，第一个事务开始提交它的更改：

=> COMMIT;
=> SELECT * FROM accounts WHERE client = 'bob';
id | client | amount
−−−−+−−−−−−−−+−−−−−−−−−−
2 | bob | 202.0000
3 | bob | 707.0000
(2 rows)

一方面，UPDATE命令应该看不到第1个事务的任何改变。但另一方面，它不能丢失任何提交的改变。

一旦锁被释放，更新操作符会重新读取更新的行（也只有那一行！）。结果，Bob得到了 900。但是如果他只有 $900的存款，他的帐户就不应该被包含到第1个查询的结果里。

这样，我们的事务返回的是不正确的数据：不同的行都是读自不同的快照。没看到丢失更新，我们看到的是又一次反常的读歪斜。

丢失更新.  然而，重读锁定的行，这个技巧无助于不同的SQL操作符更新数据时丢失更新的处理。

这是一个我们曾经看到过的例子。应用读取和注册（数据库以外）当前Alice帐户的存款：

=> BEGIN;
=> SELECT amount FROM accounts WHERE id = 1;
amount
−−−−−−−−
800.00
(1 row)

与此同时，另一个事务做了相同的事情：

=> BEGIN;
=> SELECT amount FROM accounts WHERE id = 1; --事务2
amount
−−−−−−−−
800.00
(1 row)

第1个事务通过前一个注册的值，再加上 $100，并提交了变化：

UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1 --事务1
RETURNING amount;
 amount
--------
 900.00
(1 row)

UPDATE 1
COMMIT;
COMMIT

第2个事务做相同的事情：

internals=*# UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1
internals-*# RETURNING amount;
 amount
--------
 900.00
(1 row)

UPDATE 1
internals=*# COMMIT;
COMMIT

不幸地是，Alice已经丢失了 800在某种程度上与accounts.amount有关，因此它不能避免反常的丢失更新。在读已提交隔离级，这段代码是错误的。

可重复读

无不可重复读和幻想读 就像名字里头提示的，可重复读^[2]隔离级必须确保可重复的读。我们检查一下，确保幻像读也是不许发生的。为达到此目的，我们开启一个事务，将Bob的帐户回退到前一个状态，并为Charlie创建一个新的帐户：

internals=# BEGIN;
BEGIN
internals=*# UPDATE accounts SET amount = 200.00 WHERE id = 2;
UPDATE 1
internals=*# UPDATE accounts SET amount = 800.00 WHERE id = 3;
UPDATE 1
internals=*# INSERT INTO accounts VALUES
internals-*# (4, 'charlie', 100.00);
INSERT 0 1
internals=*# 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)

在第2个会话里，我们开始另一个事务，在BEGIN命令里显式地使用可重复读隔离级（第1个事务的级别并不太重要）：

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT * FROM accounts ORDER BY id;
internals=*# SELECT * FROM accounts ORDER BY id;
 id | client |  amount
----+--------+----------
  1 | alice  |   900.00
  2 | bob    | 202.0000
  3 | bob    | 707.0000
(3 rows)

现在第1个事务提交它的变化，第2个事务重复它的查询：

COMMIT; -- 事务1

internals=*# SELECT * FROM accounts ORDER BY id; --事务2
 id | client |  amount
----+--------+----------
  1 | alice  |   900.00
  2 | bob    | 202.0000
  3 | bob    | 707.0000
(3 rows)

第2个事务仍然看到与前一次相同的结果：没有新行也没有更新可以看到。在这个隔离级，您不用担心操作符之间会有数据在改变。

串行化失败而不是丢失更新. 正如我们看到的，如果两个事务在读已提交隔离级下更新同一行数据，它可能会造成反常的读歪斜：等待的事务不得不重读锁定的行，因此它看到的该行的状态是与别的行处于不同的时间点。

这种反常在可重复读隔离级是不被允许的，如果它发生了，事务在遇到串行化故障时只能终止。我们可以通过重复前边利息生成的例子来看看：

=> SELECT * FROM accounts WHERE client = 'bob';
id | client | amount
−−−−+−−−−−−−−+−−−−−−−−
2 | bob | 200.00
3 | bob | 800.00
(2 rows)

internals=# BEGIN;
BEGIN
internals=*# UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
UPDATE 1

事务2：

=> 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
); --它会发生挂起

事务1：

internals=*# COMMIT;
COMMIT

事务2：(出错)

ERROR:  could not serialize access due to concurrent update
internals=!# ROLLBACK;
ROLLBACK

但是数据仍然是一致的：

internals=# SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+--------
  2 | bob    | 200.00
  3 | bob    | 700.00
(2 rows)

任何并发的行更新也会造成相同的错误，即使他们影响的是不同的列。

如果我们试着基于以前存储的旧值去更新存款，也会产生这个错误：

事务1：

=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT amount FROM accounts WHERE id = 1;
amount
−−−−−−−−
900.00
(1 row)

事务2：

internals=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
internals=*# SELECT amount FROM accounts WHERE id = 1;
 amount
--------
 900.00
(1 row)

回到事务1：

internals=*# UPDATE accounts SET amount = 900.00 + 100.00 WHERE id = 1
internals-*# RETURNING amount;
 amount
---------
 1000.00
(1 row)

UPDATE 1
internals=*# COMMIT;
COMMIT

回到事务2：

internals=*# UPDATE accounts SET amount = 900.00 + 100.00 WHERE id = 1
internals-*# RETURNING amount;
ERROR:  could not serialize access due to concurrent update
internals=!# ROLLBACK;
ROLLBACK

实践的观点：如果您的应用程序使用了可重复读隔离级用于写事务，您必须准备好重试已经完成的带有串行化故障的事务。对于只读事务而言，不可能出现这样的结果。

写歪斜. 正如我们所见，PG在可重复读隔离级上的实现能避免标准中提到的所有的反常。但是并不是所有可能的反常：没有人知道到底有多少反常存在。但是，一个重要的事实可以确定：快照隔离级并不能避免两种反常，不管最终那里会出现多少种反常。

第1种反常是写歪斜(write skew)。

我们试着定义一下下边的一致性规则：某些客户的帐户只要总的存款是非负值，我们允许其中一些帐户存款为负。

第1个事务获取Bob的帐户的总的存款：

internals=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
internals=*# SELECT sum(amount) FROM accounts WHERE client = 'bob';
  sum
--------
 900.00
(1 row)

第2个事务得到同样的总和：

internals=# BEGIN ISOLATION LEVEL REPEATABLE READ; --事务2
BEGIN
internals=*# SELECT sum(amount) FROM accounts WHERE client = 'bob';
  sum
--------
 900.00
(1 row)

第1个事务公平地以为它可以从其中一个帐户里取出 $600：

internals=*# UPDATE accounts SET amount = amount - 600.00 WHERE id = 2; --事务1
UPDATE 1

第2个事务会相信相同的结论，但是它从另一个帐户里去取：

internals=*# UPDATE accounts SET amount = amount - 600.00 WHERE id = 3; --事务2
UPDATE 1
internals=*# COMMIT;
COMMIT

提交第1个事务并再次查询：

internals=*# COMMIT;
COMMIT
internals=# SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+---------
  2 | bob    | -400.00
  3 | bob    |  100.00
(2 rows)

Bob总的存款现在变成负值，尽管这两个事务分别运行都曾经是正确的。

只读事务反常.  只读事务反常是第2个也是最后一个在可重复读隔离级上允许的反常。要观察这种反常，我们需要运行3个事务：两个用于更新数据，而第3个将是只读的。

首先，我们恢复一下Bob的存款总额：

internals=# UPDATE accounts SET amount = 900.00 WHERE id = 2;
UPDATE 1
internals=# SELECT * FROM accounts WHERE client = 'bob';
 id | client | amount
----+--------+--------
  3 | bob    | 100.00
  2 | bob    | 900.00
(2 rows)

第1个事务按照Bob的总存款来计算他的利息，并将这个总和加到他的帐户上：

internals=# BEGIN ISOLATION LEVEL REPEATABLE READ; -- 事务1
BEGIN
internals=*# UPDATE accounts SET amount = amount + (
internals(*# SELECT sum(amount) FROM accounts WHERE client = 'bob'
internals(*# ) * 0.01
internals-*# WHERE id = 2;
UPDATE 1

接着第2个事务从Bob的另一个帐户上取一些钱，并提交了这个改变：

internals=# BEGIN ISOLATION LEVEL REPEATABLE READ; -- 事务2
BEGIN
internals=*# UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
UPDATE 1
internals=*# COMMIT;
COMMIT

如果第1个事务在这个时间点上提交，将不会有反常：我们可以假定第1个事务在第2个之前提交（反过来则不是 --- 第1个事务在第2个事务做任何更新之前已经看到了id=3的帐户的变化）。

但是我们想像下，在这个非常时刻，我们启动一个只读事务来查询一个不受头两个事务影响的帐户：

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

现在只有第1个事务提交了：

internals=*# COMMIT; -- 事务1
COMMIT

第3个事务在这个时间点能看到哪个状态？刚开始的时候，它可以看到第2个事务的改变（已经提交了），但是看不到第1个 （因为它还没有提交）。但正如我们已经证实的，第2个事务应该被当做它在第1个之后启动。第3个事务看到的任何状态都将是不一致的---这也准确的反映了只读事务反常的含义：