TIMEOUT Value for a Lock Wait
The default for lock time out is 0, which is to wait indefinitely.
For example: User1 is running a report and User2 issues an INSERT statement against the same table. User2’s INSERT appears to hang and waits for a lock on the table until User1’s report completes and the transaction is committed.
If you do not want users to wait indefinitely for locks, TIMEOUT can be set to an integer value expressed in seconds. Then if a lock is not granted in that number of seconds, the statement that is waiting for the lock is aborted and either the single statement or the whole transaction is rolled back depending on the setting of ON ERROR ROLLBACK and an error is returned. This error must be trapped and handled by the application.
Set a TIMEOUT Value for a Lock Wait
To limit to thirty seconds the time that a lock request remains pending, issue the following statement:
set lockmode session where timeout = 30;
To immediately return control to the application when a lock request is made that cannot be granted, use TIMEOUT=NOWAIT. For example:
set lockmode session where timeout = nowait;
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.
Example: Timeout Program
The following program example, written in ESQL/C and using the Forms Runtime System, checks for timeout and retries the transaction.
The program assumes an interface using a form to enter the department name, manager name, and a list of employees. The program inserts a new row into the department table to reflect the new department and updates the employee table with the new department name. An ESQL error handler checks for timeout. If timeout is detected, the user is asked whether to try the operation again.
/* Global variable used by main and by error handler */
int timeout;
main()
{
int myerror();
exec sql begin declare section;
char deptname[25];
char mgrname[25];
char empname[25];
char response[2];
exec sql end declare section;
. . .
exec sql set lockmode session where timeout = 15;
exec sql set_ingres(errorhandler=myerror);
. . .
/* Assume this activate block starts a new transaction */
exec frs activate menuitem 'addemp';
exec frs begin;
while (1)
{
timeout=0;
exec frs getform empform (:deptname=dept, :mgrname=mgr);
exec sql insert into dept (dname, mgr)
values (:deptname, :mgrname);
if (!timeout)
{
exec frs unloadtable empform emptbl (:empname=name);
exec frs begin;
exec sql update emp set dept = :deptname
where ename = :empname;
if (timeout)
exec frs endloop;
/* Terminate unloadtable */
exec frs end;
}
if (!timeout)
{
exec sql commit;
break;
}
else
{
exec sql rollback;
exec frs prompt ('Timeout occurred. Try again? (Y/N)',
:response);
if (*response == 'N')
break;
}
}
exec frs end;
. . .
}
int
myerror()
{
#define TIMEOUT 4702
exec sql begin declare section;
int locerr;
exec sql end declare section;
exec sql inquire_sql (:locerr = dbmserror);
if (locerr == TIMEOUT)
timeout = 1;
}
Last modified date: 01/30/2023