Skip to content
≡

Administrator

Developer

Tools

External Tools

DBMS LOCK

DBMS_LOCK#

The DBMS_LOCK package provides an interface managing lock and unlock which can be requested.

The following table demonstrates the procedures and functions comprised of the DBMS_LOCK package.

Procedures/Functions Description
RELEASE Unlocks the user.
REQUEST Requests the user lock.
SLEEP Makes the session to rest for a certain period of time as it is set.
SLEEP2 Makes the session to rest for a certain period of time as it is set.

DBMS_LOCK properties can be set in altibase.properties.

  • USER_LOCK_POOL_INIT_SIZE

  • USER_LOCK_REQUEST_CHECK_INTERVAL

  • USER_LOCK_REQUEST_LIMIT

  • USER_LOCK_REQUEST_TIMEOUT

For more detailed information, please refer to the General Reference.

RELEASE#

The RELEASE is a function which unlocks the user account.

Syntax#

INTEGER variable :=
  DBMS_LOCK.RELEASE(id IN INTEGER);

Parameters#

Name In/Output Data Type Description
id IN INTEGER Lock ID 0 ~ 1073741823

Result Values#

The result values are as follows.

  • 0 : Success

  • 3 : Parameter error

  • 4: Already own lock specified by id

Exception#

There is no exception in this function; however, if it fails, other values, rather than 0, are returned.

Example#

Unlocks ID which is 0.

iSQL> var v1 integer;
iSQL> v1 := dbsm_lock.release(0);

REQUEST#

The REQUEST is a function requesting the user lock.

Syntax#

INTEGER variable :=
  DBMS_LOCK.REQUEST(
    id IN INTEGER,
    lockmode IN INTEGER DEFAULT x_mode,
    timeout IN INTEGER DEFAULT MAXWAIT,
    release_on_commit IN BOOLEAN DEFAULT FALSE);

Parameters#

Name In/Output Data Type Description
id IN INTEGER Lock ID 0 ~ 1073741823
lockmode IN INTEGER This is the parameter only for compatibility. x_mode (exclusive lock) is supported.
timeout IN INTEGER This is the parameter only for compatibility. The default value is MAXWAIT.
release_on_commit IN INTEGER This is a parameter only for compatibility. The default value is FALSE.

Result Values#

The result values are as follows.

  • 0 : Success

  • 1 : Timeout

  • 3 : Parameter error

  • 4: Already own lock specified by id

Exception#

There is no exception in this function; however, if it fails, other values, rather than 0, are returned.

Example#

Requests lock on the ID which is 0.

iSQL> var v1 integer;
iSQL> v1 := dbsm_lock.request(0);

SLEEP#

The SLEEP is a procedure putting the procedure into to sleep for a specific time.

Syntax#

DBMS_LOCK.SLEEP(seconds IN INTEGER);

Parameter#

Name In/Output Data Type Description
second IN INTEGER Sleep for a specific seconds. There is no maximum.

Result Value#

Because it is a stored procedure, there is no result value.

Exception#

There is no exception.

SLEEP2#

The SLEEP is a procedure putting the procedure into to sleep for a specific time.

Syntax#

DBMS_LOCK.SLEEP2(seconds IN INTEGER, microseconds IN INTEGER);

Parameter#

Name In/Output Data Type Description
seconds IN INTEGER Sleep for a specific seconds. There is no maximum.
microseconds IN INTEGER Maximum amount of time, in microseconds, that a session is idle is 999999

Result Value#

Because it is a stored procedure, there is no return value.

Exception#

There is no exception.