18
DBMS_LOCK

Oracle Lock Management services for your applications are available through procedures in the DBMS_LOCK package. You can 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.

User locks never conflict with Oracle locks because they are identified with the prefix "UL". You can view these locks using the Enterprise Manager lock monitor screen or the appropriate fixed views. User locks are automatically released when a session terminates.

The lock identifier is a number in the range of 0 to 1073741823.

Some uses of user locks:

• Providing exclusive access to a device, such as a terminal

• Providing application-level enforcement of read locks

• Detecting when a lock is released and cleanup after the application

• Synchronizing applications and enforcing sequential processing

Requirements

DBMS_LOCK is most efficient with a limit of a few hundred locks per session. Oracle strongly recommends that you develop a standard convention for using these locks in order to avoid conflicts among procedures trying to use the same locks. For example, include your company name as part of your lock names.

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.

Viewing and Monitoring Locks

Oracle provides two facilities to display locking information for ongoing transactions within an instance:

Enterprise Manager Monitors (Lock and Latch Monitors)

The Monitor feature of Enterprise Manager provides two monitors for displaying lock information of an instance. Refer to Oracle Server Manager User's Guide for complete information about the Enterprise Manager monitors.

UTLLOCKT.SQL

The UTLLOCKT.SQL script displays a simple character lock wait-for graph in tree structured fashion. Using any ad hoc SQL tool (such as SQL*Plus) to execute the script, it prints the sessions in the system that are waiting for locks and the corresponding blocking locks. The location of this script file is operating system dependent. (You must have run the CATBLOCK.SQL script before using UTLLOCKT.SQL.)

Constants

nl_mode  constant integer := 1;
ss_mode  constant integer := 2;       -- Also called 'Intended Share'
sx_mode  constant integer := 3;       -- Also called 'Intended Exclusive'
s_mode   constant integer := 4;
ssx_mode constant integer := 5;
x_mode   constant integer := 6;
  

These are the various lock modes (nl -> "NuLl", ss -> "Sub Shared", sx -> "Sub eXclusive", s -> "Shared", ssx -> "Shared Sub eXclusive", x -> "eXclusive").

A sub-share lock can be used on an aggregate object to indicate that share locks are being aquired on sub-parts of the object. Similarly, a sub-exclusive lock can be used on an aggregate object to indicate that exclusive locks are being aquired on sub-parts of the object. A share-sub-exclusive lock indicates that the entire aggregate object has a share lock, but some of the sub-parts may additionally have exclusive locks.

Lock Compatibility Rules

When another process holds "held", an attempt to get "get" does the following:

Table 18-1 Lock Compatibility

maxwait  constant integer := 32767;

The constant maxwait waits forever.

Summary of Subprograms

Table 18-2 DBMS_LOCK Package Subprograms

ALLOCATE_UNIQUE procedure

REQUEST function

CONVERT function

RELEASE function

SLEEP procedure

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.

Syntax

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

Parameters

Table 18-3 ALLOCATE_UNIQUE Procedure Parameters

lockname

lockhandle

expiration_specs

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 18-4 REQUEST Function Parameters

id or lockhandle

lockmode 

timeout

release_on_commit

Return Values

Table 18-5 REQUEST Function Return Values

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 18-6 CONVERT Function Parameters

id or lockhandle

lockmode 

timeout

Return Values

Table 18-7 CONVERT Function Return Values

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 18-8 RELEASE Function Parameter

id or lockhandle

Return Values

Table 18-9 RELEASE Function Return Values

Exceptions

None.

SLEEP procedure

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

Syntax

DBMS_LOCK.SLEEP (
   seconds  IN NUMBER);

Parameters

Table 18-10 SLEEP Procedure Parameters

seconds

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.