Search Results allocate_unique

Overview

SYS.DBMS_LOCK is an Oracle-supplied PL/SQL package that provides an interface to the Oracle RDBMS lock management services. It allows a session to request, convert, and release user-defined locks that are managed by the same lock infrastructure the database itself uses. In the context of Oracle E-Business Suite 12.1.1 and 12.2.2, the package is not part of the EBS application schema logic itself but is a prerequisite utility that EBS and custom code rely upon to serialize access to shared resources.

All lock identifiers created by this package are prepended with the "UL" prefix, ensuring that user-defined locks do not conflict with the internal DBMS locks. Because these locks are registered with the RDBMS lock manager, they appear in the standard lock monitor screens and fixed views, and deadlock detection is performed against them. Locks are released automatically upon session termination, and lock identifiers fall in the range 0 to 1073741823; the identifier space from 1073741824 to 1999999999 is reserved for lock identifiers generated via ALLOCATE_UNIQUE.

Within the ETRM 12.2.2 metadata, DBMS_LOCK is owned by SYS and classified as OTHER. It exposes nine documented entry points and is referenced by 55 other packages, which illustrates its role as a foundational concurrency primitive for EBS-adjacent code.

Key Procedures and Functions

• ALLOCATE_UNIQUE — Allocates a unique lock identifier given a lock name. The first session to call it with a new name causes a unique lockid to be generated and stored in the DBMS_LOCK_ALLOCATED table; subsequent calls return the previously generated lockid. The name-to-lockid association is retained for at least the expiration interval (default ten days) past the last call, after which the row may be removed to recover space. This procedure performs a commit.
• ALLOCATE_UNIQUE_AUTONOMOUS — A variant of ALLOCATE_UNIQUE that executes in an autonomous transaction, allowing a unique lockid to be allocated without affecting the caller's transaction state. This is useful when the caller cannot tolerate an intervening commit.
• REQUEST — Requests a lock of a given mode, blocking or returning according to the requested semantics until the lock becomes available.
• CONVERT — Converts an existing lock from one mode to another, rather than releasing and reacquiring it.
• RELEASE — Releases a lock previously acquired by the session.
• SLEEP — Causes the calling session to suspend execution for a specified interval. Because the timestamp of the lock identifier space and sleep intervals are not published here, callers should consult the package specification for exact parameter semantics.

Tables Accessed

The documented table referenced by this package is DBMS_LOCK_ALLOCATED. It stores the mapping between a lock name supplied to ALLOCATE_UNIQUE and the unique lockid generated for it. Rows persist for at least the expiration interval after the last call for that name, after which they may be deleted to reclaim space. No other tables are documented in the ETRM excerpt.

Usage Notes

DBMS_LOCK is typically invoked from PL/SQL — EBS concurrent programs, database triggers, forms-based server-side code, and customizations — where a logical unit of work must be serialized across sessions. The common pattern is to name a resource, resolve a stable lockid through ALLOCATE_UNIQUE, then request, convert, or release that lock as needed. SLEEP is often used to pace retry loops or to stagger concurrent processing. Because locks vanish at session end and deadlocks are detected by the RDBMS, the package is safe to use without explicit cleanup in most cases. Note that ALLOCATE_UNIQUE commits, so it should not be called inside a transaction that must remain atomic; use ALLOCATE_UNIQUE_AUTONOMOUS where a commit would be disruptive. Execution privilege on DBMS_LOCK is not granted to PUBLIC by default and must be explicitly granted.