When an application requests a database resource that is unavailable, it is placed on a wait queue. Database resources that cause applications to be placed on a wait queue include the following:
- Locks
The application attempts to lock a database object that has already
been locked in a conflicting mode.
- Transaction Slots
The maximum number of concurrent transactions has been reached and the application attempts to begin a transaction. Note that ALLBASE/SQL creates an implicit, brief transaction when the CONNECT statement is issued.
If the amount of time the application waits is longer than
the timeout value, an error occurs and the transaction is rolled back.
The application must check the sqlcode field of the sqlca for
timeout error 2825.
The strategy for handling timeout errors depends on the specific needs of your application and on your business procedures. When encountering a timeout error, you may want to inform the user that a timeout has occurred and then halt execution of the program. Or, you may want to prompt the user to try again, in case the database resource is
now available.
A timeout value can be changed with the following statements:
The SQLUtil SHOWDBE command displays the timeout values that have been set in the DBECon file. (Note that NONE is the default when no timeout value is specified at DBEnvironment creation time.) Remember, however, that DBECon file values can be temporarily overridden with a START DBE or START DBE NEWLOG statement. In such a case, the DBEnvironment startup parameters currently in effect are not reflected
by issuing a SHOWDBE command.
Locking and transaction management strategies should be considered when setting timeout values. Refer to the following section "Using Timeouts to Tune Performance" and to the "Programming for Performance"
chapter in the ALLBASE/SQL application programming guides for more information.
The following example illustrates how you can check for the occurrence of a timeout error:
[vellip]
CONNECT TO '../sampledb/PartsDBE'
Check the sqlcode field of the sqlca.
If sqlcode equals -2825, the CONNECT has timed out because the maximum number of transaction slots has been exceeded. Although the application has not explicitly begun a transaction, ALLBASE/SQL creates an implicit, short-lived transaction when a CONNECT is issued. Since the application has not yet executed the SET USER TIMEOUT statement, the timeout value in the DBECon file is still in effect.
To prevent the application from waiting at all for a database resource, such as the lock needed for an update, set the timeout value to zero. The timeout values of other applications are unaffected.
Timeout = 0
SET USER TIMEOUT :Timeout
BEGIN WORK
Check the sqlcode field of the sqlca.
If sqlcode equals -2825, the maximum number of transaction slots has been exceeded.
UPDATE PurchDB.Parts
SET SalesPrice = SalesPrice * 1.25
WHERE SalesPrice > 500.00
Check the sqlcode field of the sqlca.
If sqlcode equals -2825, another transaction has placed an incompatible lock on a database object which your transaction wishes to lock for update.
COMMIT WORK
[vellip]
|
