How to Oracle RDB start a read-only PASCAL program

[Techy911]

I am new to Pascal and Oracle RDB and it seems odd that the program I will work on STARTs the dB via EXEC SQL SET TRANSACTION ON variable_handle_name USING READ ONLY; DB_OPERATION := 'Start_RO_dB';

After the successful START then a Cursor FETCH, process the data, it will COMMIT.  Why would the program COMMIT a read only program?  Please let me know what I am missing.  There are no MODIFY/INSERT/DELETE statements of any variety.  This program generates an exception report that could have one to few records output.

Thank you

[Mark_Corcoran]

The Oracle RDB SQL Reference Manual Volume 4 (RDB release 7.1.4.3, December 2005) that I have says the following:

A transaction ends with a COMMIT or ROLLBACK statement. If you end the
transaction with the COMMIT statement, all the changes made to the database
by the statements are made permanent. If you end the transaction with the
ROLLBACK statement, the statements do not take effect.

On the face of it, a COMMIT or ROLLBACK on a read only transaction would logically appear to be no different.

However, the underlying I/O operations that RDB executes for either may be different and therefore, the performance between the two may be different in general terms or depend on the size of the database table(s) that the fetch operated on (or the number of rows that it returned).

The performance difference between a COMMIT and ROLLBACK on a read transaction in (much) older versions of RDB running on (much) older VAX hardware may have been vastly different when the code was first written (but perhaps not now if using newer RDB and hardware, so the decision to use COMMIT vs ROLLBACK may now be obsolete (other than for customers of yours using old versions of RDB or old VAX hardware)).

It may also be that the original programmers worked to a coding standard that always used COMMIT, or used COMMIT when a transaction was successful but ROLLBACK when it failed (assuming that the code checks the return status of the read-only operation "that can't fail").

It's been a long time since I've looked at RDB or Ingres on OpenVMS, so I did a bit of a Google before responding.

It may be possible that there is also performance issues that vary between the COMMIT and ROLLBACK if table/record locking is being used (you have - necessarily - been circumspect in what details you have provided), and if so, whether or not the locking is on a single node or a cluster, and whether the cluster is co-located or distributed over a WAN.

The following page also has a response by a Sam Watkins on how a read-only transaction can logically fail (in a couple of cases, where the underlying database I/O was "successful"):

https://stackoverflow.com/questions/309834/should-i-commit-or-rollback-a-read-transaction

I am not incidentally, endeavouring to lecture in the fine art of egg-sucking (my RDB EXEC SQL experience is limited and 8-9 years old; My Ingres EXEC SQL experience is greater, but 20+ years old, so apologies for the fact that this is a gr(e|a)y response, rather than a black or white one).