oracle.jbo.RowInconsistentException (JBO-25014)

oracle.jbo.RowInconsistentException: JBO-25014: Another user has changed the row with primary key oracle.jbo.Key[SI00892 FU9897O]

 After a lot of pondering, head banging and googling I somehow removed this exception and the following post is the collective summary of the solution.

This exception may be caused when some other user changes the database entry you are currently working on and by the time you commit the changes ,this exception occurs as the value was already changed in the database.

The basic scenario for such exception -

 1.You called the commit method.The particular EO instance is marked as changed. (Optimistic locking is enabled)

2.The current values of the corresponding row in the database do not match the original values queried for the row .

So if your ADF-based application is getting this exception, then the reason is either one of the following:

1. Some other user has actually updated the row since you retrieved it from the database.
2. Your application may contain a problem that makes BC4J think that the row has been changed when logically no other user has really changed it. 

So in such case RIE is thrown. Well , in case another user has changed the row then this exception is required, keeping in mind the Data Integrity point of Database Transactions .


But what if this exception is thrown when no other user actually changed anything on that particular row ? For instance in a development environment , where developers are using their own database , and no other user could possibly be using the database records. Thus arises the problem of Phantom User .

The common reasons for which this error can be thrown when logically you believe it should not, are:

1. You have database triggers modifying the values of some of the columns on which your entity's persistent attributes are based, and you have failed to correctly indicate that to BC4J with the "Refresh on Insert" and/or "Refresh on Update" flags on the entity object attribute definition.

When we insert or update a record in the database via ADF, ADF keeps a copy of the record written to the database. However the cached record is instantly out of date as the database triggers have modified the record that was actually written to the database. Thus when we update the record we just inserted or updated for a second time to the database, ADF compares its original copy of the record to that in the database, and it detects the record has been changed – giving us JBO-25014.

2. You have (or have added) database level column default values for attributes and failed to correctly indicate that to BC4J with the "Refresh on Insert" property for the corresponding attribute.

3. You have created a view object with "Expert Mode" custom SQL statement, and have gotten the SELECT list of your custom SQL statement out of sync with the view object attributes.

4. You are using an attribute whose type is a custom domain class and have failed to correctly implement the equals() method for your custom domain class.

5. Your are using a ROWID-valued attribute in your entity and have not marked it to "Refresh on Insert" and "Refresh on Update". Some features of the database like partitioning can cause the value of ROWID to be updated when data in the row is updated.
   
   
6. If the attribute which is inconsistent is actually the accessor for an association, in particular, an association that is a composition with “Lock Top-Level Container” set on it i.e, when any composed entity is locked, the containing entity gets locked too. So if we try to acquire a lock on one of the 'composing' entity object instances, and someone else has changed the top-level containing row, we’ll get a RowInconsistentException on the top-level row even if we haven’t changed that row.

Solution :

=> In case it is caused by triggers ,set the refresh flags appropriately for any related entity object attributes that your triggers are changing.

=> Close any open RowSetIterators which have been used, as unlike Rollback where open Iterators are closed by framework itself, on Commit Iterators are not closed automatically by framework. So we need to explicitly close any open iterators we used.

=> Re-querying all View Objects on commit :

Though this is an expensive technique as it requeries every time after commit for all the view Objects of the AM.

1. <AppModule
2. xmlns="http://xmlns.oracle.com/bc4j"
3. Name="AppModuleAM"
4. Version="11.1.2.60.81"
5. ClearCacheOnRollback="true"
6. RequeryOnCommit="true"
7. ComponentClass="myApp.model.module.AppModuleAMImpl"
8. ComponentInterface="myApp.model.common.AppModuleAM"
9. ClientProxyName="myApp.model.client.AppModuleAMClient" 

=> Another way is to execute the View Object after the commit operation to bring back updated data to ViewObject. For this we need to override the afterCommit() of ViewObject class as follows:

1. public void afterCommit(TransactionEvent){
2. executeQuery();
3. }

This would call the view Object's executeQuery() method which would bring the latest data from the database thus avoiding any data mismatch between the cached copy and the original data from the database.

=> In case the entity attribute accessor is the culprit we should refresh the values in the overridden lock() method as follows:

1. public void lock() {
2. try {
3. super.lock();
4. } catch (RowInconsistentException e) {
5. refresh(REFRESH_WITH_DB_ONLY_IF_UNCHANGED | REFRESH_CONTAINEES);
6. super.lock();
7. }
8. }

References:

1. http://www.avromroyfaderman.com/2008/05/bring-back-the-hobgoblin-dealing-with-rowinconsistentexception/

2. http://jobinesh.blogspot.in/2011/02/yet-another-reason-for-jbo-25014.html

3. https://blogs.oracle.com/onesizedoesntfitall/entry/the_case_of_the_phantom