Start of change

SET CURRENT LOCK TIMEOUT statement

The SET CURRENT LOCK TIMEOUT statement assigns a value to the CURRENT LOCK TIMEOUT special register.

FL 500

Invocation for SET CURRENT LOCK TIMEOUT

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization for SET CURRENT LOCK TIMEOUT

None required.

Syntax for SET CURRENT LOCK TIMEOUT

Read syntax diagramSkip visual syntax diagramSETCURRENTLOCK TIMEOUT=WAITNOT WAITNULLWAITinteger-constantvariable

Description for SET CURRENT LOCK TIMEOUT

WAIT
Specifies a value of -1, which means that the database manager waits until a lock is released, or a deadlock is detected. When a lock cannot be obtained an error is returned.
NOT WAIT
Specifies a value of 0,which means that the database manager does not wait for locks that cannot be obtained. When a lock cannot be obtained an error is returned.
NULL
Specifies that the database manager uses the current value of the IRLMRWT subsystem parameter when waiting for a resource. The value returned for the special register is the current value of the IRLMRWT subsystem parameter, and this value changes as the value of the subsystem parameter changes. When a lock cannot be obtained an error is returned.
WAIT integer-constant
Specifies an integer value between -1 and 32767. A value of -1 is equivalent to specifying the WAIT clause without an integer value. A value of 0 is equivalent to specifying the NOT WAIT clause. When a lock cannot be obtained after the specified number of seconds, an error is returned.
variable
A variable that contains an integer value between -1 and 32767. When a lock cannot be obtained after the number of seconds specified by the variable, an error is returned.

If host-variable is specified with an associated indicator variable, and the value of that indicator variable is a null value, the CURRENT LOCK TIMEOUT value is reset to the initial value. This is equivalent to specifying the NULL keyword.

Notes for SET CURRENT LOCK TIMEOUT

Considerations for the SPREG_LOCK_TIMEOUT_MAX subsystem parameter:
The rules for assigning a value to this special register depend on the value of the SPREG_LOCK_TIMEOUT_MAX subsystem parameter, which gives the database manager a way to limit the values that can be assigned to this special register. If the value of the SPREG_LOCK_TIMEOUT_MAX subsystem parameter is not -1 (which allows for unlimited waiting), the following rules must be met:
  • An integer value must not be greater than the value of the SPREG_LOCK_TIMEOUT_MAX subsystem parameter
  • WAIT without an integer value, or WAIT -1, must not be specified
  • NULL must not be specified if the value of the SPREG_LOCK_TIMEOUT_MAX subsystem parameter is less than the value of the IRLMRWT subsystem parameter
When the new value takes effect:
An updated value of the special register takes effect immediately upon successful execution of the SET statement. Because the special register value used during statement execution is fixed at the beginning of statement execution, an updated value of the CURRENT LOCK TIMEOUT special register is in effect for statements that start execution after the SET LOCK TIMEOUT statement has completed successfully.
Syntax alternatives:
The following syntax alternatives are supported for compatibility with Db2 family products and Informix database products. These alternatives are non-standard and should not be used.
  • MODE can be specified in place of TIMEOUT
  • TO can be specified in place of the equals (=) operator

Examples for SET CURRENT LOCK TIMEOUT

Example 1:
Set the lock timeout value to wait for 30 seconds before returning an error.
SET CURRENT LOCK TIMEOUT = 30
Example 2:
Reset the lock timeout value to the initial value, so that the IRLMRWT subsystem parameter value is used when waiting for a resource.
SET CURRENT LOCK TIMEOUT = NULL
End of change