Very Computer

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

(if that email address didn't require changing)

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.

Quote:>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)

Quote:>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.

Quote:>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.

Quote:>Any information which you can provide would be greatly appreciated.
>Thanks.

>David Spaisman

Thomas Kyte

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.

Two approaches to this problem are typical:

- When you select the row to work on it, issue a "select ... fur update;"
  This locks the row so that other people trying to work on the row will
  have to wait until the person locking it is done.

- After selecting the row and changing it, "select ... for update;" the
  row again just before updating it, to verify that no changes have been
  made while the application was playing with the row. If the data has
  changed out from underneath the user, they have to start over.

It is generally felt that #2 is a better approach, because #1 allows big
locking pileups to occur is a heavy OLTP envirenment.  For instance, if an
application has selected several other rows in other tables for update,
but then has to wait on a lock, others may come along and have to wait on
the rows that the first waiting session is holding. This has a cascading
effect that results in big locking pileups. If the person at the top of
the lock chain goes out for a smoke, a DBA has top come along and kill the
blocking session off. This does not exactly promote "smooth operation."

--
Jeremiah Wilton      http://www.wolfenet.com/~jeremiah