aspose file tools*
The moose likes Object Relational Mapping and the fly likes Update JPA cache when updating  last_value in JPA_GENERATED_KEYS TABLE form NON_JPA code Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Update JPA cache when updating  last_value in JPA_GENERATED_KEYS TABLE form NON_JPA code" Watch "Update JPA cache when updating  last_value in JPA_GENERATED_KEYS TABLE form NON_JPA code" New topic
Author

Update JPA cache when updating last_value in JPA_GENERATED_KEYS TABLE form NON_JPA code

arfeen khan
Greenhorn

Joined: May 13, 2011
Posts: 25
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?

Thanks.
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
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).


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Update JPA cache when updating last_value in JPA_GENERATED_KEYS TABLE form NON_JPA code
 
Similar Threads
executing an update sql takes too long , what is problem ?
Update based on Row id
Standard way to handle Repository in JPA
soft delete functionality
Hibernate cache problem?