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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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;
    -- 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;
    when others then
      [handle the exception however you want it handled]
      if c_lock%isopen then
        close c_lock;
      end if;
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.