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.
PROCEDURE DBMS_LOCK.ALLOCATE_UNIQUE (lockname IN VARCHAR2, lockhandle OUT VARCHAR2, expiration_secs IN INTEGER DEFAULT 864000);
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:
Timeout. The lock could not be converted within the specified number of seconds.
Deadlock. In this case, an arbitrary session will be rolled back.
The session does not own the lock specified by lock ID or the lock handle.
Invalid lock handle. The handle was not found on the DBMS_LOCK_ALLOCATED table.
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:
Successful release of lock
Error in the parameter passed to release
Session does not own lock specified by ID or lock handle
Illegal lock handle
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.