DBAs are familiar with most wait events, however there is one called “enq: UL – contention”, which does not pop up that often:
The foreground events above are from an Oracle 19c database. Clearly, we see a lot of user defined lock waits: enq: UL – contention. They come usually when the DBMS_LOCK package is being used.
There are 3 enqueue types which are defined in Oracle as “User Type” locks, they are:
TM – DML Enqueue called against base tables or partitions for operations that need to be coordinatedTX – Transaction Enqueue used to protect transaction informationUL – User Lock Enqueue used when an application makes use of the DBMS_LOCK package
MOS has a note Resolving Issues Where ‘enq: UL – contention’ Waits are Occurring (Doc ID 1915980.1) which can help us identify what session is holding the lock and what part of the application it is currently executing.
The ‘UL’ (User-defined Lock) Enqueue is a lock that is created and defined by a developer (or seldom a DBA) using the DBMS_LOCK package. The DBMS_LOCK package allows the application developer to request, convert and release locks which are independent from the locks provided automatically by Oracle. These routines are meant for synchronization within the application and augment the locking which is already there in the database.
Locks are automatically released at the end of a session. DBSM_LOCK.RELEASE explicitly releases a lock previously acquired using the REQUEST function.
Wondering How can I find who has allocated a user lock via dbms_lock?
Here is the query from the link above showing how to find the active blocking situations around DBMS_LOCK:
, blocked.username blocked, blocked.sid blocked_session
FROM v$session blocked
JOIN v$session blocker
ON (blocked.blocking_session = blocker.sid)
WHERE blocked.wait_class = ‘Application’
AND blocked.event=’enq: UL – contention’;
The DBMS_LOCK_ALLOCATED table is periodically cleared out by calling the allocate_unique() procedure when the modulus of the lockid is 0 and the expiration column is less than the current date. On every 100th attempt to create a new entry, allocate_unique purges expired entries from the table. If the entry already exists, allocate_unique updates the expiry time.
There are a couple important MOS notes related to the DBMS_LOCK_ALLOCATED table:
The first one called DBMS_LOCK_ALLOCATED Table Keeps Growing Eventually Depleting Tablespace (Doc ID 1054343.6) explains how to delete entries for locks that are no longer being used. The second on entitled How To Cleanup And Shrink DBMS_LOCK_ALLOCATED? (Doc ID 1065384.1) is a bit similar and it warns that Bug 2624130 is going to make the delete very slow. In that case, one option is to use DBMS_REDEFINITION: How To Cleanup And Shrink DBMS_LOCK_ALLOCATED? (Doc ID 1065384.1).
The structure of the DBMS_LOCK_ALLOCATED table is rather simple it has only 3 columns: name, lockid and expiration.
There are few bugs in the Oracle database related to the “enq: UL – contention” wait event:
Bug 30070584 – 13.3 ‘enq: UL – contention’ wait event (OEM related)
Bug 27282543 – 13.2 Frequent UL contention wait events for the node 2 on EMNPRD repository db (OEM)
Bug 26618817 – UL contention from ProcessBuddyAgent jobs in scheduled state
A recent update from 2020 (Oracle 19.3) by Jonathan Lewis confirmed that PL/SQL execution time includes the time spent waiting for UL enqueues, so we should not forget that we may need to subtract wait time for ‘enq: UL – contention’ from the PL/SQL time before you start to worry about how much actual work we are doing in PL/SQL.
If you are working with Oracle Text, you may also observe the “enq: UL – contention” wait event. DBMS_LOCK is used internally by ctxsys.drvdml.com_sync_index, so requesting UL is not a problem. However, fragmenting the index composition table of CTXSYS increases the acquisition time of UL enqueue, so this issue will appear.
Bottom line: DBMS_LOCK should be used (carefully) in the application code only when there are no other options.
Read MoreJulian Dontcheff’s Database Blog
