Re: Has the row been updated since owhile I am looking at it?

First, I'd have to seriously question why this is needed. If two programs are
updating the same rows simultaneously, one of them will block until the other finishes. When the second find that there was an update applied before its update, how will it decide what to do? If there is some human interaction involved in deciding what to do, then you may have a chance at doing the right thing.

Now given that you still want to do this, perhaps you currently use logic something like this:

     SELECT many rows
     decide that a row needs updated
     check whether someone else got there first
         if no other update occurred
             update that row
         else resolve the conflicting updates

It's the check step that you have trouble with, right? Well, you can use the SELECT...FOR UPDATE; to get into the state you want. Assume the data for your update is MYROW, the current value of that row is HISROW, and the original value was ONEROW. then

     SELECT ROW into ONEROW from THETABLE;
     ---   decide that row needs updated
     MYROW = modified(ONEROW);
     ---   check whether someone else got there first
     SELECT ROW into HISROW from THETABLE
         WHERE rowid = rowid_of(ONEROW)
         FOR UPDATE;
     IF ONEROW = HISROW
     THEN
     --- no update happened, so we own the row. update it.
          UPDATE THETABLE SET ROW = MYROW
             WHERE rowid = rowid_of(ONEROW);
          COMMIT; --- do this so locks released and
           ------------- so other processes can see our update.
     ELSE
          --- resolve the conflicting updates
          --- you are on your own here, it's too application specific.
     END;

So the key is the COMMIT step. Oracle isolates sessions from changes not yet commited to the database (as all good RDMS's should). Without the COMMIT there really isn't much you can do without going to something like a Batch based architecture (ie, write changes to a "pending changes" table with a background process that handles the updates sequencially, and the check step would look in that "pending changes" table.)

I hope this helps. But in closing, I'd have to strongly suggest since this is a
new application, you really take some time and think about whether this is a

design that you really want to use. Ask the question: why is this needed? You are looking for a solution to a race condition. The best solution is to eliminate the race condition itself.

Good luck and Merry Christmas!

   ed

David Spaisman wrote:

> We are about to start developing some new applications on Oracle 8.0.4
> on NT 4.0, SP3.
>
> One of the developers I work with asked me the following question as he
> needs to determine how programmatically how he will handle this and what
> does Oracle provide, specifically, for this?
>
> The question posed was: "How do I know if a row was updated since or
> while I am looking at it"?
>
> So the question does Oracle use some sort of internal timestamp to track
> the row if it was updated or does the programmer need to set some of
> counter/indicator to track this?
>
> Is there an initialization parameter relating to read_consistency that
> is set by default? Are there other options other than read_consistency?
>
> The Oracle documenatation only speaks about this very generally.
>
> What I am looking for is other than the locking mechanism. BTW, how do I
> tell programmatically which lock is held on a specific row?
>
> Any information which you can provide would be greatly appreciated.
> Thanks.
>
> David Spaisman
Received on Wed Dec 23 1998 - 10:09:26 CST