DBMS_LOCK tips

Oracle DBMS_LOCK?

 The Oracle DBMS_LOCK package makes the Oracle Kernel’s lock management services available to user-written applications.

Application locking implemented using Oracle DBMS_LOCK is functionally identical to native RDBMS locking at the levels of lock modes and deadlock detection, compatibility, and visibility through V$ views.

Possible uses of Oracle DBMS_LOCK are as follows:

• Oracle DBMS_LOCK can provide for exclusive access to an external device or service (e.g., a printer)

• Oracle DBMS_LOCK can coordinate or synchronize application processing in parallel

• Oracle DBMS_LOCK can signal the availability of services

• Oracle DBMS_LOCK can enable or disable program execution

• Oracle DBMS_LOCK can coordinate access to a shared database pipe

Oracle DBMS_LOCK Programs

The Oracle DBMS_LOCK package presents a relatively straightforward interface containing two procedures and three functions. These are summarized below:

The Oracle DBMS_LOCK Programs

ALLOCATE_UNIQUE   Procedure   Generate lock handle for a given lock name
REQUEST           Function    Request lock in a specific mode
CONVERT           Function    Convert lock from one mode to another
RELEASE           Function    Release previously acquired lock
SLEEP             Procedure   Suspend session for a specified time

PROCEDURE DBMS_LOCK.ALLOCATE_UNIQUE


FUNCTION DBMS_LOCK.CONVERT RETURNS NUMBER(38)

 
FUNCTION DBMS_LOCK.CONVERT RETURNS NUMBER(38)


FUNCTION DBMS_LOCK.RELEASE RETURNS NUMBER(38)


FUNCTION DBMS_LOCK.RELEASE RETURNS NUMBER(38)


FUNCTION DBMS_LOCK.REQUEST RETURNS NUMBER(38)


FUNCTION DBMS_LOCK.REQUEST RETURNS NUMBER(38)


PROCEDURE DBMS_LOCK.SLEEP