Overview

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.

Rules and limitations

The following table describes the lock compatibility matrix. Y indicates that the application succeeds, and N indicates that the application fails.

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_LOCK is 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.