Database Reference
In-Depth Information
DBMS_LOCK.ALLOCATE_UNIQUE(
lockname => 'MYAPP_MUTEX1',
lockhandle => :lockhandle
);
END;
/
print lockhandle
LOCKHANDLE
-----------------------
10737418641073741864187
There's now a new row in table
SYS.DBMS_LOCK_ALLOCATED
.
SQL> SELECT * FROM sys.dbms_lock_allocated /* no public synonym */;
NAME LOCKID EXPIRATION
---------------------------------------- ---------- --------------
DROP_EM_USER:SYSMAN 1073741824 13.03.07 17:45
ORA$KUPV$MT-SYSTEM.SYS_EXPORT_SCHEMA_01 1073741844 09.03.07 15:51
ORA$KUPV$JOB_SERIALIZE 1073741845 09.03.07 15:51
ORA$KUPM$SYSTEM$SYS_EXPORT_SCHEMA_01 1073741846 09.03.07 15:48
MYAPP_MUTEX1 1073741864 20.07.07 19:52
As is obvious from the other entries above, the ORACLE DBMS uses
DBMS_LOCK
for internal
purposes.
SQL> VARIABLE result NUMBER
SQL> BEGIN
-- request the lock with the handle obtained above in exclusive mode
-- the first session which runs this code succeeds
:result:=DBMS_LOCK.REQUEST(
lockhandle => :lockhandle,
lockmode => DBMS_LOCK.X_MODE,
timeout => 0,
release_on_commit => TRUE /* default is false */
);
END;
/
SELECT decode(:result,0,'Success',
1,'Timeout',
2,'Deadlock',
3,'Parameter error',
4,'Already own lock specified by id or lockhandle',
5,'Illegal lock handle') Result
FROM dual;
RESULT
-------
Success
The second session (the initial call to
DBMS_LOCK.ALLOCATE_UNIQUE
is omitted) may then run
the same code.
