17.8.1 Lock Timeout Configuration
Lock Timeout Types
There are two different methods of setting a timeout for when an SQL transaction is waiting for a lock to be freed by another process. They are:
1. For a Connection Lock Timeout supported on Windows and IBM i (but not on Linux) for all LANSA development databases: SQL Server, Oracle and Sybase Adaptive Server Anywhere, set a timeout on each connection so that ANY locks that occur on that connection can return control back to the application.
2. For Oracle on Linux, a different technique is required. In this environment, a wait time can be set on the SELECTs executed before LANSA performs an UPDATE or DELETE. This is called a Statement Lock Timeout. LANSA also supports this setting on Oracle for Windows so that an application can expect consistent lock timeout behavior when running on either Windows or Linux.
The Connection Lock Timeout requires setting LOCK_TIMEOUT in X_DBMENV.DAT to the time to wait before timing out. A value of zero indicates it should wait forever and that LANSA should not trap timeout errors. This is for backward compatibility. Zero is the default. The unit of measurement differs depending on the database type. This is noted in comments in X_DBMENV.DAT. For example SQL Server requires the timeout to be specified in milli-seconds and MySQL requires it to be specified in seconds.
Also set LOCK_TYPE=C (default)
For Oracle it also requires setting a value in the file ORAODBC.INI in your Windows directory. This is usually c:\windows, but the actual value can be determined by typing "set windir" in a command window.
To set the lock timeout ensure that text similar to the following is in ORAODBC.INI:
[Oracle ODBC Driver Common]LockTimeOut=2Note that for Oracle the value in X_DBMENV.DAT just enables LANSA's lock timeout behavior, it does not actually set the timeout value. Also the error code returned depends on which mewthod is chosen to implement the lock so DBMS_RETCODE_ROW_LOCKED=1013
The full set of Oracle settings in X_DBMENV.DAT is:
LOCK_TYPE=C
LOCK_TIMEOUT=2
CMD_LOCK_TIMEOUT=<setting ignored>
DBMS_RETCODE_ROW_LOCKED=1013
The Statement Lock Timeout is specific to Oracle. It allows the same lock timeout behavior to occur on Windows and Linux Oracle databases.
This also requires LOCK_TIMEOUT in X_DBMENV.DAT to be set to a non-zero value to enable the LANSA feature, but it does not actually set the value of the timeout. Also set LOCK_TYPE=S.
There is also a choice of whether to wait for the lock to be freed, or to not wait at all if a lock is encountered. This is set in CMD_LOCK_TIMEOUT: "FOR UPDATE WAIT n" or "FOR UPDATE NOWAIT" respectively.
Finally the error code (DBMS_RETCODE_ROW_LOCKED ) needs to be set to match the timeout setting as set out below.
For example, to set the lock timeout to 2 seconds use the following settings:
LOCK_TYPE=S
LOCK_TIMEOUT=2
CMD_LOCK_TIMEOUT=FOR UPDATE WAIT 2
DBMS_RETCODE_ROW_LOCKED=30006
To set the lock timeout to not wait at all use the following settings:
LOCK_TYPE=S
LOCK_TIMEOUT=2
CMD_LOCK_TIMEOUT=FOR UPDATE NOWAIT
DBMS_RETCODE_ROW_LOCKED=54
For Windows, Linux servers, the Client and Server must have the same timeout settings in x_dbmenv.dat for the database type used on the server (LOCK_TYPE, LOCK_TIMEOUT, CMD_LOCK_TIMEOUT, and DBMS_RETCODE_ROW_LOCKED). Otherwise, *DBMS_RECORD_LOCKED may return N when you expect it to return Y.