本系列的内容主要翻译自Postgresql官方博客,为了便于理解,对于其中部分涉及到的知识,我在查阅相关资料的基础上做了补充。

原文: MVCC in PostgreSQL — 1. Isolation

系列文章索引

什么是隔离,为什么隔离是重要的

也许每个接触过数据库的人都知道事务的存在,都接触过ACID,也听说过隔离级别。但是也有这样的观点,这些都是理论上的东西,在实践中没有什么用。因此,我将花一些时间来讲一下为什么这个真的很重要。

如果应用程序从数据库中获取了不正确的数据或者向数据库中写入了不正确的数据,你会相当的不开心。

但是什么是“正确”的数据?众所周知,像NOT NULL和UNIQUE这样的完整性约束可以在数据库级别被创建。如果数据能够满足完整性约束(DBMS保证这一点),那么数据就是完整的。

“正确性”和”完整性”是一回事吗?不完全是,不是所有的约束都可以在数据库级别指定。有一些约束太过于复杂,例如,一次操作同时覆盖了多个表。甚至即使一个约束可以在数据库上定义,但是因为某些原因没有去定义,但并不意味着可以违反此约束。

所以说,”正确性”要大于”完整性”,但是我们仍然不知道这具体指什么东西。我们不得不承认,一个“黄金标准”是如果一个程序被正确编写了,那么它就不会出错。但对于那些违反了正确性但是没有违反完整性的程序,DBMS是无法知晓的,并且不会报错。

此外,我们用一致性(consistency )来代替完整性。

让我们来假设一个应用程序执行操作序列的顺序始终是正确的,如果应用程序正确执行了操作序列,那么DBMS做了什么工作呢。

首先,事实上正确的执行操作序列会暂时的破坏数据的一致性,奇怪的是,这是正常的。一个老套但是又清晰的例子是从一个账户向另外一个账户转账:一致性规则看起来像这样子:转账的过程不会改变账户上的资金总额(这种规则很难在SQL中来指定为一个完整性约束,因此,需要在程序中来指定这个约束,对DBMS来说则是不可见的)。一次转账分为两个部分:从一个账户上扣钱,然后在另外一个账户上加钱。第一个操作破坏了数据的一致性,第二个操作又恢复了数据的一致性。

好的实践是在完整性约束上来实现这个规则

如果第一个操作被执行了但是第二个操作没有被执行呢?事实上,不用多说:第二个操作可能会发生电力故障、服务崩溃或者除以了0等情况导致未能执行。很明显这时候一致性被破坏了,这是不被允许的。通常在应用层面解决这种问题是很麻烦的。然而,幸运的是,没有必要在应用层面解决这些问题:它是数据库的工作。为了能够做到这一点,DBMS必须知道两个操作是一个不可分割的一个整体。这就是事务了。

事情变得有趣了:DBMS知道这些操作组成了一个事务,它通过确保事务的原子性来维护一致性,并且能够在不知道具体的一致性规则下就能够做到这一点。

还有更加微妙的一点:几个互相分离的事务,它们分别运行正常,但是一起运行的话就会失败。这是因为操作的顺序被混合了:你不能假设一个事务中的所有操作先被运行,然后再去运行另一个事务的所有操作。

关于同时运行的另一点说明。事务可以在一个具有多核处理器以及多个磁盘的系统上同时运行,但是同样的在分时运行模式下的操作系统内也同样存在:例如在一个时间片内运行一个事务,然后在另一个时间周期内运行另外一个事务。有时候会用并行(concurrent )来说明这个。

当分别正常运行的事务一起运行会出异常时,被称为并行运行异常。

举一个简单的例子:如果一个应用程序希望从数据库中获取到正确的数据,它至少不能看到其它未提交的事务的数据。否则,你不仅不能得到一致性的数据,还可能看到数据库中未出现的数据(如果事务被取消了)。这种称之为脏读。

还有一些其它的更加复杂的情况,我们稍后会讲到。

当然不能禁止并发执行,否则还谈什么性能。但是又不能获取到不正确的数据。

DBMS再一次来拯救了。你可以让事务执行的就像它们是一个接着一个运行的一样,换句话说,彼此隔离。事实上,DBMS可以混合执行操作,但是可以确保并行运行的结果和顺序执行的结果是一致的。这就消除了可能出现的异常情况。

