If the use_checksum=1 option is set in the DP4 configuration file, DP4 issues "SELECT FOR UPDATE NOWAIT" statements to lock records and enable proper contention control. Up to Oracle 8i this statement seems to work reliably. In Oracle 9i spurious ORA-00054 errors are frequently generated by these calls: Oracle reports that a resource is busy when it is not. According to a company called IXORA increasing the PCTFREE parameter for the table should help. We have added an option to retry these calls when an error 54 occurs: it is frequently the case that the operation succeeds if retried after a short delay.
In order to enable the work-around you need to set two entries, either in the [sql config dbname] or the [oracle] sections of the DP4 configuration file. lock_retry_count=n specifies the number of times to retry the operation, (the default value is 0). lock_retry_delay=n specifies a number of milliseconds to delay between try attempts (the default value is 1000). Please note all programs trying to access DP4 will be delayed while these retries are taking place. Experiment indicates that a delay of 500ms with 20 retries (giving a maximum delay of 10 seconds) works well. Usually only one or two retries are required.
In Oracle, if an attempt is made to insert a record that has already been inserted but not yet committed or rolled back, the inserting process is blocked until the first process commits or does a roll back. In the event that the first insert was done by the same AUXORACL on behalf of another program, this causes AUXORACL, and indeed the whole DP4 system, to hang completely. AUXORACL has been changed to use a non blocking mode when attempting to insert a record: the insert is retried repeatedly until it either completes or a maximum number of retries has been exceeded: unfortunately Oracle does not return any information that allows a program to determine why an insert has not completed, so this change means that a insert might fail simply because the system was very heavily loaded - even in a lightly loaded system it is absolutely normal for an insert to require several retries before it completes. The delay between retries and the number of retries is configurable either in the [sql config dbname] or the [oracle] sections of the DP4 configuration file. insert_retry_count=n specifies the number of times to retry the operation, (the default value is 250). insert_retry_delay=n specifies a number of milliseconds to delay between try attempts (the default value is 20). Please note all programs trying to access DP4 will be delayed while these retries are taking place. If insert_retry_count is set to zero, the old, blocking, method of doing inserts is used. This may be necessary if the version of Oracle you use does not supporting non blocking mode.
This new version of AUXORACL also fixes a bug. The autoinc() function should not change any data in a record other than the key being incremented. However AUXORACL randomly corrupted other fields: fields that were null in the previously accessed record in the table would be set to null, and null character fields that were previously not null were set to all spaces.
Beta software is supplied without warranty and should not be used in live sites without the agreement of Itim Technology Solutions. Compatibility with an eventual final release cannot be guaranteed.