Guidelines for Timeout Handling
If you embed a SET LOCKMODE WITH TIMEOUT in an application, timeout must be carefully handled by the application. There are two cases, depending on whether cursors are used in the embedded application:
• No cursors--if a timeout occurs while processing a statement in a multiple query transaction, only the statement that timed out is rolled back. The entire transaction is not rolled back unless the user specifies rollback in the SET SESSION WITH ON_ERROR=ROLLBACK statement. For this reason, the application must be able to trap the error, and either re-issue the failed statement, or roll back the entire transaction and retry it starting with the first query. For more information on the SET SESSION statement, see the SQL Reference Guide.
• Cursors open--if one or more cursors are open when timeout occurs during a multiple query transaction, the entire transaction is rolled back and all cursors are closed.
We recommend that the timeout error handler check on the transaction status so it can tell which case was used. This can be done with an INQUIRE_SQL statement that retrieves the transaction state. For example, in the following statement xstat has a value of 1 if the transaction is still open:
exec sql inquire_sql (:xstat = transaction);
For a detailed description of the INQUIRE_SQL statement, see the SQL Reference Guide.