(if that email address didn't require changing)
>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:
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
- 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.
>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.
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.