Using DBMS_LOCK.RELEASE to release the lock owned by another process [message #10989] |
Sat, 28 February 2004 03:41 |
Sivakrishna
Messages: 2 Registered: February 2004
|
Junior Member |
|
|
Hi,
We are using dbms_lock package in our application to ensure that only one process instance is running.
Before the process starts, it gets the lock handle. The lockname is the process name <PROCESS>. Then it requests the lock.
The code I used:
var lock_handle varchar2(300);
var result number;
begin
dbms_lock.allocate_unique(<PROCESS>,:lock_handle);
:result:=dbms_lock.request(:lock_handle);
end;
It is working fine, as the first process instance requests the lock and when a second instance requests the lock, it is denied (meaning an instance is already running).
The problem is, in case the first process got hanged or the connection to database is lost due to network failure, it won't be able to release the lock. Another instance won't be started because the lock is still present. Can we release the lock manually logging to a sqlplus session?
I tried to do the following logging into a sqlplus session.
var lock_handle varchar2(300);
var result number;
begin
:result:=dbms_lock.release(:lock_handle);
end;
/
print v_result;
The output was:
V_RESULT
----------
4
which means - 'Do not own lock; cannot release'.
Please let me know what should be done to release this lock.
Thank you
|
|
|
|
|
|