DBMS_LOCK

The DBMS_LOCK package provides you with access to the Oracle Lock Management (OLM) services. With OLM, you can request a lock of a particular type, assign it a name that can then be used as a handle to this lock, modify the lock, and even release the lock. A lock you create with the DBMS_LOCK package has all the functionality of a lock generated by the Oracle RDBMS, including deadlock detection and view access through SQL*DBA and the relevant virtual tables.

The ALLOCATE_UNIQUE procedure

The ALLOCATE_UNIQUE procedure allocates a unique lock handle for the specified lock name. The specification is:

PROCEDURE DBMS_LOCK.ALLOCATE_UNIQUE (lockname IN VARCHAR2, lockhandle OUT VARCHAR2, expiration_secs IN INTEGER DEFAULT 864000);

The CONVERT function

The CONVERT function converts a lock from one type or mode to another. The specifications are:

FUNCTION DBMS_LOCK.CONVERT (id IN INTEGER, lockmode IN INTEGER, timeout IN NUMBER DEFAULT MAXWAIT) RETURN INTEGER; FUNCTION DBMS_LOCK.CONVERT (lockhandle IN VARCHAR2, lockmode IN INTEGER, timeout IN NUMBER DEFAULT MAXWAIT) RETURN INTEGER;

The function returns the status of the attempt to change the mode, as shown below:

• 0
• Success.
• 1
• Timeout. The lock could not be converted within the specified number of seconds.
• 2
• Deadlock. In this case, an arbitrary session will be rolled back.
• 3
• Parameter error.
• 4
• The session does not own the lock specified by lock ID or the lock handle.
• 5
• Invalid lock handle. The handle was not found on the DBMS_LOCK_ALLOCATED table.

The RELEASE function

The RELEASE function releases the specified lock. This specifications are:

FUNCTION DBMS_LOCK.RELEASE (id IN INTEGER) RETURN INTEGER; FUNCTION DBMS_LOCK.RELEASE (lockhandle IN VARCHAR2) RETURN INTEGER;

In both cases, the RELEASE function returns a status with one of four values:

• 0
• Successful release of lock
• 3
• Error in the parameter passed to release
• 4
• Session does not own lock specified by ID or lock handle
• 5
• Illegal lock handle

The REQUEST function

The REQUEST function requests a lock of the specified mode. The specifications are:

FUNCTION DBMS_LOCK.REQUEST (id IN INTEGER, lockmode IN INTEGER DEFAULT X_MODE, timeout IN NUMBER DEFAULT MAXWAIT, release_on_commit IN BOOLEAN DEFAULT FALSE) RETURN INTEGER; FUNCTION DBMS_LOCK.REQUEST (lockhandle IN VARCHAR2, lockmode IN INTEGER DEFAULT X_MODE, timeout IN NUMBER DEFAULT MAXWAIT, release_on_commit IN BOOLEAN DEFAULT FALSE) RETURN integer;

The function returns the status of the attempt to obtain the lock; the codes are identical to those shown above for the convert function.

The SLEEP procedure

The SLEEP procedure suspends the current session for a specified period of time (in seconds). The specification is:

PROCEDURE DBMS_LOCK.SLEEP (seconds IN NUMBER);