事务是运行应用程序指定的一系列操作将数据库从一个状态转移到另外一个状态(一致性consistency),前提是事务已经完成(原子性atomicity),并且不受其它事务的影响(隔离性isolation)

这个定义结合了ACID的前三个字母,它们之间的关系很密切,只讲其中一个的话是没有意义的。事实上,也很难将D(持久性durability)分离开来讲。的确,如果数据库在崩溃的时候,仍有一些未提交的修改,这时候就需要做一些事情来恢复数据的一致性。

但是实现事务的隔离是一项很难的技术,并且会降低系统的吞吐量。因此,在实践中,经常会使用弱化的隔离,这种隔离级别可以避免一些问题,但是不是所有的都可以避免。这意味着确保数据一致性的部分工作将有应用程序来负责了。因为这个原因,所以去理解数据库使用的是哪种隔离级别就非常重要了:它能够保证什么,不能够保证什么,以及在这种条件下如何能够写出正确的代码。

SQL标准中的隔离级别和异常情况

SQL标准中关于隔离的四种级别有很长的描述。这些级别定义了在事务在这种级别下同时运行时,允许哪些异常不允许哪些异常。因此,在讨论级别时,有必要去了解一下异常。

需要强调的是,我们这部分讲的是标准,就是理论,实践在很大程度上是基于这种理论的,但同时与理论也会有一定的出入。因此,这里的所有的例子都是根据理论推测的。他们在客户的账户上使用同样的操作:这个具有示范性的意义,但是与现实的银行业务所使用的方式毫无关系。

丢失更新(Lost update)

我们首先从丢失更新开始讲。当两个事务同时读取表中的同一行数据,然后一个事务更新了这一行数据,然后第二个事务也更新了同样的行,但是没有考虑到第一个事务更新的结果,就会发生这种异常。

举个例子,两个事务都想要给同一个账户增加100元。第一个事务读取了当前的余额(1000元),然后第二个事务读取了同样的值(1000元)。第一个事务将余额(1000元)增加了100然后写到了数据库中。第二个事务做了同样的操作,将1000元增加了100元然后写入到了数据库中。最后,客户损失了100元。

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

脏读(Dirty read)和未提交读( Read Uncommitted)

脏读是我们前面已经提到过的东西。当一个事务读取到另一个事务还未提交的修改时,会发生这种异常。

例如,第一个事务将所有的钱从客户的一个账户转移到另外一个账户,但是没有提交修改。另外一个事务读取账户余额,得到了余额时0元,然后拒绝让客户提取现金。这时候第一个事务被终止了撤销了修改,因此这个余额0就是数据库中从来不存在的一个值。

SQL标准中允许在未提交读级别下发生脏读。

不可重复读(Non-repeatable read)和提交读(Read Committed)

不可重复读通常发生在当一个事务读取同一行数据两次,在这两次之间,第二个事务修改并提交了这行数据,导致第一个事务在第二次读取时得到了一个不同的结果。

例如,让一致性规则来禁止客户的余额变成负数。第一个事务想要从用户的账户中扣除100元。它检查了当前的值,得到了1000,然后决定开始扣除。同时,第二个事务将余额修改为了0并提交了修改。如果第一个事务再次检查余额,它就会得到0(但是它已经决定扣除了,因此这个账户就成了负数)。

SQL规范中允许在未提交读级别和提交读级别出现不可重复读

幻读(Phantom read)和可重复读(Repeatable Read)

当一个事务根据相同的条件读取了一组数据两次,然后在两次读取之间,第二个事务增加了一些符合条件的数据。然后第一个事务第二次将会读取到和第一次读取的不同的数据集。

例如,让我们用一致性规则来禁止客户有超过三个以上的账户。第一个事务想要建立一个新账户,检查了客户当前账户的数量是2,然后决定开设新账户。同时,第二个事务同样为这个客户开设了一个新账户并且提交了修改。如果第一个事务重新检查账户的数量,它就变成了3(由于第一个事务已经决定为客户创建新账户了,此时账户的数目就变成了4)。

SQL标砖允许在未提交读、提交读和可重复读级别发生幻读。

没有异常情况和串行(Serializable)

SQL标准定义了另外一个级别-串行。这种级别不允许任何异常。这不等同于禁止丢失更新、脏读、不可重复读、幻读,而是不允许全部异常。

问题是,已知的异常情况要比标砖中列出的多得多,而且还有很多未知的异常情况。

