I'm new to Oracle and I just want to confirm my understanding of Statement-Level Read Consistency for Oracle 10g and 10g+.
The Oracle 10g document states:
Oracle always enforces statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time—the time that the query began. Therefore, a query never sees dirty data or any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.
Oracle 11g document states:
Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent with respect to a single point in time. The point in time to which a single SQL statement is consistent depends on the transaction isolation level and the nature of the query:
In the read committed isolation level, this point is the time at which the statement was opened. For example, if a SELECT statement opens at SCN 1000, then this statement is consistent to SCN 1000.
In a serializable or read-only transaction this point is the time the transaction began. For example, if a transaction begins at SCN 1000, and if multiple SELECT statements occur in this transaction, then each statement is consistent to SCN 1000.
In a Flashback Query operation (SELECT … AS OF), the SELECT statement explicitly specifies the point in time. For example, you can query a table as it appeared last Thursday at 2 p.m.
See the bold text. If I understand correctly, Oracle changed its behavior between 10g and 10g+. In 10g, Statement-Level Read Consistency is always based on the view when the statement starts. In 10g+, Statement-Level Read Consistency is based on a view that depends on the isolation level and the query itself. Is my understanding correct? Thank you.
Best Answer
The behaviour did not change. It has been the same since Oracle 7 at least (probably before, but that was before my time) and still is today with Oracle 19. The documentation was just expanded to include the behaviour at isolation levels other than the default READ COMMITTED, or when using the Flashback Query mechanism.
(You should really be looking at the latest documentation: https://docs.oracle.com/en/database/oracle/oracle-database/index.html - 11g is very old)
The important point is that the default isolation used by Oracle (statement-level consistency) implies that repeating the same statement may not deliver the same result since other transactions may have updated the data during the first execution of the statement. Oracle uses multi-version consistency to provide concurrent access such that readers don't block writers and writers don't block readers.