- Using Explicit Commit and Rollback

It is possible to explicitly terminate a transaction using the COMMIT(), or ROLLBACK() statements in logic. However, this can cause certain problems that the application developer must ensure are avoided.

A transaction terminated in this way does not include the update of data pending the current PROIV write cycle, and therefore does not form part of the same recoverable unit. These updates form part of the next transaction.

Many file systems/DBMSs lose read and write currency on recoverable files when a transaction is terminated. Loss of write-currency means it is no longer possible to write a record previously read with write-intent and loss of read-currency means it is no longer possible to read the 'next' record. The discussion below refers to Logical Reports but the problems apply equally to Logical Updates and LSupdates.

Loss of write-currency can be tolerated provided the application does not subsequently attempt to write an affected record.

Loss of Read-Currency

Loss of read-currency can prevent further processing of a Logical Report in circumstances that are outlined below. Given certain restrictions it is possible for an application to continue processing by re-executing an affected Logical Report with suitably modified selection criteria, thereby effectively re-establishing its read-currency.

One-time Logical Reports

One-time Logical Reports are not affected by loss of read-currency.

Sorted Logical Reports

Execution of sorted Logical Reports consists of a sorting phase followed by a processing phase. Primary file read-currency is required during the entire sorting phase and this phase must be contained within a single transaction. During the processing phase read-currency is required only on the sort results and this is not affected by terminating a transaction (sort results are not stored in recoverable files).

Non-Sorted Logical Reports

Execution of non-sorted Logical Reports requires primary file read-currency. If this is lost, PROIV cannot process the next iteration of the LR. To work around this, the LR must be re-executed for each separate transaction. Each execution of the LR must have different selection criteria in order to ensure that all the intended records are processed. Some care is needed to prevent any intended records being missed out or processed more than once and the following points should be noted:

It is not meaningful to re-establish read-currency if there was no ordering of the selected records (as may be the case with explicit SQL statements or with RealityX files). Imposing an ordering where it is not necessary may impact performance.
Using the key/ordering value(s) of the last record processed to re-establish currency will result in that record being processed more than once. It is necessary to generate the succeeding value(s) to ensure correct processing (i.e.: the closest possible following key).
When accessing the primary file using a key/ordering that may contain duplicate values beware of ending a transaction while processing a series of records with the same values. Currency can only be re-established at a particular ordering value or values and hence only at the start of a series of duplicates.
Re-execution of a LR can cause problems with control breaks if done at an inappropriate point (current-record control breaks may be missed and 'next'-record control breaks may recur). Automatic vertical totals also will not work across separate executions of a LR.

If transactions are only terminated immediately following a current-record control break on the most significant, varying key/ordering field then it should be possible to re-establish currency and process control breaks correctly.

Selection Techniques

Note that there are two ways of processing the desired records in multiple passes. The first involves initially selecting all the intended records, exiting the LS before processing all the records and re-executing it with selection criteria that select the remaining, unprocessed records as many times as necessary. The second method is to select a subset of the intended records, allow the LR to complete processing that subset and re-execute it selecting the 'next' subset (for example the 'A' records, then the 'B' records and so on). These methods might be referred to as 'remainder-selection' and 'subset-selection' respectively.

The subset-selection method is preferable when appropriate, as it is simpler to understand. In addition, DBMSs may acquire locks on all selected rows before the first selected row can be retrieved. This is true of the Oracle database for example, and means that remainder-selection may lock many more rows than subset-selection.

Paging Groups

It is not recommended that transactions be terminated explicitly during processing of paging groups. Where possible, committable paging groups re-establish read-currency automatically to display a new page full of data. This is impossible when the primary file is accessed via a duplicate key.

Comment on this topic

Topic ID: 720275