串行级别必须能够阻止所有的异常。它意味在这个级别,应用程序的开发者完全不需要考虑并发问题。如果事务能够分别按照操作的序列正确运行,那么它们在并发运行的时候也是正常的。

总结表

最后我们提供一个表格来表明什么样的级别下允许什么样的异常。为了清晰起见,我们增加了SQL标准中没有提到的最后一列。

丢失更新 脏读 不可重读读 幻读 其它异常
未提交读 - 允许 允许 允许 允许
提交读 - - 允许 允许 允许
可重复读 - - - 允许 允许
串行 - - - - -

为什么要突出这些异常

为什么SQL标准中要在众多的异常中突出这些异常呢?

似乎没有人确切的知道这个。但是有可能的是在那个时候,实践领先于理论。其它的异常也不是随便能想到的。

此外,隔离被认为是要建立在锁上的。广泛使用的两阶段锁定协议(2PL)背后的想法是,在执行过程中,事务锁定它要处理的行,然后在完成以后释放锁。简单的说,一个事务使用的锁越多,与其它事务的隔离就越好。但是系统的性能也会受到很大的影响,因为事务不是一起工作了,而是要排队等待处理相同的行。

我的感觉是,仅仅是锁的数量,就能说明不同的隔离级别的差异

如果一个事务锁定了要修改的行,使其不能够被别的事务更新,但是可以读取,我们就到了未提交读级别。不会丢失更新,但是能够读取到未提交的数据。

如果一个事务锁定了要修改的行,使其不能够被别的事务更新和读取,我们就到了提交读级别。不能够读到未提交的数据,但是能够读到不同的值。

如果一个事务锁定了既要读取又要更新的行,使其不能够被别的事务更新和读取,我们就到了可重复读级别。每次读取返回同样的值。

但是在串行级别会存在一个问题:不能锁定一个没有存在的行。因此,幻读仍然可能存在。另外一个事务可能会增加(但是不能删除)一条符合查询条件的行,并且这行记录在重新查询到时候会被查询到。

因此,实现串行级别,通常的锁是不够的-你需要锁定条件(谓词)而不是行。这样的锁被称为谓词锁。到目前为止,这种锁还没有在任何系统中实现。

PostgreSQL中的隔离级别

随着时间的推移,事务管理中的基于锁的协议逐渐被基于快照隔离的协议替换。它的思想是每一个事务都在某个时间点一致的数据快照上工作。只有那些在创建快照前提交的变化才会进入到快照。

这种隔离避免了脏读。尽管可以在PostgreSQL中指定未提交读,但是其工作方式和提交读是一样的。因此,后面我们也不会提到未提交读这个级别。

PostgreSQL 实现了这个协议的多版本变体。多版本并发的思想是同一行的多个版本可以在DBMS中共存。这允许你使用现有的版本来建立一个数据快照,并且使用更少的锁。实际上,只有对同一行的后续修改才会被锁定。所有的其它操作都是并行的:写事务不会锁定只读事务,只读事务不会去锁定任何东西。

通过使用数据快照,PostgreSQL中的隔离级别比标准要求的更加严格。可重复读级别不仅不允许不可重复读,也不允许幻读(尽管不提供完全隔离),这是在不损失效率的情况下提供的。

丢失更新 脏读 不可重复读 幻读 其它异常
读未提交 允许 允许 允许
提交读 允许 允许 允许
可重复读 允许
串行

我们将在后面的文章中讨论多并发版本是如何实现的,现在我们以用户的眼光来观察这三个隔离级别(如你所知,最有趣的是隐藏在 “其他异常现象 “的背后)。为了做到这个,我们创建一个用户表,Alice和Bob每个人有1000元,但是Bob有两个账户。

1
2
3
4
5
6
CREATE TABLE accounts(
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
number text UNIQUE,
client text,
amount numeric
);
1
2
3
4
INSERT INTO accounts VALUES
(1, '1001', 'alice', 1000.00),
(2, '2001', 'bob', 100.00),
(3, '2002', 'bob', 900.00);

提交读

脏读

很容易证明不能读取脏数据。我们启动一个事务,默认情况下,使用的是提交读级别。

1
2
=> BEGIN;
=> SHOW transaction_isolation;
1
2
3
4
 transaction_isolation 
-----------------------
read committed
(1 row)

更确切的说,默认级别是由参数控制的,如果需要的话,可以修改它。

