Spring JPA - sequence caching gives unexpected behaviour. Using allocation size = 1 is ok

My Spring Boot application uses a.o. 2 Entity classes. Entity class 1 uses a technical key id that uses a sequence. The Entity contains a list of other Entities, so a one-to-many. The Child entity uses the same sequence.

Using a seqeuence allocation (cacheing) size of 20, I see that I get EntityExistsException:

javax.persistence.EntityExistsException: A different object with the same identifier value was already associated with the session : [nl.xyz.app1.entity.ChildFields #123456]

The Entities are:

@Entity
@Table(name = "CHILD_FIELDS")
public class ChildFields implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "my_entity_seq_gen")
    @SequenceGenerator(name = "my_entity_seq_gen", sequenceName = "MYSEQ_S01")
    @Column(name = "CF_ID", unique = true, nullable = false)
    private Long id;

    @Column(name = "CF_DETAILS_ID")
    private Long detailsId;

And

@Entity
@Table(name = "PARENTS_OBJECT")
public class ParentObject implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "my_entity_seq_gen")
    @SequenceGenerator(name = "my_entity_seq_gen", sequenceName = "MYSEQ_S01")
    @Column(name = "PF_ID", unique = true, nullable = false)
    private Long id;

    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "CF_DETAILS_ID")
    private List<ChildFields> children;

When I use a allocation sequence of 1, then everything is ok! Why is this?

@Id
@GeneratedValue(strategy = GenerationType.AUTO, generator = "my_entity_seq_gen" )
@SequenceGenerator(name = "my_entity_seq_gen", sequenceName = "MYSEQ_S01", allocationSize=1)

Etc.

Take care when using JPA in combination with DB sequences. A solution is using an allocationSize of 1. I tested this both with Oracle and Progress.

You can find a good explanation via this post. Puspender Tanwar, gives a elaborate explanation!

The issue is solved by changing the Sequence IncrementBy value to the value as I set in allocationSize of JPA.

CREATE SEQUENCE "APPS"."LINE_LOCQTY_JPA_ID_SQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 20 START WITH 7641 CACHE 20 NOORDER NOCYCLE ;

The explanation for the issue for the previous Sequence(IncrementBy value 1):

1. When the JPA required the Id value, it hits the DB Sequence and asks for the unique value, Sequence returns 7641.
2. Now JPA has a starting point for ID, and based on allocationSize=20, JPA creates next 20 values itself and generate 7641 to 7660 unique IDs.
3. Now when all of these IDs get consumed, JPA asks DB Sequence for next unique value. And since the last value returned was 7641, sequence returns 7642(because INCREMENTBY value was 1 ).
4. JPA gets the 7642 and creates next 20 values in cache.
5. The time when JPA tries to allocate these values to the ID, it finds 7642 already has been assigned to an entity object(at step 2).

How INCREMENTBY 20 solved the issue: When Ids get consumed at step 3, JPA asks for next value to Sequence. Last value return was 7641, so this time it will increment it by 20 and return 7661. JPA creates values from 7661 to 7680 and uses them. And hence there is no unique key violation issue.