The DBMS_LOCK package provides an interface to the lock management services of OceanBase Database.
The system package provides the following features:
Exclusive access to a device, such as a terminal
Perception of a lock release when an application releases or cleans up a lock
Synchronization and coordination among different applications or processes
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Privileges
The database system may impose an upper limit on the maximum number of available locks. When you use locks or provide this package to other users, consider granting the EXECUTE privilege only to specific users or roles.
Constants
The following table lists the constants used in the DBMS_LOCK package.
| Constant | Attribute | Type | Value | OEM abbreviation | Description |
|---|---|---|---|---|---|
| NL_MODE | Nul1 | INTEGER | 1 | None | Lock-free mode |
| SS_MODE | Sub Shared | INTEGER | 2 | ULRS | Intention read lock |
| SX_MODE | Sub eXclusive/Row Exclusive Mode | INTEGER | 3 | ULRX | Intention 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 intention write lock |
| X_MODE | Exclusive | INTEGER | 6 | ULX | Write lock |
Rules and limitations
The following table describes the lock compatibility matrix. Y indicates that the application succeeds, and N indicates that the application fails.
| Lock held mode | GET NL | GET SS | GET SX | GET S | GET SSX | GET X |
|---|---|---|---|---|---|---|
| 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 maxwait for a lock to set the default wait time.
maxwait constant integer := 32767;
Considerations
- User locks are identified with the prefix "UL" and will never conflict with table locks.
- Locks are automatically released when a session terminates.
- The lock ID ranges from 0 to 1073741823.
- A user lock acts in the same way as a table lock and has all the features of a table lock, such as deadlock detection.
- Make sure that any user locks used in distributed transactions are released upon transaction commits; otherwise, an undetected deadlock may occur.
DBMS_LOCKis most efficient with a limit of a few hundred locks for each session.
Subprograms
The following table describes the DBMS_LOCK subprograms supported by the current OceanBase Database version.
| Subprogram | Description |
|---|---|
| ALLOCATE_UNIQUE | Requests a unique lock ID in the range of 1073741824 to 1999999999 for a specified lock name. |
| ALLOCATE_UNIQUE_AUTONOMOUS | Requests a unique lock ID for a specified lock name. |
| REQUEST | Requests a lock. |
| SLEEP | Suspends the current session for a specific amount of time. |
| RELEASE | Releases a lock obtained by using the REQUEST function. |