Table of Contents
This chapter briefly introduces the DBMS_LOCK package, and describes how to use the procedures of the package.
DBMS_LOCK provides lock management and SLEEP functions for users. By default, only administrators can use this package.
DBMS_LOCK uses the following defined constants.
| Name | Alias | Type | Value |
|---|---|---|---|
| NS_MODE | Null | INTEGER | 1 |
| SS_MODE | Sub shared | INTEGER | 2 |
| SX_MODE | Sub eXclusive or Row Exclusive Mode | INTEGER | 3 |
| S_MODE | Shared or Row Exclusive Mode or Intended Exclusive | INTEGER | 4 |
| SSX_MODE | Shared Sub eXclusive or Shared Row Exclusive Mode | INTEGER | 5 |
| X_MODE | Exclusive | INTEGER | 6 |
A lock request for an already occupied lock is processed according to the following table.
| Mode | NL Request | SS Request | SX Request | S Request | SSX Request | X Request |
|---|---|---|---|---|---|---|
| NL | Success | Success | Success | Success | Success | Success |
| SS | Success | Success | Success | Success | Success | Failure |
| SX | Success | Success | Success | Success | Failure | Failure |
| S | Success | Success | Failure | Success | Failure | Failure |
| SSX | Success | Success | Failure | Failure | Failure | Failure |
| X | Success | Failure | Failure | Failure | Failure | Failure |
If set to the constant MAXWAIT, the session waits forever for the lock.
maxwait constant pls_integer := 32767;
This section describes the procedures provided by the DBMS_LOCK package, in alphabetical order.
Allocates a lock ID to the specified lockname parameter. A lock ID can range from 1073741824 to 1999999999. A user can easily manage a lock using its name, instead of its number.
Details about the ALLOCATE_UNIQUE procedure are as follows:
Prototype
DBMS_LOCK.ALLOCATE_UNIQUE
(
lockname IN VARCHAR2,
lockhandle OUT VARCHAR2,
expiration_secs IN PLS_INTEGER DEFAULT 864000
)
Parameter
| Parameter | Description |
|---|---|
| lockname | Lock name to use to create a unique lockhandle value. |
| lockhandle | Handle returned for the created lock ID. REQUEST, CONVERT, and RELEASE procedures are called through this handle. To protect the lock ID from misuse, the handle value set in a VARCHAR2 (128) variable set to the handle value is returned instead of the lock ID. Since a handle is valid only within the session, it must not be used by another session. |
| expiration_secs | Allowed time to reuse the DBMS_LOCK_ALLOCATED table. (Unit: second, default value: 10 days) Time is calculated starting from when the last ALLOCATE_UNIQUE procedure is called. A lock must not be removed directly from the DBMS_LOCK_ALLOCATED table. This parameter is valid only when the _DBMS_LOCK_REUSE initialization parameter is set to Y. |
Example
DECLARE
lock_handle VARCHAR2(128);
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE('my_lock', lock_handle);
END;
/
Changes the lock mode.
Details about the CONVERT procedure are as follows:
Prototype
DBMS_LOCK.CONVERT
(
id IN INTEGER ||
lockhandle IN VARCHAR2,
lockmode IN INTEGER,
timeout IN NUMBER DEFAULT MAXWAIT
)
RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| id or lockhandle | Lock ID or handle used to change the lock mode. |
| lockmode | New lock mode. |
| timeout | Waiting time to change the lock mode. (Unit: second) A timeout error is returned if the lock cannot be obtained within this time. |
Return Value
| Value | Description |
|---|---|
| 0 | Success |
| 1 | Timeout |
| 2 | Deadlock |
| 3 | Parameter error |
| 4 | Owner error |
| 5 | Illegal lock handle |
Example
BEGIN
DBMS_LOCK.CONVERT(lock_handle, DBMS_LOCK.X_MODE, 600);
END;
/
Explicitly releases a lock. By default, a lock is automatically released when the session ends.
Details about the RELEASE procedure are as follows:
Prototype
DBMS_LOCK.RELEASE
(
id IN INTEGER ||
lockhandle IN VARCHAR2
)
RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| id or lockhandle | Lock ID or handle used to release the lock. |
Return Value
| Value | Description |
|---|---|
| 0 | Success |
| 3 | Parameter error |
| 4 | Owner error |
| 5 | Illegal lock handle |
Example
BEGIN
DBMS_LOCK.RELEASE(lock_handle);
END;
/
Requests a lock by specifying the lock mode.
Details about the REQUEST procedure are as follows:
Prototype
DBMS_LOCK.REQUEST
(
id IN INTEGER ||
lockhandle IN VARCHAR2,
lockmode IN INTEGER DEFAULT X_MODE,
timeout IN INTEGER DEFAULT MAXWAIT,
release_on_commit IN BOOLEAN DEFAULT FALSE
)
RETURN INTEGER;
Parameter
| Parameter | Description |
|---|---|
| id or lockhandle | Lock ID or handle used to request the lock. |
| lockmode | Requested lock mode. |
| timeout | Waiting time to get the lock. (Unit: second) A timeout error is returned if the lock cannot be obtained within this time. |
| release_on_commit |
|
Return Value
| Value | Description |
|---|---|
| 0 | Success |
| 1 | Timeout |
| 2 | Deadlock |
| 3 | Parameter error |
| 4 | Already owned by id or lock handle |
| 5 | Illegal lock handle |
Example
BEGIN
DBMS_LOCK.REQUEST(lock_handle, DBMS_LOCK.S_MODE, DBMS_LOCK.MAXWAIT, TRUE);
END;
/
Suspends the current session for the specified time period.
Details about the SLEEP procedure are as follows:
Prototype
DBMS_LOCK.SLEEP
(
seconds IN NUMBER
)
Parameter
| Parameter | Description |
|---|---|
| seconds | Period of time for which the session is suspended. (Unit: second) |
Example
DECLARE second NUMBER; BEGIN second := 0.11; DBMS_LOCK.SLEEP(second); END; /