| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Has the row been updated since owhile I am looking at it?
I, too, prefer select FOR UPDATE. However, if the nature of the application lets the user view the queried data, ponder it over lunch (or over the weekend!), and finally make changes, then we would be holding locks for a long while.
Checking for unchanged blocks by comparing full row content with a saved version of the row can sometimes involve lots of data transfer and local storage (like with LONG columns. Another solution is to use triggers to update a timestamp / sequence for every update. Then you only need to see if the timestamp / sequence has changed.
MotoX wrote:
Yep, I'd tend to agree with most of that. I've normally used theReceived on Wed Dec 23 1998 - 19:27:08 CST
SELECT FOR UPDATE OF clause.Howveer, it's interesting to see how tools used on databases other
than Oracle resolve this. PowerBuilder is a good example (which, of
course, also supports Oracle).You can choose to have the row checked before the update occurs at
three levels: primary key check, primary key and updateable colums,
and finally all columns. PowerBuilder performs the check on the row
columns before it attempts the updates, and if the requested check
items have changed the row will not be updated and an error is
returned.Not saying the above is the best solution, but it is workable, and may
be a suitable alternative to investigate if the original poster
doesn't want to use SELECT FOR UPDATE OF, for some reason.MotoX.
(Oracle7 and Oracle8 Certified Pro.)
On Wed, 23 Dec 1998 11:09:26 -0500, Ed Prochak
<edprochak@interfacefamily.com> wrote:>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
 |
 |