winsoftech
asked on
How to Use NOWAIT with UPDATE Queries
Hi Experts,
I have two processes running and both want to update the same record. The First process sends a packet to the second one containing the update metarial for the record the second process updates. The First process then enters a loop, trying to update the same record. But the complicacy is that one of the udate condition is contained in the update material sent to second process. That means the second process should update first and then the first process. But here the first process just sends the update metarial packet and acquires the lock on the record to update.
On getting the packet, the second process tries to update the record with the data it recieved. but it fails as it is already locked by another process. I tried to insert artificial wait in the first process so that the second one gets enough time to update the record with the data it received, but time taken by the Oracle to try acquire a lock on database object is greater than the artificial lock. The artificial wait introduced here can not be extended as it is a critical process and warrants real time response. Now the second process fails to update and timeouts.
I want to use the NOWAIT option for Updates available in Oracle, so that it does not wait if the database object is locked by another process. By the time the first process will loop back and wait for some time. Meanwhile the NOWAIT UPDATE will also terminate without waiting to lock the object and will loop back.
Kindly tell me how to use NOWAIT with Udates.
Regards,
Satya
I have two processes running and both want to update the same record. The First process sends a packet to the second one containing the update metarial for the record the second process updates. The First process then enters a loop, trying to update the same record. But the complicacy is that one of the udate condition is contained in the update material sent to second process. That means the second process should update first and then the first process. But here the first process just sends the update metarial packet and acquires the lock on the record to update.
On getting the packet, the second process tries to update the record with the data it recieved. but it fails as it is already locked by another process. I tried to insert artificial wait in the first process so that the second one gets enough time to update the record with the data it received, but time taken by the Oracle to try acquire a lock on database object is greater than the artificial lock. The artificial wait introduced here can not be extended as it is a critical process and warrants real time response. Now the second process fails to update and timeouts.
I want to use the NOWAIT option for Updates available in Oracle, so that it does not wait if the database object is locked by another process. By the time the first process will loop back and wait for some time. Meanwhile the NOWAIT UPDATE will also terminate without waiting to lock the object and will loop back.
Kindly tell me how to use NOWAIT with Udates.
Regards,
Satya
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
http://www.wisc.edu/drmt/oratips/sess004.html
You cannot use NOWAIT with updates, at least not directly. You can though use NOWAIT with select statements, then if the select succeeds, you can do the update and an immediate commit to release the lock so another process can update the same row.
Here is part of one of our procedures that uses that technique:
-- sub-procedure to update line:
procedure chg_line as
cursor c_lock is select rowid from box_lines
where box_nbr = v_box
and box_line_nbr = v_lin
for update of qty_moved nowait;
box_row rowid;
begin
-- Check if box_line is locked, and skip this record for now if it is:
open c_lock;
fetch c_lock into box_row;
close c_lock;
update box_lines
set qty_moved = v_qty
where rowid = box_row;
commit;
exception
when others then
[handle the exception however you want it handled]
if c_lock%isopen then
close c_lock;
end if;
end;
Here is part of one of our procedures that uses that technique:
-- sub-procedure to update line:
procedure chg_line as
cursor c_lock is select rowid from box_lines
where box_nbr = v_box
and box_line_nbr = v_lin
for update of qty_moved nowait;
box_row rowid;
begin
-- Check if box_line is locked, and skip this record for now if it is:
open c_lock;
fetch c_lock into box_row;
close c_lock;
update box_lines
set qty_moved = v_qty
where rowid = box_row;
commit;
exception
when others then
[handle the exception however you want it handled]
if c_lock%isopen then
close c_lock;
end if;
end;
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
To: winsoftech
It has been almost a week since you posted this question and we haven't heard anything from you. Did some of these comments help you? Are you hoping for more input here? Did you get this problem solved?
It has been almost a week since you posted this question and we haven't heard anything from you. Did some of these comments help you? Are you hoping for more input here? Did you get this problem solved?