Re: Is the row updated since or while I am looking at it?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 23 Dec 1998 14:30:50 GMT
Message-ID: <3680fd34.65781098@192.86.155.100>

A copy of this was sent to David Spaisman <davedba_at_intercall.com> (if that email address didn't require changing) On Tue, 22 Dec 1998 22:06:48 -0800, you 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"?
>

if you read the row out with a simple "select ... from t" (eg: with no FOR UPDATE clause) then the only way to determine if the row was modified is to requery it. For example, here is the logic Oracle Forms uses to do lost update prevention (auto locking)..

1- when you query the data, forms ALWAYS reads the rowid column or the primary key. You would add the primary key or rowid to all of your queries that needed to do this check. That is, instead of just "select ename, job from emp" you would query "select emp.rowid rid, ename, job from emp"

2- when you want to see if the row has been updated AND to get a lock on it (suggest you get the lock -- you must be going to update the row if you care if its been updated I assume) you would issue:

   select 1
     from emp
    where rowid = YOUR_RID_YOU_ALREADY_SELECTED

      and ename = YOUR_ENAME_YOU_ALREADY_SELECTED
      and job = YOUR_JOB_YOU_ALREADY_SELECTED

FOR UPDATE NOWAIT one of 3 things will happen here

you will get 1 row back. in this case the row has not changed and you have an exclusive lock on it.
you will get 0 rows back. The data has CHANGED in the database, you need to requery to get the current data.
you will get an error -- someone else has the row locked.

>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?
>

Nope, you just need to keep the primary key (the rowid being a very fast row locator you can use as well)

>Is there an initialization parameter relating to read_consistency that
>is set by default? Are there other options other than read_consistency?
>

all queries are read consistent. you can make entire transactions read consistent by setting the isolation mode to serializable.

>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?
>

you cannot.

>Any information which you can provide would be greatly appreciated.
>Thanks.
>
>David Spaisman
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Dec 23 1998 - 08:30:50 CST