In the system, let say there are two different tools are available, Tool-A_Uses_JPA and Tool_B_Uses_JDBC.
As the name of tools is self explanatory what persistence technology used by tools.
Say,there is a table COMMON_TABLE. This can be used by both tools to perform CRUD operation.
Lets see the problematic scenario-
Tool_B_Uses_JDBC tries to insert some new rows in COMMON_TABLE, by performing following tasks--
(Tool_B_Uses_JDBC already calculated how many rows will be inserted into COMMON_TABLE. Let's say it is 5.)
1. Read JPA_GENERATED_KEYS table for getting last_value for COMMON_TABLE. Let's say it is 30.
2. Add (last_value + number of rows to be inserted), here it 30+5 =35.
3. Update the JPA_GENERATED_KEYS table for last_value of COMMON_TABLE by the value calculated in step 2.
In my example it was updated by 35.
Above three steps done in a single transaction.
Now, ideally if any other tool use this COMMON_TABLE to perform any insert operation, the last_value from
JPA_GENERATED_KEYS should be 35. But for Tool-A_Uses_JPA, JPA cache is enabled. So this tool still get
last_value as 30, rather 35. And hence Tool-A_Uses_JPA, does not work properly.
So to resolve this issue, i thought to write a code to update JPA cache while updating the last_value.
Is there any way to update and/or send notification to all other tools to update JPA cache when updating
last_value form NON_JPA code?
Or is there any other way to resolve this issues?
Marking this as answered, as this can never be a situation the way JPA uses caching. This was issue with the transaction form non-JPA code. Actually when JDBC uses data source then whether transaction will be committed as soon as application issues commit, is not guaranteed. Hence need to work on transaction.