How to Use NOWAIT with UPDATE Queries

Posted on 2006-03-21
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.

Question by:winsoftech
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    LVL 34

    Expert Comment

    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;
    LVL 2

    Accepted Solution

    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.
    LVL 34

    Expert Comment

    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?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now