1
=> SHOW default_transaction_isolation;
1
2
3
4
 default_transaction_isolation 
-------------------------------
read committed
(1 row)

因此,在一个打开的事务中,我们从一个账户中扣钱,但是不提交事务。这个事务将会看到自己的变化。

1
2
=> UPDATE accounts SET amount = amount - 200 WHERE id = 1;
=> SELECT * FROM accounts WHERE client = 'alice';
1
2
3
4
 id | number | client | amount 
----+--------+--------+--------
1 | 1001 | alice | 800.00
(1 row)

在另一个会话中,我们以提交读级别启动另外一个事务。为了区分两个事务,第二个事务的命令将会增加一个| 来做标记·

在另一个会话中,运行下面的命令

1
2
|  => BEGIN;
| => SELECT * FROM accounts WHERE client = 'alice';
1
2
3
4
|   id | number | client | amount 
| ----+--------+--------+---------
| 1 | 1001 | alice | 1000.00
| (1 row)

正如期待的那样,看不到另外一个事务的修改。

不可重复读

现在我们来提交第一个事务,然后在第二个事务中重新执行相同的查询

1
=> COMMIT;
1
| => SELECT * FROM accounts WHERE client = 'alice';
1
2
3
4
|   id | number | client | amount 
| ----+--------+--------+--------
| 1 | 1001 | alice | 800.00
| (1 row)
1
|  => COMMIT;

查询获取到了不一样的值,这就是不可重复读异常,是提交读级别允许的。

结论:在一个事务中,你不能基于前一个操作所获取的数据做结论,因为数据会在两个操作之间改变。这里有一个例子,其变体经常在程序代码中出现,以至于被认为是一个典型的反模式

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

在检查和更新的间隔中,其它事务可以以任何方式来改变账户的状态,所以这样的检查就没有保证了。如下所示

1
2
3
4
5
6
7
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语句。另外也可以使用NSERT ON CONFLICT语句,实现了这样的逻辑:“插入,如果记录存在了就更新”

  • 自定义锁
    最后的办法时对所有的必要的行(SELECT FOR UPDATE) 甚至整个表(LOCK TABLE)手动设置一个独占锁,这个是有效的,但是会损失多版本并发控制带来的好处。一些操作将会被顺序执行,而不是并行执行。

不一致读取

在进行下一个隔离级别之前,必须承认,这一切不像听起来那么简单。PostgreSQL的实现是这样的,它允许其他不太为人所知的、不受标准约束的异常情况。

让我们假设第一个事务从Bob的一个账户向另外一个账户转移资金

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

同时,另外一个事务计算Bob的余额,这个计算是在Bob所有的账户上循环统计的。这个计算从Bob的第一个账户开始(很显然,看到了之前的状态)

1
2
|  => BEGIN;
| => SELECT amount FROM accounts WHERE id = 2;
1
2
3
4
|   amount 
| --------
| 100.00
| (1 row)

此时,第一个事务成功完成了

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

第二个事务开始读取Bob第二个账户上的余额(看到了新值)

1
|  => SELECT amount FROM accounts WHERE id = 3;
1
2
3
4
|   amount 
| ---------
| 1000.00
| (1 row)
1
|  => COMMIT;

因此,第二个事务得出了Bob的余额为1100,这是一个不正确的数据,即不一致读取异常。

怎么能够在提交读级别避免这种异常呢?当然,使用一个操作。例如:

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

到目前为止,我们讲到的数据可见性问题都是发生在两个操作之间。如果一个操作运行的时间很长,那么有没有可能这个操作在前半段时间内看到一个状态,在后半段时间看到另外一个状态呢?

让我们检查一下。一个简单的方法是在插入数据的时候通过调用pg_sleep函数来强制延迟运行。其参数指定了延迟的秒数。

1
=> SELECT amount, pg_sleep(2) FROM accounts WHERE client = 'bob';

在这个事务运行的时候,在另外一个事务里转移资金

1
2
3
4
|  => BEGIN;
| => UPDATE accounts SET amount = amount + 100 WHERE id = 2;
| => UPDATE accounts SET amount = amount - 100 WHERE id = 3;
| => COMMIT;

结果显示,这个事务看到的是这个事务在执行的时候的状态。这是一个正确的显示

1
2
3
4
5
 amount  | pg_sleep 
---------+----------
0.00 |
1000.00 |
(2 rows)

