The DBMS_LOCK package provides APIs for managing locks in OceanBase Database.
The DBMS_LOCK package provides the following functionality:
Supports mutual exclusion: provides exclusive access to a device, such as a terminal.
Be able to detect released locks: After an application releases or clears a lock, other applications should be able to detect it.
Synchronization: Coordinate and synchronize between applications or workflows.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
DBMS_LOCK privilege requirements
The database system has a limit on the total number of available locks. When using locks or sharing this package with other users, it is advisable to grant only the EXECUTE privilege to specific users or roles.
DBMS_LOCK constants
The following table lists the constants the DBMS_LOCK package uses when you specify parameter values.
| Name | Attribute name | Type | Value | OEM abbreviation | Description |
|---|---|---|---|---|---|
| NL_MODE | Nul1 | INTEGER | 1 | N/A | Nonlocking mode |
| SS_MODE | Sub Shared | INTEGER | 2 | ULRS | Shared Read Lock |
| SX_MODE | Subexclusive/Row Exclusive Mode | INTEGER | 3 | ULRX | Intention write lock |
| S_MODE | Shared/Row Exclusive Mode/Intended Exclusive | INTEGER | 4 | ULRSX | Reader 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 |
DBMS_LOCK rules and limitations
The following table lists the lock compatibility matrix. Y indicates a successful request, and N indicates a failed request.
| Holding lock type | Request NL lock | Request SS lock | Request SX lock | Request S lock | Request SSX lock | Request 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 |
Set the default lock wait time when requesting a lock by using maxwait.
maxwait constant integer := 32767;
DBMS_LOCK usage notes
- User locks are prefixed with "UL" and do not conflict with table locks.
- If the session is disconnected, then the locks are released.
- The lock ID ranges from (0, 1073741823).
- User locks and table locks behave identically and include all the capabilities of table locks, such as deadlock detection.
- Make sure that the user locks used in distributed transactions are released when the transaction is committed, or you might face a deadlock issue.
- For each session, best practices recommend that you limit the number of locks in
DBMS_LOCKto a few hundred.
DBMS_LOCK subprograms overview
The following table lists the DBMS_LOCK subprograms supported in the current version of OceanBase Database and provides brief descriptions.
| Subprogram | Description |
|---|---|
| ALLOCATE_UNIQUE | Allocates a unique lock ID for the lock name. The lock ID range is (1073741824, 1999999999). |
| ALLOCATE_UNIQUE_AUTONOMOUS | Allocates a unique lock ID for the lock name. |
| REQUEST | Obtains a lock. |
| SLEEP | Suspends the current session for the specified amount of time. |