Thanks for the question, Yavnica.
Asked: September 22, 2017 - 12:30 pm UTC
Last updated: September 22, 2017 - 4:12 pm UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Tom,
When we use the FOR-UPDATE clause in cursors, we lock the rows which we intend to work on.
Also, we use BULK COLLECT to process huge amount for rows to save time.
Let's considering a scenario that a cursor is fetching rows for bulk processing.
These rows are then fetched into a nested table collection for updating a third table.
When the code is in progress and sets of rows are being inserted into nested table, can the data in source table (on which the cursor is based) be changed? Will the modified data (from a random transaction) be fetched in cursor?
In case, the data does change, can we prevent the rowset of the cursor to be static.
Thanks
Yavnica
and Chris said...
When you start a query, the results are read-consistent to that point in time.
If you're using a cursor, the bulk collect fetching from it, the results are read-consistent to the time you open the cursor.
So provided you're populating your nested table from a single query/cursor, you won't see any changes made after this starts. But if this cursor has "select ... for update", once you open it no one else can change the rows anyway!
The update is a separate statement. So this can see changes made to the table you're updating between you fetching rows from the other table and running it. So if you haven't locked these rows too, this may affect your results.
Of course, if all you're doing is:
select (bulk collect) into nested tables -> forall update another table using this nested table
The obvious question is:
Why can't you do all of this in one update statement?
This avoids any read consistency issues between selecting and updating the data.
Is this answer out of date? If it is, please let us know via a Comment