但是事情没那么简单。PostgreSQL 允许自定义一个函数,同时函数可以指定一个易变性的标签。如果在一个查询中调用VOLATILE 函数,并且函数中又执行了另外一个查询,那么该函数中的查询结果将和主查询中的查询结果不一致。

1
2
3
 CREATE FUNCTION get_amount(id integer) RETURNS numeric AS $$
SELECT amount FROM accounts a WHERE a.id = get_amount.id;
$$ VOLATILE LANGUAGE sql;
1
2
=> SELECT get_amount(id), pg_sleep(2)
FROM accounts WHERE client = 'bob';
1
2
3
4
|  => BEGIN;
| => UPDATE accounts SET amount = amount + 100 WHERE id = 2;
| => UPDATE accounts SET amount = amount - 100 WHERE id = 3;
| => COMMIT;

在这种情况下,我们得到了不正确的数据-丢失了100元。

1
2
3
4
5
 get_amount | pg_sleep 
------------+----------
100.00 |
800.00 |
(2 rows)

强调一下,这个情况只会在提交读级别以及VOLATILE函数同时存在时才可能发生。麻烦的是PostgreSQL默认情况下就是使用这个隔离级别和易变性标签。不要落入陷阱

补充:

PostgreSQL的函数的易变性(Volatility )标签支持三个VOLATILE和STABLE和IMMUTABLE。

VOLATILE函数里可以做查询、更新、删除等操作,每次被执行的时候,相同的参数也可以返回不同的结果(即使VOLATILE函数本身没有修改数据),VOLATILE函数每次被执行的时候,都能看到当前最新的数据快照版本。PG在执行查询的时候,如果条件是VOLATILE函数的话,索引将不起作用。

STABLE函数不能修改数据库,同时保证单个语句中所有行中如果参数相同则返回结果相同。STABLE函数在被执行的时候看到的数据快照版本和主事务看到的数据版本的一致的。PG在执行查询的时候,会将多次运行优化成一次运行,索引扫描也会起到作用。

例如我们定义了下面的STABLE函数

1
2
3
CREATE FUNCTION get_balance(name varchar) RETURNS numeric AS $$
SELECT sum(amount) FROM accounts WHERE client = name;
$$ STABLE LANGUAGE sql;

然后查询账户余额比bob总额还多的账户

1
SELECT number FROM accounts WHERE amount > get_balance('bob')

虽然accounts有三条记录,但是get_balance是STABLE函数,PG并不会在每条记录上都去重新运行get_balance,而是复用第一次执行的结果。如果get_balance是VOLATILE的话,PG就会在每条记录上都去重新运行get_balance

IMMUTABLE函数同样不能修改数据库,同时保证只要参数相同就需要返回相同的结果。和STABLE的区别是,STABLE只需要保证在一个事务中参数相同结果相同(例如,如果有一个事务修改了bob的余额,那么下一个事务中,get_balance(‘bob’)的结果就不同了),而IMMUTABLE保证的是在任何时候,即使是数据库重启,参数相同,结果也要相同。典型的例子是 1 + 1,在什么情况下,1+1都等于2。因为IMMUTABLE函数的特点,PG可以在预处理阶段直接将IMMUTABLE函数替换成结果,并且根据此结果来优化查询。

一般来说,尽量不要在IMMUTABLE函数中去查询表,一旦表的内容发生变化,函数的IMMUTABLE就被破坏。但是PG并不强制你不能这么做。

不一致读取来避免丢失更改

在更新过程中,我们也能得到不一致的数据,尽管是一种出乎意料的方式。

让我们再看看当两个事务试图修改同一行数据的时候会发生什么。现在Bob两个账户共有1000元。

1
=> SELECT * FROM accounts WHERE client = 'bob';
1
2
3
4
5
 id | number | client | amount 
----+--------+--------+--------
2 | 2001 | bob | 200.00
3 | 2002 | bob | 800.00
(2 rows)

启动一个事务去从Bob的账户中扣钱

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

同时,另外一个事务给所有那些账户总额大于1000的客户增加1%的利息

1
2
3
4
5
6
7
|  => UPDATE accounts SET amount = amount * 1.01
| WHERE client IN (
| SELECT client
| FROM accounts
| GROUP BY client
| HAVING sum(amount) >= 1000
| );

这个更新操作可以分为两部分。首先,执行SELECT语句,这个会选择出符合条件的要更新的行。因此第一个事务的更新操作还没有提交,第二个事务不能看到更改,这种变化不会影响选择的行。因此,Bob的账户是符合条件的,应该增加10元的利息。

