How to Identify the Users of DBMS_LOCK (also known as UL) locks
Last updated on SEPTEMBER 29, 2017
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Information in this document applies to any platform.
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?
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms