Re: Checking Record has been changed?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 12 Oct 1998 17:26:57 GMT
Message-ID: <36263b3e.18261879@192.86.155.100>

A copy of this was sent to NBronke_at_t-online.de (Nicolas Bronke) (if that email address didn't require changing) On 12 Oct 1998 16:06:36 GMT, you wrote:

>Does exist a possibility (Oracle 7.3x) to check, whether the current record
>has been changed by another user?
>
>Kind regards
>Nicolas Bronke
>

The way most apps do this is via a select for update nowait by rowid. For example, when you first read the record out to the client, select the underlying rowid along with the other data you wish to display. Then, to check if the record has changed AND to get it locked for yourself, you would:

select null
  from T
where rowid = :the_rowid_you_fetched_earlier    and c1 = :the_column_data_you_fetched_earlier    and c2 = :the_other_column_data
   and .... (upto cN for your columns)
for update NOWAIT;

If that

succeeds and returns no rows, the row is not locked but has been changed
succeeds and returns a row, the row is now locked by you and no one else changed it
fails, the row is currently locked by some other session.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
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 Mon Oct 12 1998 - 12:26:57 CDT