OptimisticLockException Data from Table not getting Refreshed

[icodemee]

Consider the below scenario:

Application uses eclipselink(JPA) to manage the updates in Database(oracle). Scope of the application is to take an ID from the incoming request and fetch the entire record from the DB for that ID. After few validations, update the same record with new values and commit the transaction. Each of the record in the DB has a REC version. i.e for each update to the record, the REC version is automatically incremented, based on which the number of updates to a record can be found out.

However during the update OptimisticLockException is thrown.

To explain further when these two web service requests are fired, below happens:

• Client A web_service_request_1- > Reads entity from DB with REC_VERSION value as 0- when it tries to update the related record - > First it compares that the REC_VERSION field has not changed to detect locking conflicts. If not, DB update will happen successfully. This will also update the REC_VERSION field for e.g. it will update REC_VERSION value 0 to 1 -> Successful.

• Client A web_service_request_2- > Reads entity from DB with REC_VERSION value as 0-when it tries to update the same record (updated last by web service request 1), first it compares that the REC_VERSION field has not changed to detect locking conflicts. -> But the REC_VERSION version field was already updated in the DB (from 0 to 1) by the web service request 1 . so in this case, conflict happens and this OptimisticLockException was thrown.

The reason for web_service_request_2 still getting old REC_VERSION value as 0 is unknown. Since the web_service_request_1 updated the REC_VERSION from 0-1, the web_service_request_2 should have got the latest REC_VERSION as 1 as updated by the web_service_request_1. Since this didn't happen OptimisticLockExpcetion is being thrown.

From Oracle Logs we could verify that web_service_request_1's commit was successful there by updating the values of the record and the REC_VERSION itself. However the eclipselink JPA layer is not able to get the latest update from the DB.

If the failed request is sent again after 10 mins to the same application, it passes and updates the DB without any issues.

Log:
Oct 05, 2023 5:27:11 PM org.eclipse.persistence.default.sql
FINE: UPDATE CONTRACT_ALL SET CO_MODDATE = ?, REC_VERSION = ? WHERE ((CO_ID = ?) AND (REC_VERSION = ?))
bind => [2023-10-05 17:27:11.498, 4, 2721830, 3]
Oct 05, 2023 5:27:11 PM org.eclipse.persistence.default
WARNING:
Local Exception Stack:
Exception [EclipseLink-5006] (Eclipse Persistence Services - 2.1.3.v20110304-r9073): org.eclipse.persistence.exceptions.OptimisticLockException
Exception Description: The object [com.lhs.Contract.bscs_core.Contract@23855c6] cannot be updated because it has changed or been deleted since it was last read.
Class> com.lhs.Contract.bscs_core.Contract Primary Key> 2,721,830
at org.eclipse.persistence.exceptions.OptimisticLockException.objectChangedSinceLastReadWhenUpdating(OptimisticLockException.java:137)

We understand that OptimisticLockException is thrown to prevent "update at all cost" and there by maintaining data integrity when parallel updates happen in the table.

However in this scenario no parallel updates are being made to the table only one request at a time is being fired.

As we checked there was no network loss/interruption between JPA and the DB. (Suspected network problem because updates went if there is a 10 min gap between subsequent request)

Is there any setting that we need to set a eclipselink level to refresh the object from the DB? Or any configuration to handle it?

[rfelcman]

I think, that this issue is fixed by
#2248 [master 5.0.0-SNAPSHOT]
or
#2250 [2.7 branch]
if not please let me know there.