Oracle PL/SQL Programming, 2nd Edition

Oracle PL/SQL Programming, 2nd EditionSearch this book
Previous: C.6 DBMS_LOB (PL/SQL8 Only)Appendix C
Built-In Packages
Next: C.8 DBMS_MAIL
 

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

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);


Previous: C.6 DBMS_LOB (PL/SQL8 Only)Oracle PL/SQL Programming, 2nd EditionNext: C.8 DBMS_MAIL
C.6 DBMS_LOB (PL/SQL8 Only)Book IndexC.8 DBMS_MAIL

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference
This HTML Help has been published using the chm2web software.