DBMS_LOCK overview

The DBMS_LOCK package provides interfaces for OceanBase Database lock management.

The DBMS_LOCK package provides the following main functions:

• Mutual exclusion: grants exclusive access to certain devices, such as a terminal.

• The application can detect lock release: After the application releases or clears the lock, others can detect it.

• Synchronization: Coordination and synchronization of different applications or processes.

  Applicability

  This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition is available only in MySQL-compatible mode.

Privilege requirements

The database system has a limit on the total number of available locks. When using locks or granting this package to other users, consider granting the EXECUTE privilege only to specific users or roles.

DBMS_LOCK Constants

The DBMS_LOCK package includes the following constants, which can be used when you specify parameter values:

Rules and limitations of DBMS_LOCK

The following table describes the lock compatibility matrix. Y indicates a successful request, and N indicates a failed request.

You can set the default wait time maxwait when you request a lock.

maxwait  constant integer := 32767;

Considerations for using DBMS_LOCK

• User locks have the "UL" prefix and do not conflict with table locks.
• When a session disconnects, the lock is released.
• The lock ID can be in the range of (0, 1073741823).
• A user lock behaves the same as a table lock and has all the capabilities of a table lock, such as deadlock detection.
• Ensure that user locks used in a distributed transaction are released when the transaction is committed. Otherwise, a deadlock might occur.
• For each session, the best practice when using DBMS_LOCK is to keep the number of locks to less than a few hundred.

Overview of DBMS_LOCK subprograms

The following table describes the DBMS_LOCK subprograms and their purposes in OceanBase Database.