As I stated before I have not used Iteration Count in any
applications, but from reading the manual I'm assuming
(probably my first mistake) that it merely replaces the need
to perform a manual count and explicit commit. My earlier
comments relating to cursors and commits were based upon
personal experiences, and information proffered by Oracle
experts. Unless I have been given dud information then
performing a commit part way through the processing of a
cursor, with out then re-building that cursor, is a definite
no-no. Having said that, I cannot believe that PRO-IV would
introduce a feature that was not 'pucker'.

Can anyone from PRO-IV detail the internal workings of the
Iteration Count feature ?

Does anyone have access to independent Oracle experts who
can either confirm or refute the validity of my
information ?

By default, Oracle will not close cursor's when a commit
is issued. The cursor will remain open across transaction
commits and rollbacks. The only time a commit will cause a
cursor to close is when the Oracle parameter CLOSE_CACHED_OPEN_CURSORS
is set to True, it is set to False by default. When a cursor is
no longer being used by a session, it will go onto a LRU
list until it is flushed or it will be closed when the session
ends.

Doug

For anyone who is interested, I have been running some tests
on Iteration Count (IC). All of the testing used native
PRO-IV with no embedded SQL statements, therefore all of the
SQL statements were generated by PRO-IV.

I set up two updates which were attempting to update the
same 6 rows with an IC of 3. Process A issues a FOR UPDATE
statement, applying locks to rows 1 through 6, thereby
forcing Process B to wait. When the IC is reached the locks
on rows 1 through 6 are released, and a FOR UPDATE statement
for rows 4 and 6 is issued. Process B is now free to begin
processing rows 1 and 3. Because the SELECT...FOR UPDATE
statement is re-issued then the problems of 'FETCH OUT OF
SEQUENCE' are removed. However, at this stage I have
discovered a problem.

After launching Process A, I issued a DELETE statement for
row 5, which is not processed immediately due to the locks
applied by Process A. When the IC of 3 is reached, the lock
on row 5 is removed, and the DELETE statement is executed.
Process A is now attempting to apply locks to rows 4 and 6,
but must wait for the commit of the DELETE statement. After
the commit is issued, locks are applied by Process A, and it
continues to process row 4 through 6. When row 5 is reached,
it no longer exists, and Process A stops processing. It does
not process any rows after row 4, it does not issue a SQL
error, it does not perform a rollback, and it does not exit
through @RFUNCT.

Once the initial SELECT statement is issued the mechanics of
IC appears to use specific values for row processing, which
is fine as long as the rows are not removed. If the row is
removed then the process appears to fail, but no indication
of this failure is given.

Well spotted!

I have managed to recreate the scenario you described with
Iter Count. There is a slight difference in the way Pro-IV
has to work when Iter Count is set which I think I can
explain (long windedly).

It all comes down to Cursors and what type they are. As you
mention, a function without Iter Count will issue a 'SELECT
.. FOR UPDATE' command. This causes Oracle to obtain all the
row locks in advance of processing, effectively creating a
'Select For Update Cursor'. If an explicit commit is made
within Pro-IV logic before the end of the LU, the
'FETCH OUT OF SEQUENCE' error occurs. This is because the
cursor is no longer valid as Oracle has released the locks
and discarded it. As you say, the only way to get around
this is to exit the LS and then come back in to re-create
this cursor and start processing again.

However, when Iter Count is used, Pro-IV realises the problem
a commit will cause and gets around it by issuing a
'SELECT.. ORDER BY' statement. This creates a 'Select Cursor
' which requires no locks. When each record is fetched
from the cursor, a 'SELECT .. FOR UPDATE OF' is issued for
each individual record as it is processed in order to lock
it. Pro-IV can then issue an explicit Commit without
affecting the 'Select Cursor'. Oracle will not invalidate it
as it owns no locks and read currency will remain.

This then allows the problem scenario you mentioned with
deleting a record before it has been reached in the order
of processing. I attempted this and the function bombed to
the '?' prompt, no error or RFUNCT.

This is a serious error as it affects the data integrity of
the database. If my function updated 10 thousand out of 20
thousand records and then bombed with no error, who's to know
that it needs to be restarted or corrective action should be
taken.

Doug

G'Day Doug,

Thank-you very much for taking the time to explain the
internals. Much appreciated.

