4.1.1.1 DBMS_LOCK programs

Table 4.1 lists the programs available in the DBMS_LOCK package.

Table 4.1: DBMS_LOCK Programs

Name	Description	Use in SQL?
ALLOCATE_UNIQUE	Generates a unique lock ID for a given lock name	No
CONVERT	Converts lock to specified mode	No
RELEASE	Releases previously acquired lock	No
REQUEST	Requests lock with specified mode	No
SLEEP	Suspends the session for a specified time	No

DBMS_LOCK does not declare any package exceptions, and none of its programs assert a purity level with the RESTRICT_REFERENCES pragma.

4.1.1.2 DBMS_LOCK nonprogram elements

DBMS_LOCK declares a number of constants, most of which identify specific locking modes. Table 4.2 describes these elements.

Table 4.2: DBMS_LOCK Declared Constants

Constant	Description
nl_mode CONSTANT INTEGER	Null lock mode
ss_mode CONSTANT INTEGER	Sub-shared lock mode
sx_mode CONSTANT INTEGER	Sub-exclusive lock mode
s_mode CONSTANT INTEGER	Shared lock mode
ssx_mode CONSTANT INTEGER	Sub-shared exclusive lock mode
x_mode CONSTANT INTEGER	Exclusive lock mode
maxwait CONSTANT INTEGER	Used as default for timeout parameters

4.1.1.3 Lock compatibility rules

A lock held by one user session in a certain mode may prevent another session from being able to obtain that lock in the same or another mode. There are lock compatibility rules determining the success or failure of acquiring and converting locks from one mode to another, depending on the modes in which the same lock is held by other sessions. Table 4.3 indicates the compatibility rules for the various lock modes. The HELD MODE column indicates the mode in which the lock is currently held by some session. The other columns indicate whether the lock can be obtained by other sessions in the mode specified by the column header.

Table 4.3: Lock Mode Compatibility

HELD MODE	GET NL	GET SS	GET SX	GET S	GET SSX	GET X
NL	Succeed	Succeed	Succeed	Succeed	Succeed	Succeed
SS	Succeed	Succeed	Succeed	Succeed	Succeed	Fail
SX	Succeed	Succeed	Succeed	Fail	Fail	Fail
S	Succeed	Succeed	Fail	Succeed	Fail	Fail
SSX	Succeed	Succeed	Fail	Fail	Fail	Fail
X	Succeed	Fail	Fail	Fail	Fail	Fail

4.1.2.1 The DBMS_LOCK.ALLOCATE_UNIQUE procedure

The ALLOCATE_UNIQUE procedure returns a unique "handle" to a lock specified by the lockname parameter. The handle can be used to safely identify locks in calls to other DBMS_LOCK programs. Using lockhandles avoids the potential for lock identifier collisions that exists when identifiers are determined by applications. The header for this program follows:

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

Parameters for this procedure are summarized in the following table.

Parameter	Description
lockname	Name of the lock
lockhandle	Unique handle to lock by name
expiration_secs	Length of time to leave lock allocated

The program does not raise any package exceptions.

4.1.2.2 The DBMS_LOCK.REQUEST function

The REQUEST function is used to acquire a lock in the mode specified by the lockmode parameter. If the lock cannot be acquired in the requested mode within the specified time, the function call completes with a nonzero return value (see the parameter table).

The REQUEST function is overloaded on the first parameter, which is used to identify the lock by either an INTEGER identifier or by a VARCHAR2 lockhandle. The release_on_commit parameter indicates whether the lock should persist across RDBMS transactions or be automatically released upon COMMIT or ROLLBACK. The headers for this program, corresponding to each type, are as follows:

