[译文] PostgreSQL ：对多行的约束

在 PostgreSQL 和许多其他关系数据库中，约束是功能集不可或缺的一部分。许多人都知道主键、外键、CHECK 约束、表约束等。然而，有时情况会变得更加复杂。那时需要一些更高级的技术来以最终用户所需的方式强制执行完整性。

在这篇文章中，我们将详细说明跨越多行的限制。这意味着什么？让我们设想一个简单的场景：我们想要存储有关飞机所有权的信息。在航空领域，一架飞机拥有多个所有者的情况经常发生。在这个例子中，我们要确保所有权部分实际上加起来是整体的 100%。类似的挑战在实际业务应用程序中反复出现。

创建示例数据

让我们首先创建一些示例数据，看看我们如何实际解决确保特定飞机的所有权始终必须为 100% 的问题：

CREATE TABLE t_plane
(
    id     int    UNIQUE,
    call_sign    text    NOT NULL UNIQUE
);
 
 
CREATE TABLE t_owner
(
    plane_id    int    REFERENCES    t_plane (id)
     INITIALLY DEFERRED,
    owner     text,
    fraction    numeric
);
 
 
INSERT INTO t_plane (id, call_sign)
VALUES     (1, 'D-EHWI'),
           (2, 'ES-TEEM'),
           (3, 'D-ONUT');
复制

在这种情况下，我们有两个表： t_plane 表包含一个唯一的 ID 和飞机的呼号。呼号类似于车牌——它识别飞机。“D”表示德国，OE 表示奥地利，“N”表示我们正在与美国注册的飞机打交道。

如您所见，我们在这里使用了 1 : N 关系。一架飞机可以有多个所有者。问题是，如果我们把飞机所有者的百分比加起来，我们总是必须得到 100%。令人讨厌的部分是并发性。如果多人同时进行更改怎么办？我们先来看看基本问题：

在这里你可以看到两个人要同时修改同一架飞机。没有什么可以阻止这两个用户实际这样做。最终结果是 D-EHWI 由两个人同时拥有——这是一个很大的禁忌。

有多种方法可以避免这种并发困境。

使用 LOCK TABLE 来防止并发

一种更常用的方法是使用表锁。让我们来看看它是如何工作的：

在这种情况下，我们使用了表锁来解决问题。这里的重点是 PostgreSQL 允许我们定义 8 种不同类型的锁：

test=# \h LOCK
Command: LOCK
Description: lock a table
Syntax:
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
 
where lockmode is one of:
 
   ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
   | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
复制

ACCESS SHARE只是意味着有人正在阅读一行。它所做的只是防止诸如此类的DROP TABLE事情发生。但是，我们这里需要做的是确保人们不能同时插入或修改。问题的解决方案是锁定级别EXCLUSIVE。读取仍然是可能的，但只有单个事务可以修改表。ACCESS EXCLUSIVE将阻止并发读取和写入，但在这种情况下这将是矫枉过正。

这种方法有什么问题？答案是可扩展性：在表锁定的情况下，只有一个事务可以同时修改同一张表。如果同时修改 100 架飞机，则必须等待 99 个事务，直到一个事务真正能够提交。有人可能会争辩说，飞机所有权不会经常改变。但是，对于其他用例，情况可能并非如此。我们需要记住可扩展性确实很重要。31核因为锁就注定闲置了，买32核的服务器就没有意义了。
需要更复杂的解决方案。

事务隔离级别：SERIALIZABLE

该问题的一种解决方案是使用更高的事务隔离级别。基本上，PostgreSQL 支持 ANSI SQL 标准提出的四分之三的事务隔离级别：

test=# \h BEGIN
Command: BEGIN
Description: start a transaction block
Syntax:
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]
 
where transaction_mode is one of:
 
    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ
         | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE
复制

当前，READ UNCOMMITTED映射到READ COMMITTED，这在 MVCC 上下文中很有意义。然而，这还不够。

SERIALIZABLE是这种情况下的首选方法。背后的基本思想是SERIAZIABLE什么？它为我们提供了顺序执行的错觉——然而，在后台，事情以尽可能多的并行性发生。

让我们仔细看看：

我们已经开始为D-ONUT插入数据。这个想法是直接插入它并在事务结束时检查以查看会发生什么。请注意，没有LOCK TABLE，没有SELECT FOR UPDATE或任何此类内容。它是隔离级别的普通事务SERIALIZABLE。它插入两个所有者，然后检查正确性。重要的部分是SERIALIZABLE实际上会出错：

ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during conflict out checking.
HINT: The transaction might succeed if retried.
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."t_plane" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
复制

PostgreSQL 注意到这里触及了相同的数据，并且该SELECT语句肯定无法返回所需的数据。如果我们没有修改数据，交易实际上会起作用。但是我们做到了，因此系统认为它无法维持顺序执行的错觉。

在这种情况下，人们经常会问几个问题：

• 为什么不简单地使用 SELECT FOR UPDATE?
• 为什么要在乎呢，因为无论如何都不太可能发生冲突？

让我们SELECT FOR UPDATE首先关注：确实SELECT FOR UPDATE确实会锁定行，并且我们不能同时修改它们 ( UPDATE, DELETE)。但是有一个问题：SELECT FOR UPDATE防止对现有行进行更改。它不会锁定“未来”行。换句话说：SELECT FOR UPDATE不会阻止其他事务插入数据，这当然会允许违反我们的约束（= 总所有权必须为 NULL 或加起来为 100%）。

经常听到的第二个论点是：飞机所有权确实不会经常变化。但我们在这里看到的是，把事情做好真的很容易，不需要太多努力。那么为什么不首先编写适当的代码来避免问题呢？处理好锁定和事务隔离很重要——不仅是为了一致性，也是为了可扩展性。这就是为什么我们建议您认真对待这个话题。