Shaun

There is also a potentially more serious problem with the
way that IC uses the list of rows from the original select
statement to continue locking and processing after each
commit.

I reconfigured my testing to select rows from a table,
update a processed flag, and stamp the function name on the
row. There are 8 rows in my table, and Processes A and B
have an IC of 4. Process A is executed, and locks all 8 rows
using a SQL statement something like SELECT FROM table
WHERE processed <> 'Y'. Process B is executed, but must wait
for the locks imposed by Process A to be removed. After
processing rows 1 through 4, Process A's IC of 4 is reached,
and a commit is performed. Checking the rows in table
reveals that Processed has been set to 'Y' and the stamp is
set to 'Process A' for rows 1 through 4; rows 5 through 8
are as yet untouched. Process B is now free to start
processing, but as per the SELECT statement, it does not
select rows 1 through 4, but locks and processes rows 5
through 8. Process A cannot continue as these rows are
currently locked. Process B reaches the IC of 4 and commits.
Checking the rows in the table reveals that Processed has
now also been set for rows 5 through 8, but the stamp is set
to 'Process B'. Because of the way that IC uses specific
rows, Process A does not know that the value of Processed
has changed for some rows, and it now locks and starts
processing rows 5 through 8. When Process A has finished,
rows 5 through 8 are now stamped with 'Process A'. This
means that rows 5 through 8 have been processed twice.

In my functions I changed the above LU into a window,
removed the IC, added a manual count, and added an explicit
commit and lsexit. I set up a manual FOR ... NEXT LOOP to
call this window. Process A is executed, and locks all 8
rows using a SQL statement something like SELECT FROM table
WHERE processed <> 'Y'. Process B is executed, but must wait
for the locks imposed by Process A to be removed. After
processing rows 1 through 4, Process A's manual count limit
of 4 is reached, and the explicit commit and lsexit is
performed. Checking the rows in table reveals that Processed
has been set to 'Y' and the stamp is set to 'Process A' for
rows 1 through 4; rows 5 through 8 are as yet untouched.
Process B is now free to start processing, but as per the
SELECT statement, it does not select rows 1 through 4, but
locks and processes rows 5 through 8. Process A cannot
continue as these rows are currently locked. Process B
reaches the manual count of 4, performs an explicit commit
and lsexit. Checking the rows in the table reveals that
Processed has now also been set for rows 5 through 8, but
the stamp is set to 'Process B'. Process A is now free to
continue but there are no more rows to process and it
terminates. Checking the table reveals that rows 1 through 8
have Processed set to 'Y', but rows 1 through 4 are stamped
with Process A and rows 5 through 8 are stamped with Process
B. This means that each row has been processed once.

See Doug Smith's post under 'Iteration Count' above for a
more technical Oracle explanation.

Hi Shaun

No problem, thanks for pointing out the problem in the
first place. I've recently been playing about with the
Iter Count to attempt to reduce the number of 'Snapshot
too old' problems. I'd never have spotted that.

I've been thinking about it a bit more and I suspect
that Pro-IV gets a reply from the cursor when it gets
to the 'deleted' record that there are no more records to
process, not realising that there are more records to be
processed after it. In my case, it then just ends the
function. This seems like different behaviour than what
you are experiencing.

Doug

Shaun

I hope I can explain what is happening here. Again it
is down to Cursors, as well as Oracle's Transaction
Isolation level.

In Oracle, when a transaction starts, it is issued a System Change
Number (SCN). This is basically the number of changes that have occurred in
the database since it was last started. Using your example,
I start Process A, it is given an SCN of 1 as it’s the first
change to the database. I have opened a cursor
and the cursor list shows 8 records in it. When you
start process B, you are given an SCN of 2. I then start to
process each record until the Iter Count kicks in, committing the
changes to records 1-4. This then allows your process to start by releasing
the locks.

Oracle's default transaction isolation level says that a process can't read
a record which hasn't been committed(a dirty read). More importantly, it
says that if changes were made to the data block after
I started my transaction, then Oracle will use
a Rollback Segment to reconstruct the data as it was when the process
began. (SCN 1). Just like a time machine…

This means that you can see the changes I have just
made to records 1-4. It has been committed and my SCN is
lower than yours. Process B bypass’s records 1 to 4 and then
updates 5-8.

