ORA_ROWSCN pitfall for optimistic locking

I can see lately that there's a big enthusiasm around this new 10g pseudo-column called ORA_ROWSCN, one of its great benefit being for implementing an optimistic locking mechanism. From a simplistic point of view, this mechanism consists of:

1.on the application server or on the client side we have an entity class which is mapped on a particular table. Instances of that class (objects) reflect rows on that particular table.
2.Every entity object has also a version field which maps to the ORA_ROWSCN pseudo-column.
3.At the very beginning, we are loading our entities using SELECT T.*, ORA_ROWSCN FROM TABLE T so that every entity has the corresponding values and version.
4.Now, suppose that the user changes some attributes from the entity through the application front-end. This changes have to be propagated into the database, most likely using an UPDATE statement, but taking care of the version field. That is, we need to be sure that meanwhile, the version of the entity we are about to persist didn't change within the database. So, all is narrowing down on performing something like : UPDATE TABLE set COLUMN = [value from the entity] where PK_COLUMN = [id from the entity] and ORA_ROWSCN = [version from the entity]. If the above UPDATE will return “0 rows updated” we'll know for sure that somebody else has changed the record into the database and the application will throw an “Optimistic Lock Exception”.

So, everything seems to work nicely: no more timestamp version columns, no more OLD and NEW column values comparisons, no more row hashes. In 10g, it seems that Oracle offered us a bullet proof solution. BUUUT, there's a pitfall here. Every decent application is working with transactions therefore every DML is nicely encapsulated into an Oracle transaction. What happens if on the 4th step our update succeeds? Well, in this case the version of the entity will have to be updated accordingly. But, our update is not yet committed therefore we don't have access to the new value of the ORA_ROWSCN which will be available only after COMMIT. This means that, in order to refresh our entity, we'll have to issue an SQL-SELECT after COMMIT, something like: SELECT T.*, ORA_ROWSCN FROM TABLE T WHERE PK_COLUMN=[id from the entity]. The main problem here is the fact that between our COMMIT and the SQL-SELECT there is a tiny time frame within which a lot of nasty things may happen: somebody else may delete our record or may update it once again. Furthermore, the fact that I have to issue an SQL-SELECT after the UPDATE doesn't sound to me very appealing as involves additional work for the database and might affect the whole scalability. In addition I would really like to use the RETURNING clause of the INSERT/UPDATE statements and to refresh my entity in one step.

In conclusion, if you are designing an WEB application which doesn't reuse entities across WEB pages then using ORA_ROWSCN for implementing optimistic locking is fine as long as the working flow is the following:

1.the user select the record he/she wants to change from a grid;
2.the application loads the entity and assigns the ORA_ROWSCN value as version;
3.the user performs the required changes and press the SAVE button;
4.the application performs the UPDATE into the database taking care of the ORA_ROWSCN values (WHERE ... and ORA_ROWSCN=[version from the entity]) and COMMITs the changes.
5.the application display once again the initial grid re-querying the database.

However, if you are designing an application which is required to take advantage of caching (most likely a stand-alone rich client application) then the ORA_ROWSCN is not the right solution. For example:

1.the application loads a grid and every grid record has the corresponding entity attached;
2.the user select the record he/she wants to change from the grid;
3.the application relies on the data from the corresponding entity therefore it doesn't query the database;
4.the user performs the changes and press the SAVE button;
6.the application performs the UPDATE into the database taking care of the ORA_ROWSCN values (WHERE ... and ORA_ROWSCN=[version from the entity]) and COMMIT the changes.
7.If the UPDATE returns “1 record updated” then our entity is stale because the new version from the database doesn't reflect the old one from the entity. A new SELECT, outside of the UPDATE transaction is required in order to refresh the entity which, of course, is not convenient at all.

So for this second kind of application I would use a regular version-column or hashing techniques which allows to use the RETURNING clause of the INSERT/UPDATE statements or to refresh the entity within the same modifying transaction.