Oracle7 Server Application Developer's Guide

Creating User Locks

You can use Oracle Lock Management services for your applications. It is possible to request a lock of a specific mode, give it a unique name recognizable in another procedure in the same or another instance, change the lock mode, and release it. Because a reserved user lock is the same as an Oracle lock, it has all the functionality of an Oracle lock, such as deadlock detection. Be certain that any user locks used in distributed transactions are released upon COMMIT, or an undetected deadlock may occur.

The DBMS_LOCK Package

The Oracle Lock Management services are available through procedures in the DBMS_LOCK package. Table 3 - 3 summarizes the procedures available in the DBMS_LOCK package.

Function/Procedure Description Refer to
ALLOCATE_UNIQUE Allocate a unique lock ID to a named lock.
REQUEST Request a lock of a specific mode.
CONVERT Convert a lock from one mode to another.
RELEASE Release a lock.
SLEEP Put a procedure to sleep for a specified time.

Table 3 - 3. DBMS_LOCK Package Functions and Procedures

User locks never conflict with Oracle locks because they are identified with the prefix "UL". You can view these locks using the Server Manager lock monitor screen or the appropriate fixed views.

User locks are automatically released when a session terminates.

Warning: This implementation does not efficiently support more than a few hundred locks per session. Oracle strongly recommends that you develop a standard convention be developed for using these user locks. This avoids conflicts among procedures trying to use the same locks. For example, you might want to include your company name as part of the lock name to ensure that your lock names do not conflict with lock names used in any Oracle supplied applications.

Security

There might be operating system-specific limits on the maximum number of total locks available. This must be considered when using locks or making this package available to other users. Consider granting the EXECUTE privilege only to specific users or roles. A better alternative would be to create a cover package limiting the number of locks used and grant EXECUTE privilege to specific users. An example of a cover package is documented in the DBMSLOCK.SQL package specification file. (See the commented-out package LOCK_100_TO_200.)

Creating the DBMS_LOCK Package

To create the DBMS_LOCK package, submit the DBMSLOCK.SQL and PRVTLOCK.PLB scripts when connected as the user SYS. These scripts are run automatically by the CATPROC.SQL script. See

for information on granting the necessary privileges to users who will be executing this package.

ALLOCATE_UNIQUE Procedure

Lock identifiers are used to allow applications to coordinate their use of locks. User-assigned lock identifiers can be a number in the range of 0 to 1073741823, or locks can be identified by name. If you choose to identify locks by name, you can use ALLOCATE_UNIQUE to generate a unique lock identification number for these named locks.

Warning: Named user locks may be less efficient, as Oracle uses SQL to determine the lock associated with a given name.

The parameters for the ALLOCATE_UNIQUE procedure are described in Table 3 - 4. The syntax for this procedure is shown below.

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

Parameter Description
LOCKNAME Specify the name of the lock for which you want to generate a unique ID. The first session to call ALLOCATE_UNIQUE with a new lock name causes a unique lock ID to be generated and stored in the DBMS_LOCK_ALLOCATED table. The handle to this ID is then returned for this call, and all subsequent calls (usually by other sessions). Lock IDs assigned by ALLOCATE_UNIQUE are in the range of 1073741824 to 1999999999.
Do not use lock names beginning with ORA$; these names are reserved for products supplied by Oracle Corporation.
LOCKHANDLE Returns to the caller the handle to the lock ID generated by ALLOCATE_UNIQUE. You can use this handle in subsequent calls to REQUEST, CONVERT, and RELEASE. LOCKHANDLE can be up to VARCHAR2(128).
A handle is returned instead of the actual lock ID to reduce the chance that a programming error can accidentally create an incorrect, but valid, lock ID. This provides better isolation between different applications that are using this package.
All sessions using a lock handle returned by ALLOCATE_UNIQUE using the same lock name are referring to the same lock. Different sessions can have different lock handles for the same lock, so do not pass lock handles from one session to another.
EXPIRATION_SECS Specify the number of seconds to wait after the last ALLOCATE_UNIQUE has been performed on a given lock, before allowing that lock to be deleted from the DBMS_LOCK_ALLOCATED table. The default waiting period is 10 days. You should not delete locks from this table. Subsequent calls to ALLOCATE_UNIQUE may delete expired locks to recover space.

