Seeing Database Changes Made Internally and Externally

This section discusses the ability of a result set to see the following:

• its own changes (DELETE, UPDATE, or INSERT operations within the result set), referred to as internal changes

• changes made from elsewhere (either from your own transaction outside the result set, or from other committed transactions), referred to as external changes

Near the end of the section is a summary table.

Note: External changes are referred to as "other's changes" in the Sun Microsystems JDBC 2.0 specification.

Seeing Internal Changes

The ability of an updatable result set to see its own changes depends on both the result set type and the kind of change (UPDATE, DELETE, or INSERT). This is discussed at various points throughout the "Updating Result Sets" section beginning on , and is summarized as follows:

• Internal DELETE operations are visible for scrollable result sets (scroll-sensitive or scroll-insensitive), but are not visible for forward-only result sets.

  After you delete a row in a scrollable result set, the preceding row becomes the new current row, and subsequent row numbers are updated accordingly.

• Internal UPDATE operations are always visible, regardless of the result set type (forward-only, scroll-sensitive, or scroll-insensitive).

• Internal INSERT operations are never visible, regardless of the result set type (neither forward-only, scroll-sensitive, nor scroll-insensitive).

An internal change being "visible" essentially means that a subsequent getXXX() call will see the data changed by a preceding updateXXX() call on the same data item.

JDBC 2.0 DatabaseMetaData objects include the following methods to verify this. Each takes a result set type as input (ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE).

• boolean ownDeletesAreVisible(int) throws SQLException

• boolean ownUpdatesAreVisible(int) throws SQLException

• boolean ownInsertsAreVisible(int) throws SQLException

  Note: When you make an internal change that causes a trigger to execute, the trigger changes are effectively external changes. However, if the trigger affects data in the row you are updating, you will see those changes for any scrollable/updatable result set, because an implicit row refetch occurs after the update.

Seeing External Changes

Only a scroll-sensitive result set can see external changes to the underlying database, and it can only see the changes from external UPDATE operations. Changes from external DELETE or INSERT operations are never visible.

Note: Any discussion of seeing changes from outside the enclosing transaction presumes the transaction itself has an isolation level setting that allows the changes to be visible.

For implementation details of scroll-sensitive result sets, including exactly how and how soon external updates become visible, see "Oracle Implementation of Scroll-Sensitive Result Sets".

JDBC 2.0 DatabaseMetaData objects include the following methods to verify this. Each takes a result set type as input (ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE).

• boolean othersDeletesAreVisible(int) throws SQLException

• boolean othersUpdatesAreVisible(int) throws SQLException

• boolean othersInsertsAreVisible(int) throws SQLException

  Note: Explicit use of the refreshRow() method, described in "Refetching Rows", is distinct from this discussion of visibility. For example, even though external updates are "invisible" to a scroll-insensitive result set, you can explicitly refetch rows in a scroll-insensitive/updatable result set and retrieve external changes that have been made. "Visibility" refers only to the fact that the scroll-insensitive/updatable result set would not see such changes automatically and implicitly.

Visibility versus Detection of External Changes

Regarding changes made to the underlying database by external sources, there are two similar but distinct concepts with respect to visibility of the changes from your local result set:

• visibility of changes

• detection of changes

A change being "visible" means that when you look at a row in the result set, you can see new data values from changes made by external sources to the corresponding row in the database.

A change being "detected", however, means that the result set is aware that this is a new value since the result set was first populated.

With Oracle8i release 8.1.6 and higher, even when an Oracle result set sees new data (as with an external UPDATE in a scroll-sensitive result set), it has no awareness that this data has changed since the result set was populated. Such changes are not "detected".

JDBC 2.0 DatabaseMetaData objects include the following methods to verify this. Each takes a result set type as input (ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE).

• boolean deletesAreDetected(int) throws SQLException

• boolean updatesAreDetected(int) throws SQLException

• boolean insertsAreDetected(int) throws SQLException

It follows, then, that result set methods specified by JDBC 2.0 to detect changes--rowDeleted(), rowUpdated(), and rowInserted()--will always return false with the 8.1.6 Oracle JDBC drivers. There is no use in calling them.

Summary of Visibility of Internal and External Changes

Table 12-1 summarizes the discussion in the preceding sections regarding whether a result set object in the Oracle JDBC implementation can see changes made internally through the result set itself, and changes made externally to the underlying database from elsewhere in your transaction or from other committed transactions.

Table 12-1 Visibility of Internal and External Changes for Oracle JDBC

Result Set Type	Can See Internal DELETE?	Can See Internal UPDATE?	Can See Internal INSERT?	Can See External DELETE?	Can See External UPDATE?	Can See External INSERT?
forward-only	no	yes	no	no	no	no
scroll-sensitive	yes	yes	no	no	yes	no
scroll-insensitive	yes	yes	no	no	no	no

For implementation details of scroll-sensitive result sets, including exactly how and how soon external updates become visible, see "Oracle Implementation of Scroll-Sensitive Result Sets".

Notes: Remember that explicit use of the refreshRow() method, described in "Refetching Rows", is distinct from the concept of "visibility" of external changes. This is discussed in "Seeing External Changes". Remember that even when external changes are "visible", as with UPDATE operations underlying a scroll-sensitive result set, they are not "detected". The result set rowDeleted(), rowUpdated(), and rowInserted() methods always return false. This is further discussed in "Visibility versus Detection of External Changes".

Oracle Implementation of Scroll-Sensitive Result Sets

The Oracle implementation of scroll-sensitive result sets involves the concept of a window, with a window size that is based on the fetch size. The window size affects how often rows are updated in the result set.

Once you establish a current row by moving to a specified row (as described in "Positioning in a Scrollable Result Set"), the window consists of the N rows in the result set starting with that row, where N is the fetch size being used by the result set (see "Fetch Size"). Note that there is no current row, and therefore no window, when a result set is first created. The default position is before the first row, which is not a valid current row.

As you move from row to row, the window remains unchanged as long as the current row stays within that window. However, once you move to a new current row outside the window, you redefine the window to be the N rows starting with the new current row.

Whenever the window is redefined, the N rows in the database corresponding to the rows in the new window are automatically refetched through an implicit call to the refreshRow() method (described in "Refetching Rows"), thereby updating the data throughout the new window.

So external updates are not instantaneously visible in a scroll-sensitive result set; they are only visible after the automatic refetches just described.

For a sample application that demonstrates the functionality of a scroll-sensitive result set, see "Scroll-Sensitive Result Set--ResultSet5.java".

Note: Because this kind of refetching is not a highly efficient or optimized methodology, there are significant performance concerns. Consider carefully before using scroll-sensitive result sets as currently implemented. There is also a significant tradeoff between sensitivity and performance. The most sensitive result set is one with a fetch size of 1, which would result in the new current row being refetched every time you move between rows. However, this would have a significant impact on the performance of your application.

---