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

Question

我在 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 只看到 3 个 post_comment 记录。

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

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

PostgreSQL 和 Oracle 中的 MVCC Serializable 模型是否只提供快照隔离保证而没有 Write Skew 异常检测？

更新

我什至更改了 Tx1 以发出 UPDATE 语句，更改属于同一 post 的所有 post_comment 记录的 version 列。

这样，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 上.

Answer 1

在 1995 年的论文中，A Critique of ANSI SQL Isolation Levels , Jim Gray 和同事将 Phantom Read 描述为:

P3: r1[P]...w2[y in P]...(c1 or a1) (Phantom)

One important note is that ANSI SQL P3 only prohibits inserts (and updates, according to some interpretations) to a predicate whereas the definition of P3 above prohibits any write satisfying the predicate once the predicate has been read — the write could be an insert, update, or delete.

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

在最初的 SQL Server 2PL(两阶段锁定)实现中，为查询返回相同的结果隐含谓词锁。

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

为此，我们可以设想如下场景，我们希望对所有员工进行加薪:

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

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

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

轰! CEO 已根据旧快照做出决定，提供当前更新的薪资预算可能无法维持的加薪。

您可以在 the following post 中查看此用例的详细说明(包含大量图表) .

这是幻读还是 Write Skew ？

根据 Jim Gray and co ，这是幻读，因为写偏斜定义为:

A5B Write Skew Suppose T1 reads x and y, which are consistent with C(), and then a T2 reads x and y, writes x, and commits. Then T1 writes y. If there were a constraint between x and y, it might be violated. In terms of histories:

A5B: r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur)

在 Oracle 中，事务管理器可能会或可能不会检测到上述异常，因为它不使用谓词锁或 index range locks (next-key locks) ，比如 MySQL。

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

更新

最初，我假设可序列化性也意味着时间顺序。然而，作为very well explained by Peter Bailis ，挂钟排序或线性化仅假定为严格可序列化。

因此，我的假设是针对 Strict Serializable 系统的。但这不是 Serializable 应该提供的。 Serializable 隔离模型不保证时间，并且允许重新排序操作，只要它们等同于一些串行执行。

因此，根据Serializable的定义，如果第二个事务不发出任何读取，就会发生这种幻读。但是，在 2PL 提供的 Strict Serializable 模型中，即使第二个事务没有针对我们试图防止幻读的相同条目发出读取，幻读也会被阻止。