Table 3 - 4. DBMS_LOCK.ALLOCATE_UNIQUE Procedure Parameters

REQUEST Function

To request a lock with a given mode, use the REQUEST function. REQUEST is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure.

The parameters for the REQUEST function are described in Table 3 - 5 and the possible return values and their meanings are described in Table 3 - 6. The syntax for this function is shown below.

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

The default values, such as X_MODE and MAXWAIT, are defined in the DBMS_LOCK package specification. See the package specification, available on-line, for the current default values.

Parameter Description
ID orLOCKHANDLE Specify the user assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by ALLOCATE_UNIQUE, of the lock whose mode you want to change.
LOCKMODE Specify the mode that you are requesting for the lock. The available modes and their associated integer identifiers are listed below. The abbreviations for these locks, as they appear in the V$ views and Server Manager monitors are shown in parentheses.
1 - null mode 2 - row share mode (ULRS) 3 - row exclusive mode (ULRX) 4 - share mode (ULS) 5 - share row exclusive mode (ULRSX) 6 - exclusive mode (ULX)
Each of these lock modes is explained in the Oracle7 Server Concepts manual.
TIMEOUT Specify the number of seconds to continue trying to grant the lock. If the lock cannot be granted within this time period, the call returns a value of 1 (timeout).
RELEASE_ON_COMMIT Set this parameter to TRUE to release the lock on commit or rollback. Otherwise, the lock is held until it is explicitly released or until the end of the session.

Table 3 - 5. DBMS_LOCK.REQUEST Function Parameters

Return Value Description
0 success
1 timeout
2 deadlock
3 parameter error
4 already own lock specified by ID or LOCKHANDLE
5 illegal lock handle

Table 3 - 6. DBMS_LOCK.REQUEST Function Return Values

CONVERT Function

To convert a lock from one mode to another, use the CONVERT function. CONVERT is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure.

The parameters for the CONVERT function are described in Table 3 - 7 and the possible return values and their meanings are described in Table 3 - 8. The syntax for this function is shown below.

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

Parameter Description
ID orLOCKHANDLE Specify the user assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by ALLOCATE_UNIQUE, of the lock whose mode you want to change.
LOCKMODE Specify the new mode that you want to assign to the given lock. The available modes and their associated integer identifiers are listed below. The abbreviations for these locks, as they appear in the V$ views and Server Manager monitors are shown in parentheses.
1 - null mode 2 - row share mode (ULRS) 3 - row exclusive mode (ULRX) 4 - share mode (ULS) 5 - share row exclusive mode (ULRSX) 6 - exclusive mode (ULX)
Each of these lock modes is explained in the Oracle7 Server Concepts manual.
TIMEOUT Specify the number of seconds to continue trying to change the lock mode. If the lock cannot be converted within this time period, the call returns a value of 1 (timeout).

Table 3 - 7. DBMS_LOCK.CONVERT Function Parameters

Return Value Description
0 success
1 timeout
2 deadlock
3 parameter error
4 don't own lock specified by ID or LOCKHANDLE
5 illegal lock handle

Table 3 - 8. DBMS_LOCK.CONVERT Function Return Values

RELEASE Function

To explicitly release a lock previously acquired using the REQUEST function, use the RELEASE function. Locks are automatically released at the end of a session. RELEASE is an overloaded function that accepts either a user-defined lock identifier, or the lock handle returned by the ALLOCATE_UNIQUE procedure.

The parameters for the RELEASE function are described in Table 3 - 9 and the possible return values and their meanings are described in Table 3 - 10. The syntax for this function is shown below.

DBMS_LOCK.RELEASE(id         IN INTEGER)
RETURN INTEGER;

DBMS_LOCK.RELEASE(lockhandle IN VARCHAR2)
RETURN INTEGER;

Parameter Description
ID orLOCKHANDLE Specify the user-assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by ALLOCATE_UNIQUE, of the lock that you want to release.

