I learned from the documentation of 11g that “Oracle Database always enforces statement-level read consistency”, which I think should mean that every query in a single statement (including subqueries, excluding queries in PL/SQL functions) is consistent to a single point in time (in the read committed isolation level, this point is the time at which the statement was opened).
But I also read from an older documentation that noted “Transactions containing DML statements with subqueries should use serializable isolation to guarantee consistent read” (I didn’t find it in recent versions of documentations). https://docs.oracle.com/cd/B10501_01/server.920/a96524/c21cnsis.htm

Here’s what I tried in 11g to test out subquery consistency in an update restart:
I created the table below with a before update trigger for each row that prints out a message, so that I can know how many time it’s fired.

create table T
(
    ID  NUMBER(1),
    NUM NUMBER(1)
)
/
create trigger TEST_TRIGGER
    before update
    on T
    for each row
begin
    dbms_output.put_line('fired');
end;
/
INSERT INTO T (ID, NUM) VALUES (1, 1);
INSERT INTO T (ID, NUM) VALUES (2, 2);

Example 1:

Session 1: update t set NUM = 3 where 1 = 1
Session 2: set serveroutput on
Session 2: update t set NUM = (select NUM + 3 from t where ID = 1) where NUM > 1; (blocked by session 1)
Session 1: COMMIT
Session 2: The trigger fired 3 times which means there’s a restart, and two rows were updated.
Session 2: COMMIT

The NUM columns are all set to 4 in the end, which means the subquery didn’t rerun when the update restart happened. The subquery still reads the NUM of row 1 as 1 as of the start of the statement, while the implicit query of the update reads it as 3 as of the restart, which creates inconsistency.

Example 2:

Session 1: update t set NUM = 3 where 1 = 1;
Session 2: set serveroutput on
Session 2: update t a set NUM = (select b.NUM + 3 from t b where b.ID = a.ID) where NUM > 1; (blocked by session 1)
Session 1: COMMIT
Session 2: The trigger fired 3 times which means there’s a restart, and two rows were updated.
Session 2: COMMIT

The NUM columns are all set to 6 in the end, which means both the subquery and the implicit query read data as of the restart.


So here’s my guess:
1.If there’s no update restart, queries within a single statement are guaranteed to be consistent as of the start of the statement.
2.If there is an update restart, correlated subquery will rerun since it’s executed per row, like the implicit query, it’s also consistent as of the restart.
3.If there is an update restart, non-correlated subquery won’t rerun since it’s executed per query, which makes inconsistency possible.

Am I understanding it correctly? Please clarify!