How to Identify the Users of DBMS_LOCK (also known as UL) locks (Doc ID 1913830.1)

Last updated on SEPTEMBER 19, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 8.0.6.0 and later
Information in this document applies to any platform.

Goal

Oracle's built-in DBMS_LOCK package allows the application developer to request, convert and release locks which are independent from the  locks which are provided automatically by Oracle. These routines are meant for synchronization within the application and augment the locking which is already there in the database. They are not meant to replace the existing locking which Oracle provides.

Such user locks are obtained by calls to DBMS_LOCK.ALLOCATE_UNIQUE. This procedure takes a user-supplied lock name and optionally an expiration interval and returns a unique lock handle. It always does an implicit commit. Deadlock detection is performed on these locks, and these locks are automatically released when the session terminates. It is up to the clients to agree on the use of these locks.

When DBMS_LOCK.ALLOCATE_UNIQUE is used, a unique lock handle is returned (in the range of 1073741824 to 1999999999) and associated to the user-supplied lock name.

With user defined locking in place, locks may be allocated with DBMS_LOCK and are denoted in the database as UL locks (user-defined locks).

How can we monitor such locks which were obtained from DBMS_LOCK calls and determine who is using them?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms