• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11770
  • Last Modified:

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
0
winsoftech
Asked:
winsoftech
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Mark GeerlingsDatabase AdministratorCommented:
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;
0
 
KJltCommented:
I think NOWAIT is not a good choice in this case. With NOWAIT you will get timeout error immediatly without waitting (that NOWAIT means).

I don't know how does your procesess communicate between, but i think the better solution would be if the second process would send the message to the first process about finishing the update, and only then the first process should start his updates.

The second solution - the first process should monitor if there are some expected changes on the row he have to update and only then do his job.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now