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?
When JPA preallocates generated ids, it updates the counter row in the table. So, if JPA has 30-35 cached, then the table will be 35.
When you want to use the same table in your JDBC code, you need to ensure you lock the rows correctly (even for you own JDBC application with itself).
You must either UPDATE the counter first, then selects it, or select it for update, and the update it. If you don't lock the row, then you have a concurrency hole.
This may be what you are seeing, or it may just be a +1 issue in the id (i.e. you use 35 instead of 36 as the next id).