You commit and set the SCN on the data block to 2.
I then read record 5 and realise that it was changed
after I opened my select cursor. The SCN for my
process is still 1. (As noted in a previous posting a
commit only affects an ‘Update Cursor’.) The data I see is then reconstructed
from the Rollback Segments so that it looks the same as
when I started, the time machine effect. So it can't see
your changes to records 5-8. It is looking at data as at
1, so it updates the records again.

When you changed your program to reconstruct the cursor after
the initial commit in Process A, you effectively gave the next
transaction SCN 3. Oracle will then allow process A to see
the changes made at SCN 2 (Process as it has been committed and
it's SCN is lower. It won’t update records 5-8 as it can
see that they have been updated.

I hope I made myself clear…….

What does this mean? Well, it means that using Iter Count
opens the function to Oracle’s isolation level failings. Using your
method to reconstruct the Cursor after every Commit is a more
robust way to prevent Data Integrity issues.

Doug

The vast majority of the application I am currently working
on was written using PRO-IV 2.2 (ie pre Iteration Count).
I therefore had to devise my 'own' method of performing
progressive commits to avoid transactions that were either
too large or that encountered the 'fetch out of sequence'
error. When we upgraded to PRO-IV 4.0, the routines were not
'broken', so I did not fix them. All new routines were
written using the existing methodolgy. As you point out, the
use of Iteration Count appears to allow for some serious
data integrity problems. Now that I have had the time and
inclination to test Iteration Count, I am pleased that I
stuck with my PL/SQL type structure.

Unless someone has any information on how to block these
holes, Iteration Count is not a feature that I will be
introducing into our application.

1. The above set of issues certainly does seem to render the Iteration Count feature unusable.
2. Having done some testing, there *does* seem to be a simple workaround which neatly avoids the 'Fetch out of sequence' error and still processes all the other records (even when someone else deletes some and commits the changes):

First, get rid of the iteration count setting.

Then, suppress PRO-IV's own commit processing using ENABLE(&#@SUPP-COMM) in default/logic in, and DISABLE it at the end of the function if appropriate

Make sure that the PRIMARY file of your update is in LOOKUP mode (if necessary, insert the primary file in lookup and then access it in change (or whatever) mode as the second file

Do a #STAT = COMMIT() in the AWNE of the last file written to every n records (keep a count in a scratch variable)

Then run it!

It seems to work, again, because of subtleties with the SELECT command issued by PRO-IV. As an aside, we have logged the issue with iteration count this morning with PRO-IV support. Can I encourage everyone else to do the same as it would be a very useful feature to have working correctly?

If anyone can pick holes in the above, please let me know as I have a major system to put commit points into in the next few days.

Cheers

Dan Shannon
GREYCELL SYSTEMS

This is basically the same solution as I proposed in my
initial Iteration Count (II) posting.

The introduction of the file in look-up mode does mean that
the 'Fetch out of sequence' error is avoided. However, any
selection criteria used in Initial Logic for retrieving a
list (cursor) of rows to be updated cannot be relied upon,
as 'for update of' is not included in the initial select
statement, and locks are not applied to those rows.
Therefore, any selection criteria used in the initial select
will also be need to be included in ARNE accompanied with a
DSEL.

I still maintain that the safest processing method is to
keep the primary file in Change mode (so that the locks are
applied), to issue an explicit commit (so that the logical
transaction does not get too large), to lsexit the LU after
the commit (to avoid 'fetch out of sequence'), and to
recall the LU so that the selection criteria and locks can
be reapplied to any altered data.

Unless anyone else knows different ?

The only problem with your solution is having to go back and restructure all your functions to do what you describe, which is v. painful indeed in a large system.

Meanwhile sticking a few files in lookup mode into an existing structure is a great deal simpler, and less likely to incur the displeasure of those holding purse strings / project plans

Money or Data Integrity ?

Without the latter, one will end up spending a lot more of
the former.

I'm not suggesting compromising on data integrity, just that it's quicker to do the thing that takes the least effort (and is usually more reliable in the long run).

And I don't really agree about locking all the records you want up front - it depends on the profile of your application. Certainly in ours, doing so would not be a good idea! Horses for courses...

Dan Shannon

If reading from a list of rows that could be changing
underneath you is not a data integrity issue, then I don't
know what is !!