FUNCTION DBMS_LOCK.REQUEST     (id IN INTEGER     ,lockmode IN INTEGER DEFAULT X_MODE     ,timeout IN INTEGER 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 INTEGER DEFAULT MAXWAIT     ,release_on_commit IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;

Parameters for this function are summarized in the following table.

Parameter	Description
id	Numeric identifier of the lock
lockhandle	Handle for lock returned by DBMS_LOCK.ALLOCATE_UNIQUE
lockmode	Locking mode requested for lock
timeout	Time in seconds to wait for successful conversion
release_on_commit	If TRUE, release lock automatically on COMMIT or ROLLBACK

The following table summarizes the return values of the function.

Return Value	Description
0	Success
1	Timed out
2	Deadlock
3	Parameter error
4	Do not own lock; cannot convert
5	Illegal lockhandle

The program does not raise any package exceptions.

4.1.2.3 The DBMS_LOCK.CONVERT function

The CONVERT function is used to convert a previously acquired lock to the mode specified by the lockmode parameter. If the mode conversion cannot be granted within the specified time, the function call completes with a nonzero return value (see the following parameter table). CONVERT is overloaded on the first parameter, which is used to identify the lock by either an INTEGER identifier or a VARCHAR2 lockhandle. The headers for this program, corresponding to each type, follow:

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;

Parameters for this program are summarized in the following table.

Parameter	Description
id	Numeric identifier of the lock
lockhandle	Handle for lock returned by ALLOCATE_UNIQUE
lockmode	Locking mode to which to convert the lock
timeout	Time in seconds to wait for successful conversion

The return values for this function are summarized in the following table.

Return Value	Description
0	Success
1	Timed out
2	Deadlock
3	Parameter error
4	Do not own lock, cannot convert
5	Illegal lockhandle

The program does not raise any package exceptions.

4.1.2.4 The DBMS_LOCK.RELEASE function

The RELEASE function releases a previously acquired lock. RELEASE is overloaded on the first parameter, which is used to identify the lock by either an INTEGER identifier or a VARCHAR2 lockhandle. The program headers for each corresponding type follow:

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

Parameters are summarized in the following table.

Parameter	Description
id	Numeric identifier of the lock
lockhandle	Handle for lock returned by ALLOCATE_UNIQUE

The return values for this function are summarized in the following table.

Return Value	Description
0	Success
3	Parameter error
4	Do not own lock; cannot release
5	Illegal lockhandle

The program does not raise any package exceptions.

4.1.2.5 The DBMS_LOCK.SLEEP procedure

The SLEEP procedure suspends the session for the number of seconds specified in the seconds parameter. Sleep periods can be specified with accuracy down to the hundredth of a second (e.g., 1.35 and 1.29 are recognized as distinct sleep times). Here's the header for this program:

PROCEDURE DBMS_LOCK.SLEEP     (seconds IN NUMBER);

4.1.3.1 Named locks or lock ids?

Oracle provides two methods of identifying and manipulating user locks: integer lock identifiers and handles for named locks. Using names and lockhandles to identify locks is considered safer than using integer identifiers directly because naming standards can be adopted to virtually guarantee that different applications will not use the same lock for different purposes. Therefore, best practices for using DBMS_LOCK include the use of named locks and lockhandles.

4.1.3.2 Issues with named locks

There are a couple of drawbacks to using named locks that are worth pointing out. In particular:

• Named locks are recorded in the catalog, and thus may be slower.

• The DBMS_LOCK.ALLOCATE_UNIQUE procedure issues a COMMIT.

• Applications need to keep track of lockhandles for each named lock used.

It is worth investigating these drawbacks and developing techniques to minimize their impact, thus further encouraging the use of named locks.

4.1.3.3 Performance of named locks

We can investigate the performance penalty for using named locks, and quantify that penalty in a relatively straightforward manner. Consider the following PL/SQL script:

/* Filename on companion disk:  

lock1.sql */* set timing on set serveroutput on size 100000  DECLARE     lockname VARCHAR2(30) := 'OPBIP_TEST_LOCK_10';    lockhandle VARCHAR2(128);    lockid  INTEGER := 99999;     call_status  INTEGER;    timer NUMBER;  BEGIN     /*    || timed test using lockhandles    */    timer := DBMS_UTILITY.GET_TIME;    DBMS_LOCK.ALLOCATE_UNIQUE(lockname,lockhandle);    FOR i IN 1..10000    LOOP       call_status := DBMS_LOCK.REQUEST(lockhandle,timeout=>0);       call_status := DBMS_LOCK.RELEASE(lockhandle);    END LOOP;    DBMS_OUTPUT.PUT_LINE('Using lockhandles: '||       TO_CHAR(ROUND((DBMS_UTILITY.GET_TIME-timer)/100,2)) ||' secs');     /*    || timed test using lockids    */    timer := DBMS_UTILITY.GET_TIME;    FOR i IN 1..10000    LOOP       call_status := DBMS_LOCK.REQUEST(lockid,timeout=>0);       call_status := DBMS_LOCK.RELEASE(lockid);    END LOOP;    DBMS_OUTPUT.PUT_LINE('Using lockids: '||       TO_CHAR(ROUND((DBMS_UTILITY.GET_TIME-timer)/100,2)) ||' secs');  END;

The PL/SQL block reports on the elapsed times to request and release a lock 10,000 times using either a lockhandle or an integer lock identifier. The test yielded the following results on a Personal Oracle7 database with no other activity:

SQL> @l2 Using lockhandles: 9.57 secs Using lockids: 3.02 secs  PL/SQL procedure successfully completed.   real: 12740 SQL> spool off

These results confirm that use of lockhandles is significantly slower than use of lock identifiers. However, the results also indicate that the overhead of named locks was less than one-thousandth of a second per usage. Thus, the performance impact of using named locks is negligible and is probably not a legitimate concern for most applications.

4.1.3.4 ALLOCATE_UNIQUE drawbacks

The other issues mentioned with named locks are usage related. The ALLOCATE_UNIQUE procedure needs to be called to identify a lockhandle for each named lock. This procedure issues a COMMIT, which presents some usability issues. For one, the procedure cannot be called from a database trigger, so using named locks from a database trigger requires that the lockhandle be acquired outside of the trigger and saved for use in the trigger. Another problem is the COMMIT itself: an application may want to utilize a named lock but not necessarily COMMIT the current transaction. Thus, it is desirable when using named locks to limit the number of calls to ALLOCATE_UNIQUE to exactly one call per named lock used.

4.1.3.5 Optimizing named locks

One way to achieve the objective of minimizing calls to ALLOCATE_UNIQUE is to use private package global variables to store lockhandles for each named lock. A function that will return the lockhandle can then be written, calling ALLOCATE_UNIQUE only if the lockhandle has not been previously identified. This technique is illustrated as follows:

PACKAGE BODY print_pkg IS    /* private globals for lock identification */    printer_lockname  VARCHAR2(128) := 'printer_lock';    printer_lockhandle  VARCHAR2(128);     FUNCTION get_printer_lockhandle    RETURN VARCHAR2    IS    BEGIN       IF printer_lockhandle IS NULL       THEN          DBMS_LOCK.ALLOCATE_UNIQUE             (lockname => printer_lockname             ,lockhandle => printer_lockhandle);       END IF;        RETURN printer_lockhandle;    END get_printer_lockhandle;  END print_pkg;

Using this technique ensures that the ALLOCATE_UNIQUE procedure is called only once per session requiring use of the printer lock. The lock can even be used in a database trigger if the function get_printer_lockhandle has been called prior to the triggering event.

One drawback to this technique is code redundancy: each named lock used by an application requires adding a specific package global variable for the lockhandle and an associated function to return it. Referencing a new named lock in an application involves adding a nontrivial amount of code before it can be used.

4.1.3.6 REQUEST or CONVERT?

Another usability issue with DBMS_LOCK (not specific to named locks): applications using multiple lock modes need to have intelligence about whether to call the REQUEST function or the CONVERT function. If the user has requested and received a lock in a specific mode, then that mode can only be changed by calling CONVERT. On the other hand, a lock conversion can only take place if it is preceded by a successful call to REQUEST. Getting it right can mean developing code that checks and tracks return codes from the calls to these two procedures.

4.1.4.1 The dblock package

The dblock package specification follows:

/* Filename on on companion disk: 

dblock.sql */* CREATE OR REPLACE PACKAGE dblock    /*    || Adds value to DBMS_LOCK by allowing easier manipulation    || of named locks. Calling programs use lock names only,    || corresponding lockhandles are automatically identified,    || used and saved for subsequent use.    ||    ||    || Author:  John Beresniewicz, Savant Corp    ||    || 10/26/97: added expiration_secs_IN to lockhandle    || 10/21/97: added release    || 10/21/97: added dump_lockhandle_tbl    || 10/17/97: created    ||    || Compilation Requirements:    ||    || EXECUTE on DBMS_LOCK    || EXECUTE on DBMS_SESSION    ||    || Execution Requirements:    ||    */ AS    /* variables to anchor other variables */    lockname_var  VARCHAR2(128);    lockhandle_var VARCHAR2(128);     /*    || returns TRUE if a COMMIT has taken place between    || subsequent calls to the function    || NOTE: returns TRUE on first call in session    */    FUNCTION committed_TF RETURN BOOLEAN;     /*    || returns lockhandle for given lockname, only calls    || DBMS_LOCK.ALLOCATE_UNIQUE if lockhandle has not been    || previously determined    */    FUNCTION lockhandle       (lockname_IN IN lockname_var%TYPE       ,expiration_secs_IN IN INTEGER := 864000)    RETURN lockhandle_var%TYPE;     /*    || returns TRUE if named lock is acquired in mode    || specified    */    FUNCTION get_lock_TF       (lockname_IN IN lockname_var%TYPE       ,mode_IN IN INTEGER := DBMS_LOCK.x_mode       ,timeout_IN IN INTEGER := 1       ,release_on_commit_TF IN BOOLEAN := FALSE)    RETURN BOOLEAN;     /* releases named lock */    PROCEDURE release (lockname_IN IN lockname_var%TYPE);     /* print contents of lockhandle_tbl for debugging */    PROCEDURE dump_lockhandle_tbl;  END dblock;

The dblock programs allow the user to identify and acquire locks by name only. Lockhandles for each named lock are managed within the package, transparent to the application. The package associates locknames with lockhandles by using a private global PL/SQL table called lockhandle_tbl. The table is defined as follows:

   /* rectype to pair handles with names */    TYPE handle_rectype IS RECORD       (name   lockname_var%TYPE       ,handle lockhandle_var%TYPE       );     /* table to store lockhandles by name */    TYPE handle_tbltype IS TABLE OF handle_rectype       INDEX BY BINARY_INTEGER;     lockhandle_tbl handle_tbltype;

4.1.4.2 Using locks to signal service availability

One way in which DBMS_LOCK can be usefully employed is to indicate the availability of service programs to database sessions. The basic steps are quite simple:

1. Assign specific locks to the server and/or each service provided.

2. The server process holds the lock(s) in exclusive mode when services are available.

3. Client programs request the lock to determine service availability.

To make this more concrete, the following code fragments might be part of a package used to coordinate access to a computation server called calcman:

PACKAGE calcman IS    /* the actual service provider program */    PROCEDURE calcman_driver;     /* function called by clients to determine availability */    FUNCTION calcman_available RETURN BOOLEAN;  END calcman;  PACKAGE BODY calcman IS    /* lock name used to flag service availability */    calcman_lockname  VARCHAR2(100):= 'CALCMAN_LOCK';     PROCEDURE calcman_driver    IS    BEGIN       /*       || get the special lock in exclusive mode       */       IF dblock.get_lock_TF             (lockname_IN => calcman_lockname             ,mode_IN => DBMS_LOCK.x_mode             ,timeout_IN => 1             ,release_on_commit_TF => FALSE)       THEN          /*          || execute the service loop here, which probably          || involves listening on a database pipe for          || service requests and sending responses on pipes          */          /*           || loop forever and process calc requests           */          WHILE NOT terminate_TF           LOOP             receive_unpack_calc_request                (timeout_IN => DBMS_PIPE.maxwait                ,request_rec_OUT=> request_rec                ,return_code_OUT => temp_return_code);             IF temp_return_code != 0             THEN                DBMS_PIPE.PURGE(request_pipe);             ELSE                process_request(request_rec);             END IF;          END LOOP;       ELSE          /* service is already running in another process, exit */          RETURN;       END IF:    END calcman_driver;      FUNCTION calcman_available RETURN BOOLEAN    IS       got_lock  BOOLEAN;    BEGIN       got_lock := dblock.get_lock_TF                      (lockname => calcman_lockname                      ,mode_IN => DBMS_LOCK.sx_mode                      ,timeout_IN => 0                      ,release_on_commit_TF => TRUE);        /*       || do not hold lock, this could conflict with       || starting service       */       dblock.release(calcman_lockname);        /* failure to get lock indicates server available */       RETURN NOT got_lock;    END calcman_available;  END calcman;

The calcman_driver procedure grabs and holds the lock as long as it is executing. If the lock is not available within one second, the procedure is already running in another session and exits silently in the current session. Thus, the lock ensures that only one calcman_driver will be executing at any time. Note the importance of not releasing the lock at COMMIT, ensuring that the lock is held as long as the service process is alive. The service can make itself unavailable at any time by simply releasing the lock.

The service that calcman_driver provides is not specified in the previous code fragments. It could be a complex calculation requiring large PL/SQL tables for which the overhead of having all users execute the calculation individually is too great. Or it could be connected to an external service routine of some kind. A fuller discussion of how to implement such service procedures using database pipes can be found in Chapter 3, Intersession Communication .

Client programs call the calcman_available function to determine whether the server is executing and providing its computation services. The function attempts to get the lock and, if it succeeds, this indicates that the service is not available. The lock is requested in shared mode exclusive; as a consequence, concurrent calls to the get_lock_TF function from different sessions may all succeed and indicate unavailability. If the lock is requested in exclusive mode, there is a chance that simultaneous execution of the function by two users could falsely indicate to one user that the service is available. The calcman_available function also releases the lock immediately to keep it from interfering with the calcman_driver program, which is attempting to secure the lock.