第二个阶段的更新是一行一行执行的,因此第二个事务会被迫挂起,因此第一个事务锁定了id=3的行。第二个事务必须要等待其解锁。

然后,提交第一个事务做的修改

1
=> COMMIT;

结果会是什么呢?

1
=> SELECT * FROM accounts WHERE client = 'bob';
1
2
3
4
5
 id | number | client | amount 
----+--------+--------+----------
2 | 2001 | bob | 202.0000
3 | 2002 | bob | 707.0000
(2 rows)

好吧,一方面,UPDATE操作不应该看到第二个事务的更改,另一方面,它有不应该丢失已提交的修改(丢失更新在任何隔离级别都是不允许的)。

一旦锁释放了,UPDATE操作就会重新读取行并尝试更新。作为结果,Bob得到了9元的利息。但是Bob只有900元,他的账户不应该在选择的范围内。

所以事务得到了不正确的数据:有些行的数据在一个时间点是可见的,有些行则在另外一个时间点可见。替代丢失更新的是,我们再次得到了不一致的数据。

即使在提交读级别下,由于 应用程序的问题可能会导致丢失更新,例如下面的代码

1
2
x := (SELECT amount FROM accounts WHERE id = 1);
UPDATE accounts SET amount = x + 100 WHERE id = 1;

这不怪数据库:对数据库来说它只是分别执行两条语句,但是并不知道x+100是和账户余额有关系的。应当避免这么写代码。

可重复读

不可重复读和幻读

这个隔离级别的名字本身就告诉了我们读是可以重复的。让我们检查一下,同时确认一下这个级别也没有幻读。为了做到这个,在第一个事务中,我们将Bob的账户恢复到以前的状态,同时给Charlie创建一个新的账户

1
2
3
4
5
6
=> 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;
1
2
3
4
5
6
7
 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命令中指定重复读级别来启动一个事务。

1
2
|  => BEGIN ISOLATION LEVEL REPEATABLE READ;
| => SELECT * FROM accounts ORDER BY id;
1
2
3
4
5
6
|   id | number | client | amount 
| ----+--------+--------+----------
| 1 | 1001 | alice | 800.00
| 2 | 2001 | bob | 202.0000
| 3 | 2002 | bob | 707.0000
| (3 rows)

现在提交第一个事务的改变,然后在第二个事务中重新执行查询

1
=> COMMIT;
1
| => SELECT * FROM accounts ORDER BY id;
1
2
3
4
5
6
|   id | number | client | amount 
| ----+--------+--------+----------
| 1 | 1001 | alice | 800.00
| 2 | 2001 | bob | 202.0000
| 3 | 2002 | bob | 707.0000
| (3 rows)
1
|  => COMMIT;

从执行结果可以看出,第二个事务重新查询的结果和第一个事务提交前看到的结果相同:没有出现新行,查询出来的行也没有改变

在这个数据库级别,不需要担心两个操作之间会改变数据。

用串行化错误来避免丢失更改

我们刚才讨论了在提交读级别当两个事务同时更新同一行的时候,可能会导致不一致读异常。这是因为等待的事务会重新去读取被锁定的行,因此不同的行读取的时间点是不一致的。

在可重复读级别,这种异常是不允许的。如果它发生了,那么什么事都不应该做。因此事务将会以一个串行化错误来终止。我们来检查一下这个

1
=> SELECT * FROM accounts WHERE client = 'bob';
1
2
3
4
5
 id | number | client | amount 
----+--------+--------+--------
2 | 2001 | bob | 200.00
3 | 2002 | bob | 800.00
(2 rows)
1
2
=> BEGIN;
=> UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
1
2
3
4
5
6
7
8
|  => 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
=> COMMIT;
1
|  ERROR: could not serialize access due to concurrent update
1
|  => ROLLBACK;

数据保持了一致性

1
=> SELECT * FROM accounts WHERE client = 'bob';
1
2
3
4
5
 id | number | client | amount 
----+--------+--------+--------
2 | 2001 | bob | 200.00
3 | 2002 | bob | 700.00
(2 rows)

只要要读取的行发生了竞争条件,即使我们关注的列没有发生变化,也会发生这样的错误。

有用的结论:如果你的应用程序对写事务应用了重复读级别,那么就必须在串行化错误异常之后重新运行事务。如果事只读事务,则没有必要做这个。

