Search code examples
oracle-databasejdbc

What is the behavior of the TYPE_FORWARD_ONLY resultset in Oracle?

For TYPE_FORWARD_ONLY the documentation here says that

TYPE_FORWARD_ONLY: The result set cannot be scrolled; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database generates the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.

How are the rows retrieved (i.e. rows that satisfy the query at either the time the query is executed or as the rows are retrieved) in case the underlying database is Oracle? I was unable to find the documentation so it would be great if someone can provide a link or explain the behavior.

For TYPE_SCROLL_INSENSITIVE the documentation says :

TYPE_SCROLL_INSENSITIVE: The result can be scrolled; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set is insensitive to changes made to the underlying data source while it is open. It contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.

Does that mean that for TYPE_FORWARD_ONLY the result set is insensitive to changes made to the underlying data source while it is open?

Solution

  • Does that mean that for TYPE_FORWARD_ONLY the result set is insensitive to changes made to the underlying data source while it is open?

    ALL queries are insensitive to changes made to the underlying data source while it is open.

    From the Data Concurrency and Consistency documentation:

    Oracle Database maintains data consistency by using a multiversion consistency model and various types of locks and transactions. In this way, the database can present a view of data to multiple concurrent users, with each view consistent to a point in time. Because different versions of data blocks can exist simultaneously, transactions can read the version of data committed at the point in time required by a query and return results that are consistent to a single point in time.

    Multiversion Read Consistency

    In Oracle Database, multiversioning is the ability to simultaneously materialize multiple versions of data. Oracle Database maintains multiversion read consistency.

    Queries of an Oracle database have the following characteristics:

    • Read-consistent queries

      The data returned by a query is committed and consistent for a single point in time.

      Note: Oracle Database never permits a dirty read, which occurs when a transaction reads uncommitted data in another transaction. To illustrate the problem with dirty reads, suppose one transaction updates a column value without committing. A second transaction reads the updated and dirty (uncommitted) value. The first session rolls back the transaction so that the column has its old value, but the second transaction proceeds using the updated value, corrupting the database. Dirty reads compromise data integrity, violate foreign keys, and ignore unique constraints.

    • Nonblocking queries

      Readers and writers of data do not block one another.

    Statement-Level Read Consistency

    Oracle Database always enforces statement-level read consistency, which guarantees that data returned by a single query is committed and consistent for 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.

    Therefore, if you start a query then ALL the data will be consistent to the state of the database when that query is run (i.e. consistent to a System Change Number [SNC]). If another session subsequently makes changes (committed or uncommitted) then they will not be seen in your ongoing cursor as statement-level read-consistency will apply (and those changes will have a later SCN).