The DBMS_LOCK system package provides interfaces for the lock management service of OceanBase Database.
The DBMS_LOCK system package provides the following features:
Provides mutual exclusion: Enables mutually exclusive access to a device, such as a terminal.
Lock release awareness: After an application releases or cleans up a lock, other parties must be able to detect this.
Synchronization capability: Coordinates and synchronizes different applications or processes.
Applicability
This content applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL-compatible mode.
DBMS_LOCK Privilege Description
The database system imposes a limit on the maximum total number of locks that can be available. When using locks or providing this package to other users, consider granting only the EXECUTE privilege to specific users or roles.
DBMS_LOCK constants
The following table describes the constants used by DBMS_LOCK when specifying parameter values.
Name |
Attribute Name |
Type |
Value |
OEM abbreviation |
Description |
|---|---|---|---|---|---|
| NL_MODE | Nul1 | INTEGER | 1 | None | Lock-free mode |
| SS_MODE | Sub Shared | INTEGER | 2 | ULRS | Intentional Read Lock |
| SX_MODE | Sub eXclusive/Row Exclusive Mode | INTEGER | 3 | ULRX | Intentional Write Lock |
| S_MODE | Shared/Row Exclusive Mode/Intended Exclusive | INTEGER | 4 | ULRSX | Read lock |
| SSX_MODE | Shared Sub eXclusive/Share Row Exclusive Mode | INTEGER | 5 | None | Shared Intent Write Lock |
| X_MODE | Exclusive | INTEGER | 6 | ULX | Write lock |
Rules and limitations of DBMS_LOCK
The following table describes the lock compatibility matrix. Y indicates the request is granted, and N indicates the request is denied.
Lock type |
Apply for NL lock |
Acquire an SS lock |
Acquire SX lock |
Acquire an S-lock |
Acquire an SX lock |
Acquire X-lock |
|---|---|---|---|---|---|---|
| NL | Y | Y | Y | Y | Y | Y |
| SS | Y | Y | Y | Y | Y | N |
| SX | Y | Y | Y | N | N | N |
| S | Y | Y | N | Y | N | N |
| SSX | Y | Y | N | N | N | N |
| X | Y | N | N | N | N | N |
Specify the default wait time maxwait when acquiring a lock.
maxwait constant integer := 32767;
Considerations for DBMS_LOCK
- User locks have the "UL" prefix and do not conflict with table locks.
- Locks are released after the session is disconnected.
- The value range of lock IDs is (0, 1073741823).
- User locks behave the same as table locks and have all the features of table locks, such as deadlock detection.
- Ensure that user locks used in distributed transactions are released upon transaction commit; otherwise, deadlocks may occur.
- For each session, the best practice for
DBMS_LOCKis to limit the number of locks to a few hundred.
Overview of the DBMS_LOCK Subprogram
The following table lists the DBMS_LOCK subprograms supported by the current version of OceanBase Database and provides brief descriptions.
Subprogram |
Description |
|---|---|
| ALLOCATE_UNIQUE | Apply for a unique lock ID for the lock name. The value range of the lock ID is (1073741824, 1999999999). |
| ALLOCATE_UNIQUE_AUTONOMOUS | Apply for a unique lock ID for the lock name. |
| REQUEST | Acquires a lock. |
| SLEEP | Blocks the current session for a specified duration. |