The ALLOCATE_UNIQUE stored procedure is used to request a unique lock ID for a lock name. The lock ID ranges from 1073741824 to 1999999999. Lock IDs are used to coordinate lock usage among applications. Locking by name is more convenient than locking by ID.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Syntax
DBMS_LOCK.ALLOCATE_UNIQUE (
lockname IN VARCHAR2,
lockhandle OUT VARCHAR2,
expiration_secs IN INTEGER DEFAULT 864000);
Parameters
| Parameter | Description |
|---|---|
| lockname | Specifies the lock name. Note that the lock name must not start with ORA$. |
| lockhandle | Returns the lock handle generated by ALLOCATE_UNIQUE, which can be used for REQUEST operations. Any session that uses ALLOCATE_UNIQUE with the same lock name will receive the same lock handle, pointing to the same lock. The maximum length of lockhandle is VARCHAR2 (128). |
| expiration_secs | Specifies the retention period for records of locks allocated by ALLOCATE_UNIQUE in the DBMS_LOCK_ALLOCATED table. During this period, the lock mapping will not be cleared. The default timeout is 10 days. ALLOCATE_UNIQUE automatically clears expired locks, so you should not manually delete data from the DBMS_LOCK_ALLOCATED table. |
Considerations
The first session to use ALLOCATE_UNIQUE to request a handle for a lock name will generate a unique lock ID for that name and save it in the DBMS_LOCK_ALLOCATED table. Subsequent sessions using the same lock name with ALLOCATE_UNIQUE will return the previously generated lock ID.
The mapping between lock names and lock IDs is valid for the duration specified by expiration_secs. After expiration, the mapping in DBMS_LOCK_ALLOCATED will be cleared to free up space. Therefore, after expiration, a lock requested with the same name will be a different lock.
ALLOCATE_UNIQUE executes the COMMIT command, which is a key difference from ALLOCATE_UNIQUE_AUTONOMOUS.
Error messages
The following error message may be returned: OBE-20000, ORU-10003: Unable to find or insert lock <lockname> into catalog dbms_lock_allocated.
Examples
obclient> DELIMITER /
obclient> DECLARE
my_lockname VARCHAR2(100) :='test01';
my_lockhandle VARCHAR2(200);
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE(lockname => my_lockname,lockhandle => my_lockhandle);
END;/
Query OK, 1 row affected