在Oracle和PostgreSQL中写偏斜异常不会回滚事务 [英] Write Skew anomaly in Oracle and PostgreSQL does not rollback transaction
问题描述
我注意到在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提交?因为这是Write Skew异常，所以我期望看到TX1会因"Serialization failure exception"或类似现象而回滚.
PostgreSQL和Oracle中的MVCC序列化模型是否仅提供快照隔离保证，而没有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可以很好地突出显示.
顺便说一句，您可以自己运行所有这些示例，因为它们位于解决方案
在1995年的论文中， the正在关注.
这是幻影阅读还是吉姆·格雷(Jim Gray)和同事，这是幻影读取，因为写入偏斜"定义为:
A5B写偏斜假设T1读取x和y，它们与 C()，然后T2读取x和y，写入x，然后提交.然后T1 写y.如果x和y之间存在约束，则可能是 违反了.就历史而言:
A5B:r1 [x] ... r2 [y] ... w1 [y] ... w2 [x] ...(出现c1和c2)
在Oracle中，事务管理器可能会或可能不会检测到上述异常，因为它不使用谓词锁或 一样，挂钟订购或仅假定严格可线性化为线性化.
因此，我的假设是针对严格的可序列化系统.但这不是Serializable应该提供的.可序列化隔离模型不能保证时间，并且只要它们等同于 some 串行执行，就可以对操作进行重新排序.
因此，根据可序列化的定义，如果第二个事务未发出任何读取，则可能会发生这种幻像读取.但是，在严格的可序列化模型(2PL提供的模型)中，即使第二笔交易未针对我们试图防止幻像读取的相同条目发出读取，也将防止幻像读取.
I noticed the following occurrence in both Oracle and PostgreSQL.
Considering we have the following database schema:
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;
With the following data:
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);
If I open two separate SQL consoles and execute the following statements:
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
As expected, the SERIALIZABLE isolation level has kept the snapshot data from the beginning of the TX1 transaction and TX1 only sees 3 post_comment records.
Because of the MVCC model in Oracle and PostgreSQL, TX2 is allowed to insert a new record and commit.
Why is TX1 allowed to commit? Because this is a Write Skew anomaly, I was expecting to see that TX1 would be rolled back with a "Serialization failure exception" or something similar.
Does the MVCC Serializable model in PostgreSQL and Oracle only offer a snapshot isolation guarantee but no Write Skew anomaly detection?
UPDATE
I even changed Tx1 to issue an UPDATE statement that changes the version column for all post_comment records belonging to the same post.
This way, Tx2 creates a new record and Tx1 is going to commit without knowing that a new record has been added that satisfied the UPDATE filtering criteria.
Actually, the only way to make it fail on PostgreSQL is if we execute the following COUNT query in Tx2, prior to inserting the phantom record:
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;
Then Tx1 is going to be rolled back with:
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.
Most likely that the write-skew anomaly prevention mechanism detected this change and rolled back the transaction.
Interesting that Oracle does not seem to be bothered by this anomaly and so Tx1 just commits successfully. Since Oracle does not prevent write-skew from happening, Tx1 commits juts fine.
By the way, you can run all these examples yourself since they are on GitHub.
In the 1995 paper, A Critique of ANSI SQL Isolation Levels, Jim Gray and co, described Phantom Read as:
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.
Therefore, a Phantom Read does not mean that you can simply return a snapshot as of the start of the currently running transaction and pretend that providing the same result for a query is going to protect you against the actual Phantom Read anomaly.
In the original SQL Server 2PL (Two-Phase Locking) implementation, returning the same result for a query implied Predicate Locks.
The MVCC (Multi-Version Concurrency Control) Snapshot Isolation (wrongly named Serializable in Oracle) does not actually prevent other transactions from inserting/deleting rows that match the same filtering criteria with a query that already executed and returned a result set in our current running transaction.
For this reason, we can imagine the following scenario in which we want to apply a raise to all employees:
- Tx1:
SELECT SUM(salary) FROM employee where company_id = 1; - Tx2:
INSERT INTO employee (id, name, company_id, salary) VALUES (100, 'John Doe', 1, 100000); - Tx1:
UPDATE employee SET salary = salary * 1.1; - Tx2:
COMMIT; - Tx1:
COMMIT:
In this scenario, the CEO runs the first transaction (Tx1), so:
- She first checks the sum of all salaries in her company.
- Meanwhile, the HR department runs the second transaction (Tx2) as they have just managed to hire John Doe and gave him a 100k $ salary.
- The CEO decides that a 10% raise is feasible taking into account the total sum of salaries, being unaware that the salary sum has raised with 100k.
- Meanwhile, the HR transaction Tx2 is committed.
- Tx1 is committed.
Boom! The CEO has taken a decision on an old snapshot, giving a raise that might not be sustained by the current updated salary budget.
You can view a detailed explanation of this use case (with lots of diagrams) in the following post.
Is this a Phantom Read or a Write Skew?
According to Jim Gray and co, this is a Phantom Read since the Write Skew is defined as:
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)
In Oracle, the Transaction Manager might or might not detect the anomaly above because it does not use predicate locks or index range locks (next-key locks), like MySQL.
PostgreSQL manages to catch this anomaly only if Bob issues a read against the employee table, otherwise, the phenomenon is not prevented.
UPDATE
Initially, I was assuming that Serializability would imply a time ordering as well. However, as very well explained by Peter Bailis, wall-clock ordering or Linearizability is only assumed for Strict Serializability.
Therefore, my assumptions were made for a Strict Serializable system. But that's not what Serializable is supposed to offer. The Serializable isolation model makes no guarantees about time, and operations are allowed to be reordered as long as they are equivalent to a some serial execution.
Therefore, according to the Serializable definition, such a Phantom Read can occur if the second transaction does not issue any read. But, in a Strict Serializable model, the one offered by 2PL, the Phantom Read would be prevented even if the second transaction does not issue a read against the same entries which we are trying to guard against phantom reads.
这篇关于在Oracle和PostgreSQL中写偏斜异常不会回滚事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
