Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)

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.

C.7.1 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);

C.7.2 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.

C.7.3 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

C.7.4 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.

C.7.5 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);

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.