DBMS_LOCK , 2 of 2

Summary of Subprograms

Table 19-2 DBMS_LOCK Package Subprograms

Subprogram	Description
ALLOCATE_UNIQUE Procedure	Allocates a unique lock ID to a named lock.
REQUEST Function	Requests a lock of a specific mode.
CONVERT Function	Converts a lock from one mode to another.
RELEASE Function	Releases a lock.
SLEEP Procedure	Puts a procedure to sleep for a specific time.

ALLOCATE_UNIQUE Procedure

This procedure allocates a unique lock identifier (in the range of 1073741824 to 1999999999) given a lock name. Lock identifiers are used to enable applications to coordinate their use of locks. This is provided because it may be easier for applications to coordinate their use of locks based on lock names rather than lock numbers.

If you choose to identify locks by name, you can use ALLOCATE_UNIQUE to generate a unique lock identification number for these named locks.

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. Subsequent calls (usually by other sessions) return the lock ID previously generated.

A lock name is associated with the returned lock ID for at least expiration_secs (defaults to 10 days) past the last call to ALLOCATE_UNIQUE with the given lock name. After this time, the row in the dbms_lock_allocated table for this lock name may be deleted in order to recover space. ALLOCATE_UNIQUE performs a commit.

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

Syntax

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

Parameters

Table 19-3 ALLOCATE_UNIQUE Procedure Parameters

Parameter Description

Parameter	Description
lockname	Name of the lock for which you want to generate a unique ID. Do not use lock names beginning with `ORA$`; these are reserved for products supplied by Oracle Corporation.
lockhandle	Returns the handle to the lock ID generated by `ALLOCATE_UNIQUE`. You can use this handle in subsequent calls to `REQUEST`, `CONVERT`, and `RELEASE`. A handle is returned instead of the actual lock ID to reduce the chance that a programming error accidentally creates an incorrect, but valid, lock ID. This provides better isolation between different applications that are using this package. `LOCKHANDLE` can be up to `VARCHAR2` (128). All sessions using a lock handle returned by `ALLOCATE_UNIQUE` with the same lock name are referring to the same lock. Therefore, do not pass lock handles from one session to another.
expiration_specs	Number of seconds to wait after the last `ALLOCATE_UNIQUE` has been performed on a given lock, before permitting 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.

lockname

Name of the lock for which you want to generate a unique ID.

Do not use lock names beginning with ORA$; these are reserved for products supplied by Oracle Corporation.

lockhandle

Returns the handle to the lock ID generated by ALLOCATE_UNIQUE.

You can use this handle in subsequent calls to REQUEST, CONVERT, and RELEASE.

A handle is returned instead of the actual lock ID to reduce the chance that a programming error accidentally creates an incorrect, but valid, lock ID. This provides better isolation between different applications that are using this package.

LOCKHANDLE can be up to VARCHAR2 (128).

All sessions using a lock handle returned by ALLOCATE_UNIQUE with the same lock name are referring to the same lock. Therefore, do not pass lock handles from one session to another.

expiration_specs

Number of seconds to wait after the last ALLOCATE_UNIQUE has been performed on a given lock, before permitting 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.

Errors

ORA-20000, ORU-10003: Unable to find or insert lock <lockname> into catalog dbms_lock_allocated.

Exceptions

None.

REQUEST Function

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

Syntax

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 current default values, such as X_MODE and MAXWAIT, are defined in the DBMS_LOCK package specification.

Parameters

Table 19-4 REQUEST Function Parameters

Parameter	Description
id or lockhandle	User assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by `ALLOCATE_UNIQUE`, of the lock mode you want to change.
lockmode	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 Enterprise Manager monitors are 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 Oracle8 Concepts.
timeout	Number of seconds to continue trying to grant the lock. If the lock cannot be granted within this time period, then the call returns a value of 1 (`timeout`).
release_on_commit	Set this parameter to `TRUE` to release the lock on commit or roll-back. Otherwise, the lock is held until it is explicitly released or until the end of the session.

Return Values

Table 19-5 REQUEST Function Return Values

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

Exceptions

None.

CONVERT Function

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

Syntax

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

Parameters

Table 19-6 CONVERT Function Parameters

Parameter Description

id or lockhandle

User assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by ALLOCATE_UNIQUE, of the lock mode you want to change.

lockmode

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 Enterprise Manager monitors are 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 Oracle8 Concepts.

timeout

Number of seconds to continue trying to change the lock mode.

If the lock cannot be converted within this time period, then the call returns a value of 1 (timeout).

Return Values

Table 19-7 CONVERT Function Return Values

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

Exceptions

None.

RELEASE Function

This function explicitly releases a lock previously acquired using the REQUEST 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.

Syntax

DBMS_LOCK.RELEASE (
   id         IN INTEGER)
  RETURN INTEGER;

DBMS_LOCK.RELEASE (
   lockhandle IN VARCHAR2)
  RETURN INTEGER;

Parameters

Table 19-8 RELEASE Function Parameter

Parameter	Description
id or lockhandle	User assigned lock identifier, from 0 to 1073741823, or the lock handle, returned by `ALLOCATE_UNIQUE`, of the lock mode you want to change.

Return Values

Table 19-9 RELEASE Function Return Values

Return Value	Description
0	Success
3	Parameter error
4	Do not own lock specified by `id` or `lockhandle`
5	Illegal lock handle

Exceptions

None.

SLEEP Procedure

This procedure suspends the session for a given period of time.

Syntax

DBMS_LOCK.SLEEP (
   seconds  IN NUMBER);

Parameters

Table 19-10 SLEEP Procedure Parameters

Parameter	Description
seconds	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.

Parameter

Description

seconds

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.

Example

This Pro*COBOL precompiler example shows how locks can be used to ensure that there are no conflicts when multiple people need to access a single device.

Print Check

Any cashier may issue a refund to a customer returning goods. Refunds under $50 are given in cash; anything above that is given by check. This code prints the check. The one printer is opened by all the cashiers to avoid the overhead of opening and closing it for every check. This means that lines of output from multiple cashiers could become interleaved if we don't ensure exclusive access to the printer. The DBMS_LOCK package is used to ensure exclusive access.

CHECK-PRINT

Get the lock "handle" for the printer lock:

   MOVE "CHECKPRINT" TO LOCKNAME-ARR. 
   MOVE 10 TO LOCKNAME-LEN. 
   EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.ALLOCATE_UNIQUE ( :LOCKNAME, :LOCKHANDLE ); 
      END; END-EXEC.

Lock the printer in exclusive mode (default mode):

   EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.REQUEST ( :LOCKHANDLE ); 
      END; END-EXEC.

We now have exclusive use of the printer, print the check:

...

Unlock the printer so other people can use it:

   EXEC SQL EXECUTE 
      BEGIN DBMS_LOCK.RELEASE ( :LOCKHANDLE ); 

      END; END-EXEC.