不一致写(写偏移)

因此,在PostgreSQL中,在重复读隔离级别,可以避免所有的标准中的异常。但是不是所有的异常都是普通的异常,事实证明至少有两种异常仍有可能发生。(不仅仅是针对PostgreSQL,而是所有通过快照隔离实现的数据库都有这种情况)

第一种异常是不一致写

我们先制定下面一条规则:如果用户的所有账户的余额加在一块是正的,那么允许用户的某些账户余额是负值。

第一个事务得到Bob的账户是900。

1
2
=> BEGIN ISOLATION LEVEL REPEATABLE READ;
=> SELECT sum(amount) FROM accounts WHERE client = 'bob';
1
2
3
4
  sum 
--------
900.00
(1 row)

第二个事务得到了同样的金额

1
2
|  => BEGIN ISOLATION LEVEL REPEATABLE READ;
| => SELECT sum(amount) FROM accounts WHERE client = 'bob';
1
2
3
4
|    sum 
| --------
| 900.00
| (1 row)

第一个事务认为其中的一个账户的余额可以扣除600

1
=> UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;

第二个事务做了同样的决定。但是它扣除的是另外一个账户

1
2
|  => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
| => COMMIT;
1
2
=> COMMIT;
=> SELECT * FROM accounts WHERE client = 'bob';
1
2
3
4
5
 id | number | client | amount 
----+--------+--------+---------
2 | 2001 | bob | -400.00
3 | 2002 | bob | 100.00
(2 rows)

Bob账户的总额变成了负数,而每个事务也分别都运行正常了。

只读事务异常

这是第二个也是最后一个在重复读级别可能出现的异常。为了能够得到这种异常,需要三个事务,其中两个事务修改数据,第三个事务读取数据。

我们首先恢复Bob账户的状态:

1
2
=> UPDATE accounts SET amount = 900.00 WHERE id = 2;
=> SELECT * FROM accounts WHERE client = 'bob';
1
2
3
4
5
 id | number | client | amount
----+--------+--------+--------
3 | 2002 | bob | 100.00
2 | 2001 | bob | 900.00
(2 rows)

在第一个事务中,Bob所有的账户都会产生利息,然后利息会被计入到其中一个账户中。

1
2
3
4
5
=> 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的账户中取钱,然后提交修改

1
2
3
|  => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 2
| => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
| => COMMIT;

如果第一个事务这时候提交,不会发生异常。我们可以假设是第一个事务先执行,第二个事务后执行。但是反过来也是可以的,因此第一个事务在第二个事务改变数据之前已经看到了id=3的账户的状态。

想象一下如果这时候第三个事务开始了,并且其读取的是没有被另外两个事务影响到的数据。

1
2
|  => BEGIN ISOLATION LEVEL REPEATABLE READ; -- 3
| => SELECT * FROM accounts WHERE client = 'alice';
1
2
3
4
|   id | number | client | amount 
| ----+--------+--------+--------
| 1 | 1001 | alice | 800.00
| (1 row)

然后提交第一个事务

1
=> COMMIT;

这时候第三个事务看到什么状态呢?

1
|    SELECT * FROM accounts WHERE client = ‘bob’;

执行以后,第三个事务能够看到第二个事务的修改,但是看不到第一个事务的修改。在另一方面,我们也知道,第二个事务时在第一个事务开始以后开始的。无论第三个事务看到的状态是什么,都是一种不一致的状态。但是在可重复读级别,这是一个允许的异常。

1
2
3
4
5
|    id | number | client | amount
| ----+--------+--------+--------
| 2 | 2001 | bob | 900.00
| 3 | 2002 | bob | 0.00
| (2 rows)
1
|   => COMMIT;

串行化

在串行级别可以阻止所有的异常。事实上,串行是建立在快照隔离级别之上的。在重复读级别不会发生的异常在这个级别也都不会发生,一旦出现不一致写和只读事务异常,事务就会被终止-抛出一个熟悉的串行化异常。

不一致写(写偏移)

为了说明这一点,让我们重复上面的场景来展示不一致写异常

1
2
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
=> SELECT sum(amount) FROM accounts WHERE client = 'bob';
1
2
3
4
   sum 
