18.8.1 Lock Timeout Configuration

Connection Lock Timeout

Statement Lock Timeout

SuperServer

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.

Anchor
Connection Lock Timeout Connection Lock Timeout

Connection Lock Timeout

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.

...

To set the lock timeout ensure that text similar to the following is in ORAODBC.INI:

     [Oracle ODBC Driver Common]
     LockTimeOut=2

Note 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.

...

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.