How to set up EclipseLink Preallocation strategy
I am working on a project where we recently modified our persistence provider with OpenJPA to use EclipseLink. This is a large and old application in which we also insert SQL Inserts from other processes that are currently not available for JPA migration.
We use @TableGenerator to reference the table where we keep track of the IDs used for insertion.
When we used OpenJPA, we noticed that it first fetches the next id from the table and then updates the table to preallocate the next id. This is exactly the same as the old SQL process gets and predefines the next ID.
When we switched to EclipseLink we noticed the opposite behavior, it updates the table to pre-allocate the next ID and then start doing inserts. This calls us a java.sql.SQLIntegrityConstraintViolationException because the last preallocated ID was used by non-JPA processes to insert a new record, so when the JPA process reaches that ID, the database gives an error stating that we are trying to do an insert with already used identifier.
Is there a way to tell EclipseLink to handle pre-distribution the way OpenJPA does?
Here are some examples of pre-allocation strategy from OpenJPA vs EclipseLink, for these examples I set allocizeize to 5
OpenJPA
TEST TRACE [main] openjpa.jdbc.SQL - <t 22760146, conn 3658896> executing prepstmnt 9137209 SELECT NEXT_ID FROM ABC.table_ids WHERE TABLE_ID = ? FOR UPDATE [params=(String) 1034] [reused=0]
TEST TRACE [main] openjpa.jdbc.SQL - <t 22760146, conn 3658896> [94 ms] spent
TEST TRACE [main] openjpa.jdbc.SQL - <t 22760146, conn 3658896> executing prepstmnt 23999306 UPDATE ABC.table_ids SET NEXT_ID = ? WHERE TABLE_ID = ? AND NEXT_ID = ? [params=(long) 55, (String) 10, (long) 50] [reused=0]
TEST TRACE [main] openjpa.jdbc.SQL - <t 22760146, conn 3658896> [93 ms] spent
EclipseLink:
[EL Fine]: 2013-01-23 14:08:35.875--ClientSession(6215763)--Connection(10098848)--Thread(Thread[main,5,main])--UPDATE table_ids SET next_id = next_id + ? WHERE table_id = ?
bind => [5, 10]
[EL Fine]: 2013-01-23 14:08:36.0--ClientSession(6215763)--Connection(10098848)--Thread(Thread[main,5,main])--SELECT next_id FROM table_ids WHERE table_id = ?
bind => [10]
Thanks in advance!
source to share
The problem is not the update order versus selection, but the interpretation of whether the current value is present or not. EclipseLink assumes it is getting the current value, where OpenJPA appears to be failing.
Ideally, you could change your non-JPA usage to make the same assumption. If you can't, you can write your own Sequence object in EclipseLink.
To do this, subclass TableSequence and override the buildSelectQuery () method to add "+1" (or "-1") to SQL to accommodate the difference in assumption.
Then you can add your custom sequence using the SessionCustomizer.
Please also file a bug with EclipseLink to add a compatibility option for OpenJPA sequencing.
source to share