The ALLOCATE_UNIQUE stored procedure requests a unique lock ID in the range of 1073741824 to 1999999999 for a specified lock name. Lock IDs are used to enable applications to coordinate their use of locks. It is easier for applications to coordinate their use of locks based on lock names than based on lock IDs.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
DBMS_LOCK.ALLOCATE_UNIQUE (
lockname IN VARCHAR2,
lockhandle OUT VARCHAR2,
expiration_secs IN INTEGER DEFAULT 864000);
Parameters
| Parameter | Description |
|---|---|
| lockname | The lock name. Do not use lock names beginning with ORA$. |
| lockhandle | Returns the lock handle generated by ALLOCATE_UNIQUE. You can use this handle in subsequent calls to REQUEST. Lock handles obtained by any session by calling ALLOCATE_UNIQUE with the same lock name point to the same lock. lockhandle can be up to VARCHAR2 (128). |
| expiration_secs | The number of seconds to wait after the last ALLOCATE_UNIQUE has been performed on a specified lock, before that lock can be deleted from the DBMS_LOCK_ALLOCATED table. The default waiting period is 10 days. We recommend that you do not delete locks from the DBMS_LOCK_ALLOCATED table, because subsequent calls to ALLOCATE_UNIQUE will automatically delete expired locks. |
Considerations
When the first session calls ALLOCATE_UNIQUE with a new lock name, a unique lock ID is generated for the lock name and stored in the DBMS_LOCK_ALLOCATED table. Subsequent calls ALLOCATE_UNIQUEby sessions return the lock ID previously generated.
The mapping between a lock name and the return lock ID lasts for at least the period of time specified by expiration_secs. After this period of time, the mapping will be deleted from the DBMS_LOCK_ALLOCATED table to release space. Therefore, upon expiration, another lock will be returned for a call to ALLOCATE_UNIQUE with the same name.
ALLOCATE_UNIQUE performs a commit, which is different from ALLOCATE_UNIQUE_AUTONOMOUS.
Exceptions
The following error message may be returned: ORA-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