We help IT Professionals succeed at work.

How to Use NOWAIT with UPDATE Queries

winsoftech asked
Medium Priority
Last Modified: 2009-10-08
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.

Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Mark GeerlingsDatabase Administrator

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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mark GeerlingsDatabase Administrator

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?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.