----------
910.0000
(1 row)
1
2
|   => BEGIN ISOLATION LEVEL SERIALIZABLE;
| => SELECT sum(amount) FROM accounts WHERE client = 'bob';
1
2
3
4
|      sum 
| ----------
| 910.0000
| (1 row)
1
=> UPDATE accounts SET amount = amount - 600.00 WHERE id = 2;
1
2
|   => UPDATE accounts SET amount = amount - 600.00 WHERE id = 3;
| => COMMIT;
1
=> COMMIT;
1
2
3
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的实现是允许假阴性的:一些正常的事务会因为仅仅是“不幸运”而被终止掉。我们稍后会看到,这个取决于很多因素,例如索引的可用性和内存的可用性。此外,还有一些其它的限制,例如,不能在数据库的副本上运行串行级别的查询。尽管现在在改进这种实现,但是目前的限制还是让人会较少的考虑使用这种隔离级别。

只读事务异常

为了让只读事务不会导致异常,也不会受到异常的影响,postgreSQL提供了一种有趣的技术:事务可以被锁定到直到执行是安全的为止。这也是SELECT可以被行更新锁定的唯一情况。其看起来是下面这样子:

1
2
3
=> 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;
1
2
3
4
5
 id | number | client | amount 
----+--------+--------+--------
2 | 2001 | bob | 900.00
3 | 2002 | bob | 100.00
(2 rows)
1
2
3
4
5
=> BEGIN ISOLATION LEVEL SERIALIZABLE; -- 1
=> UPDATE accounts SET amount = amount + (
SELECT sum(amount) FROM accounts WHERE client = 'bob'
) * 0.01
WHERE id = 2;
1
2
3
|  => BEGIN ISOLATION LEVEL SERIALIZABLE; -- 2
| => UPDATE accounts SET amount = amount - 100.00 WHERE id = 3;
| => COMMIT;

第三个事务被明确的指定为READ ONLY和 DEFERRABLE

1
2
|   => BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE; -- 3
| => SELECT * FROM accounts WHERE client = 'alice';

当执行这个查询的时候,事务会被锁住,因为其它事务可能会造成一个异常

1
=> COMMIT;

只有在第一个事务被提交以后,第三个事务才会继续执行

1
2
3
4
|    id | number | client | amount
| ----+--------+--------+--------
| 1 | 1001 | alice | 800.00
| (1 row)
1
|   => SELECT * FROM accounts WHERE client = 'bob';
1
2
3
4
5
|    id | number | client | amount 
| ----+--------+--------+----------
| 2 | 2001 | bob | 910.0000
| 3 | 2002 | bob | 0.00
| (2 rows)
1
|   => COMMIT;

另外一个重要的点是:如果使用串行的隔离级别,那么这个应用的所有事务都必须使用串行级别。不能管将提交读(或者重复读)和串行混用。意思是,你可以混用,但是串行的行为将变得和重复读一样。当我们讨论到实现的时候,会讲到为什么会这样。

因此,如果你使用串行的话,最后将全局的默认级别设置成串行级别(当然,这个不会影响你手动指定的隔离级别)。

1
ALTER SYSTEM SET default_transaction_isolation = 'serializable';

我们应该使用什么隔离级别

提交读级别是PostgreSQL默认使用的隔离级别,大多数应用使用的也是这种级别。这个默认值很方便,在这个级别上,只有在失败的情况下才会终止事务,而不能防止数据不一致。换句话说,不会发生串行错误。

硬币的另一面就是会出现前面讨论的很多可能的异常。软件工程师必须了解到这些,并且在写代码的时候不要允许出现这种情况。如果不能在一条SQL语句中解决问题,就必须指定锁来解决。最麻烦的是,在测试阶段很难发现与数据不一致有关的错误,这个错误很难复制也很难预测,因此修复起来很麻烦。

可重复读级别消除了一些不一致的问题,但是不是全部。因此,你需要记住其余的异常情况,并修改应用程序使其能正确的处理序列化错误。这虽然不方便,但是对于只读事务来说,这个隔离级别完美的互补了“已提交”,并且给是非常方便的:例如,当需要执行多条SQL语句来做报表的时候。

最后,在串行级别下,可以完全不用担心不一致的问题,这就很方便编写程序。对代码的唯一要求就是在遇到串行异常的时候,能够重新运行事务。但是终止事务会导致额外的开销,同时由于无法并行查询,还会大大降低系统的吞吐量。另外要注意的是,串行级别不能在数据库副本上使用,同时也不能与其它隔离级别混用。