Fetching Across Commits: Where Is the Source of the Problem?

By Boris Milrud

Fetching across commits is a common and accepted practice in the Oracle programming world. However, it is not an ANSI-supported operation. According to the ANSI standard, a cursor is invalidated whenever a commit is performed, which means it should be closed and reopened. Hence, you should be aware of potential ORA-01555 errors and the fact that you are relying on a cursor behavior that is not to the ANSI standard.

Oracle always enforces statement-level read consistency. This guarantees that the data returned by a single query is consistent with respect to the time when the query began. Therefore, a query never sees the data changes made by the transactions that commit during the course of a query's execution.

As I described in my previous 10-Minute Solution on this subject, Oracle uniquely identifies any given point in time by a set of numbers, called the System Change Numbers. To ensure read consistency, Oracle marks the current SCN as the query enters the execution phase. The query can see the snapshot of the records only as they were at the time that they were marked by, or assigned to, the SCN.

This is the situation when a query opens a cursor, then loops through fetching, changing, and committing the records on the same table. The cursor requires a "snapshot" of the data at the cursor's open time for read consistency. As data blocks are read on behalf of the query and each block contains uncommitted changes of other transactions or changed data with more recent SCNs, the data is then reconstructed using the saved snapshot from the rollback segments. During a long-running query, undo entries in the rollback segment may get overwritten by another transaction, even from the same query. In this case, the Oracle server would be unable to reconstruct the snapshot, resulting in the ORA-01555 error.

Here's an example: A cursor is opened at SCN=10. The execution SCN of the query is then marked as SCN=10. Every fetch by that cursor now needs to get the read-consistent data from SCN=10. Code in the stored procedure is fetching records from the cursor, changing them, and committing them. Let's say they were committed with SCN=20. If a later fetch happens to retrieve a record that's in one of the previously committed blocks, then the fetch will see that the current block SCN is 20. Because the fetch has to get the snapshot from SCN=10 it will try to find it in the rollback segments. If it can roll sufficiently back as previously explained, then it will be able to reconstruct the snapshot from SCN=10. If not, it will return the ORA-01555 error.

Committing less often, thus creating larger rollback segments, will reduce the probability of getting this error. The only drawback to this is that the developer has to contact the DBA and make sure the rollback segments can be extended, and that the rollback tablespace can accommodate them.

读这种文章还是有收获的。让你明白一些事情。