Re: Usage of RowId...

"Brian K. Lawton" <NOSPAM.Lawton_at_RDAConsultants.Com> wrote (in <6gnqgc$86d$1_at_ha2.rdc1.md.home.com>)...

| If I'm trying to implement optimistic concurrency within my database, is the
| RowId attribute the correct place to be checking? If not, what is Oracle's
| equivalent to SQL Server's timestamp datatype?
|
| ________________________________________________
| Brian K. Lawton (mailto:Lawton_at_RDAConsultants.com)
| RDA Consultants Limited (http://www.RDAConsultants.com)
|
|

Hello,
  Get the row again (for update - you must try and lock the row) and check that the value in the columns you are changing are the same as when you first got them.
 OR
 Try and update the row using the original value of the columns you are updating in the 'where' clause. If no rows are updated then someone else changed it.

Commit after each row (or fairly soon), otherwise you prevent any other transaction changing the row.

Note: There is *no* way to prevent any other task reading the old value of a row. Oracle *always* allows reads of rows as they were before your transaction started. This may be good or horrifying depending on what you want to happen! To state it again in a different way: A query will *always* return all the rows you asked for at the time you asked for them! It may not be the latest version of the rows.

RowId has *no* relation to any timestamp. It may (on a good day) relate to whereabouts in a data file the first instance of the row was created. However, Oracle will find the required row if you use the rowid, unless the row has been deleted. If you are using Oracle version 7 and greater then the rowid will be valid for the lifetime of the row. that is my understanding anyway.

If SQL Server automatically timestamps rows then there is *no* automatic equivalent in Oracle. I am not sure how that helps to prevent losing updates. Look at database triggers to find out how to (maybe) duplicate similar functionality.

graham Received on Sat Apr 11 1998 - 11:37:27 CDT