Table 3 - 9. DBMS_LOCK.RELEASE Function Parameter

Return Value Description
0 success
3 parameter error
4 do not own lock specified by ID or LOCKHANDLE
5 illegal lock handle

Table 3 - 10. DBMS_LOCK.RELEASE Function Return Values

SLEEP Procedure

To suspend the session for a given period of time, use the SLEEP procedure.

The parameters for the SLEEP procedure are described in Table 3 - 11. The syntax for the SLEEP procedure is shown below.

DBMS_LOCK.SLEEP(seconds  IN NUMBER);

Parameter Description
SECONDS Specify the amount of time, in seconds, to suspend the session. The smallest increment can be entered in hundredths of a second; for example, 1.95 is a legal time value.

Table 3 - 11. DBMS_LOCK.SLEEP Procedure Parameters

Function/Procedure	Description	Refer to
`ALLOCATE_UNIQUE`	Allocate a unique lock ID to a named lock.
`REQUEST`	Request a lock of a specific mode.
`CONVERT`	Convert a lock from one mode to another.
`RELEASE`	Release a lock.
`SLEEP`	Put a procedure to sleep for a specified time.

Parameter	Description
`LOCKNAME`	Specify the name of the lock for which you want to generate a unique ID. The first session to call ALLOCATE_UNIQUE with a new lock name causes a unique lock ID to be generated and stored in the DBMS_LOCK_ALLOCATED table. The handle to this ID is then returned for this call, and all subsequent calls (usually by other sessions). Lock IDs assigned by ALLOCATE_UNIQUE are in the range of 1073741824 to 1999999999. Do not use lock names beginning with ORA$; these names are reserved for products supplied by Oracle Corporation.
`LOCKHANDLE`	Returns to the caller the handle to the lock ID generated by ALLOCATE_UNIQUE. You can use this handle in subsequent calls to REQUEST, CONVERT, and RELEASE. LOCKHANDLE can be up to VARCHAR2(128). A handle is returned instead of the actual lock ID to reduce the chance that a programming error can accidentally create an incorrect, but valid, lock ID. This provides better isolation between different applications that are using this package. All sessions using a lock handle returned by ALLOCATE_UNIQUE using the same lock name are referring to the same lock. Different sessions can have different lock handles for the same lock, so do not pass lock handles from one session to another.
`EXPIRATION_SECS`	Specify the number of seconds to wait after the last ALLOCATE_UNIQUE has been performed on a given lock, before allowing that lock to be deleted from the DBMS_LOCK_ALLOCATED table. The default waiting period is 10 days. You should not delete locks from this table. Subsequent calls to ALLOCATE_UNIQUE may delete expired locks to recover space.

Parameter	Description
`ID` or`LOCKHANDLE`	Specify the user assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by ALLOCATE_UNIQUE, of the lock whose mode you want to change.
`LOCKMODE`	Specify the mode that you are requesting for the lock. The available modes and their associated integer identifiers are listed below. The abbreviations for these locks, as they appear in the V$ views and Server Manager monitors are shown in parentheses. 1 - null mode 2 - row share mode (ULRS) 3 - row exclusive mode (ULRX) 4 - share mode (ULS) 5 - share row exclusive mode (ULRSX) 6 - exclusive mode (ULX) Each of these lock modes is explained in the Oracle7 Server Concepts manual.
`TIMEOUT`	Specify the number of seconds to continue trying to grant the lock. If the lock cannot be granted within this time period, the call returns a value of 1 (timeout).
`RELEASE_ON_COMMIT`	Set this parameter to TRUE to release the lock on commit or rollback. Otherwise, the lock is held until it is explicitly released or until the end of the session.

Return Value	Description
`0`	success
`1`	timeout
`2`	deadlock
`3`	parameter error
`4`	already own lock specified by ID or LOCKHANDLE
`5`	illegal lock handle

Parameter	Description
`SECONDS`	Specify the amount of time, in seconds, to suspend the session. The smallest increment can be entered in hundredths of a second; for example, 1.95 is a legal time value.