sql - 我需要什么隔离级别来保持这些不变量？

Question

我有两个表，TabelA 和 TabelB。两者都有一个作为主键的 ID 列。此外，TabelB 有一个 A_ID 列。其他列与此处无关。

create table TableA (
    ID serial primary key,
    ...,
)
create table TableB (
    ID serial primary key,
    A_ID integer not null references TableA(ID),
    ...,
)

对于 TabelA 中的每一行，TableB 中可以有许多行使用 A_ID 列引用它。这意味着，TableA 和 TableB 中的行具有父子关系。我希望能够在事务中替换 TableA 中的一行及其在 TableB 中的所有关联行(即整个“系列”)。

用 ID=old_id 替换行的 SQL 如下所示:

delete from TableA where ID=old_id;
delete from TableB where A_ID=old_id;
insert into TableA values (default, ...);
-- get the new id somehow
insert into TableB values (default, new_id, ...), (default, new_id, ...), ...;

(省略号代表其余的列。)

我需要设置哪个隔离级别来保证如果执行此代码的两个事务重叠，则在它们完成后以下内容成立:

1. TableA 中没有 ID=old_id 的行。
2. TableB 中的所有行都有一个 A_ID，它也出现在 TableA 的 ID 列中(特别是，TableB 中没有带 A_ID=old_id 的行)。
3. TableA 中只有一个事务恰好插入了一行。
4. 只有一个事务将任何行插入到 TableB 中，并且所有事务都具有 A_ID=new_id，其中 new_id 是 ID 中插入的行的 3.

我也乐于接受更改代码的建议。

Answer 1

即使对于您的第一个要求，您也需要一种可序列化隔离形式，因为任何其他任何东西都可以随时出现行。如果您删除所有现有行并且下一毫秒有人插入新行，则可能会出现异常。

参见“Phantom Reads”。请注意，Postgres 中的 <9.1“SERIALIZABLE”级别不提供可序列化性，正如文档明确指出的那样。