Implementing optimistic concurrency on a legacy database

Question

I have a database with some tables and also data in them.I need to implement for all tables optimistic concurrency.

I was wondering what would be the best way.

Query with a predicate will be created on the application side.

My concern is how-to store the rowversion(timestamp) value.

First I was thinking of using ora_rowscn for rowversion value,but then I realised I have to recreate all tables to set up ora_rowscn. Maybe just adding a some kind of timestamp column would be good,but then I would be forced to create and save a new timestamp value for every update in the application.

Any ideas ?

Answer 1

Oracle has a built-in package for optimistic locking called OWA_OPT_LOCK. This can be used to generate a checksum for any row like this:

select owa_opt_lock.checksum('SCOTT','EMP',ROWID)
from emp
where empno = 123;

This can be called when originally getting the record and again before saving the changes; if the 2 values are different, someone else has changed the record since you got it.

Answer 2

A very simple but effective pattern is to first fetch the whole row about to be edited without keeping a lock. When you are finally ready for the update, augment the where clause with clauses like COLUMNA='OLDVALUEA'. The number of changed records indicates if some other modification interfered with your optimistic update or not.

This method will notice all modifications that are still in effect when you try update. Any method relying on checksums can falsely indicate that no modifications have taken place. The reliability you need depends on your application. I would not bet human lifes on checksums. However, I would try not to rely on optimistic updates in this case either.