Alas, the ora_rowscn approach is not going to work very well. It still enables "lost updates".
I have my information from AskTom: ORA_ROWSCN for optimistic locking
Copied example here to anticipate dead links...
What you should do IMHO, is to add an extra column (eg: Version_Number) to your table where you set the nextval of a sequence on update of a record (using a trigger, or do this in the package code).
WorkFlow: Client queries some rows and obtains the data with the version_Number. When updating this data he needs to check the version_number with the current version_number in the record. If they differ, no row will be updated and you should return an error to the client (No_Records_Found).
You see something like this:
As I said, you should notify the client that there was no record updated, but this could have 2 reasons:
In both cases the client has to refresh this record again and determine what to do next.
Typically I think that systems would select the ora_rowscn value when they read the data, and when they write it back they would specify that value as part of the update statement. If no rows are updated then either the record has been deleted or the record has changed.
|show 1 more comment|