Oracle和PostgreSQL中的Write Skew异常不回滚事务

我注意到 Oracle 和 PostgreSQL 中都发生了以下情况。

考虑到我们有以下数据库架构：

create table post (
    id int8 not null, 
    title varchar(255), 
    version int4 not null, 
    primary key (id));    

create table post_comment (
    id int8 not null, 
    review varchar(255), 
    version int4 not null, 
    post_id int8, 
    primary key (id));

alter table post_comment 
    add constraint FKna4y825fdc5hw8aow65ijexm0 
    foreign key (post_id) references post;  

具有以下数据：

insert into post (title, version, id) values ('Transactions', 0, 1);
insert into post_comment (post_id, review, version, id) 
    values (1, 'Post comment 1', 459, 0);
insert into post_comment (post_id, review, version, id) 
    values (1, 'Post comment 2', 537, 1);
insert into post_comment (post_id, review, version, id) 
    values (1, 'Post comment 3', 689, 2); 

如果我打开两个单独的 SQL 控制台并执行以下语句：

TX1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

TX2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

TX1: SELECT COUNT(*) FROM post_comment where post_id = 1;

TX1: > 3

TX1: UPDATE post_comment SET version = 100 WHERE post_id = 1;

TX2: INSERT INTO post_comment (post_id, review, version, id) VALUES (1, 'Phantom', 0, 1000);

TX2: COMMIT;

TX1: SELECT COUNT(*) FROM post_comment where post_id = 1;

TX1: > 3

TX1: COMMIT;

TX3: SELECT * from post_comment;

     > 0;"Post comment 0";100;1
       1;"Post comment 1";100;1
       2;"Post comment 2";100;1
       1000;"Phantom";0;1

正如预期的那样，SERIALIZABLE隔离级别保留了 TX1 事务开始时的快照数据，TX1 只看到 3post_comment记录。

由于Oracle和PostgreSQL中的MVCC模型，TX2被允许插入新记录并提交。

为什么允许 TX1 提交？因为这是写入倾斜异常，所以我期望看到 TX1 会因“序列化失败异常”或类似的情况而回滚。

PostgreSQL 和 Oracle 中的 MVCC Serialized 模型是否仅提供快照隔离保证，但没有 Write Skew 异常检测？

UPDATE

我什至更改了 Tx1 以发出更新语句来更改version所有人的专栏post_comment属于同一记录post.

这样，Tx2 创建一条新记录，并且 Tx1 将在不知道已添加满足 UPDATE 过滤条件的新记录的情况下提交。

实际上，使其在 PostgreSQL 上失败的唯一方法是在插入虚拟记录之前在 Tx2 中执行以下 COUNT 查询：

Tx2: SELECT COUNT(*) FROM post_comment where post_id = 1 and version = 0

TX2: INSERT INTO post_comment (post_id, review, version, id) VALUES (1, 'Phantom', 0, 1000);

TX2: COMMIT;

然后 Tx1 将被回滚：

org.postgresql.util.PSQLException: 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.

最有可能的是写入倾斜异常预防机制检测到此更改并回滚事务。

有趣的是，Oracle 似乎并没有受到这种异常的困扰，因此 Tx1 只是成功提交。由于 Oracle 无法防止写入偏差的发生，因此 Tx1 提交得很好。

顺便说一句，您可以自己运行所有这些示例，因为它们已打开GitHub https://github.com/vladmihalcea/high-performance-java-persistence/tree/master/core/src/test/java/com/vladmihalcea/book/hpjp/jdbc/transaction.

在 1995 年的论文中，对 ANSI SQL 隔离级别的批评 https://www.microsoft.com/en-us/research/publication/a-critique-of-ansi-sql-isolation-levels/Jim Gray 及其同事将 Phantom Read 描述为：

P3: r1[P]...w2[y in P]...(c1 或 a1)（幻影）

一个重要的注意事项是 ANSI SQL P3 仅禁止插入（并且 更新（根据某些解释）到谓词，而 上面 P3 的定义禁止任何满足谓词的写入 一旦谓词被读取——写入可能是插入， 更新，或者删除。

因此，幻读并不意味着您可以简单地返回当前运行事务开始时的快照，并假装为查询提供相同的结果将保护您免受实际幻读异常的影响。

在原始 SQL Server 2PL（两阶段锁定）实现中，为查询返回相同的结果隐含谓词锁。

MVCC（多版本并发控制）快照隔离（在 Oracle 中被错误地称为可序列化）实际上并不能阻止其他事务插入/删除与我们当前运行中已执行并返回结果集的查询匹配相同过滤条件的行交易。

因此，我们可以想象以下场景，我们希望向所有员工加薪：

1. Tx1: SELECT SUM(salary) FROM employee where company_id = 1;
2. Tx2: INSERT INTO employee (id, name, company_id, salary) VALUES (100, 'John Doe', 1, 100000);
3. Tx1: UPDATE employee SET salary = salary * 1.1;
4. Tx2: COMMIT;
5. Tx1: COMMIT:

在这种情况下，CEO 运行第一笔交易 (Tx1)，因此：

1. 她首先检查了公司所有工资的总和。
2. 与此同时，人力资源部门运行第二笔交易 (Tx2)，因​​为他们刚刚成功雇用了 John Doe 并给了他 10 万美元的薪水。
3. 考虑到工资总额，CEO 认为加薪 10% 是可行的，但他不知道工资总额已经增加了 100k。
4. 同时，HR 事务 Tx2 被提交。
5. Tx1 已提交。

繁荣！首席执行官已对旧快照做出决定，当前更新的薪资预算可能无法维持加薪。

您可以在以下位置查看此用例的详细说明（带有大量图表）以下帖子 https://vladmihalcea.com/write-skew-2pl-mvcc/.

这是幻读还是?

根据吉姆·格雷等人 https://www.microsoft.com/en-us/research/publication/a-critique-of-ansi-sql-isolation-levels/，这是幻读，因为写入偏差定义为：

A5B Write Skew 假设T1读取x和y，它们与 C()，然后 T2 读取 x 和 y，写入 x，然后提交。然后T1 y 写道。如果 x 和 y 之间存在约束，则可能是 违反了。从历史来看：

A5B: r1[x]...r2[y]...w1[y]...w2[x]...（出现 c1 和 c2）

在 Oracle 中，事务管理器可能会也可能不会检测到上述异常，因为它不使用谓词锁或索引范围锁（下一个键锁） https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-next-key-locks，如 MySQL。

仅当 Bob 对员工表发出读取操作时，PostgreSQL 才能捕获此异常，否则无法阻止该现象。

UPDATE

最初，我假设可串行性也意味着时间排序。然而，作为Peter Bailis 解释得很好 http://www.bailis.org/blog/linearizability-versus-serializability/、挂钟排序或线性化仅假设为严格串行化。

因此，我的假设是针对严格可序列化系统的。但这不是 Serialized 应该提供的。 Serialized 隔离模型不保证时间，并且只要操作相当于一个操作，就允许重新排序。some串行执行。

因此，根据可序列化的定义，如果第二个事务不发出任何读取，则可能会发生这种幻读。但是，在严格可串行化模型（2PL 提供的模型）中，即使第二个事务没有针对我们试图防止幻读的相同条目发出读取，幻读也会被阻止。