2 Applications using different hibernate versions but same oracle database throwing unique constraint error

There are 2 applications : one is using Spring boot - 1.5.18.Release version, which has hibernate version as 5.0.12.Final

:https://search.maven.org/artifact/org.springframework.boot/spring-boot-dependencies/1.5.18.RELEASE/pom

and another application is using Spring boot - 2.4.1 version which has hibernate version as 5.4.25.Final : https://search.maven.org/artifact/org.springframework.boot/spring-boot-dependencies/2.4.1/pom where we have used @SequenceGenerator(name = "sequenceGenerator", sequenceName = "ABCD_SEQ",allocationSize = 1),

The allocation size is required because the application does not starts up

while the allocation size was not required in first application.

Database sequence is created with "INCREMENT BY 1" and same oracle database is used by both applications.

The 2 applications uses many similar entity which is copied in another application/project.

But when inserting record from the second application where spring-boot is 2.4.1 , we are getting unique sequence generator issue.

When analysed, we found out that the first application(1.5.18.Release) is abruptly incrementing the sequence although it should increment it by 1, leaving lot of gaps in between, sometimes by 50, 100, etc. and when second application(2.4.1) tries to insert record , there is error of unique constraint.

Please help, exactly where to search for the root cause , or how the hibernate cache mechanism is used this case ?

One of the entity in first application (1.5.18.Release)

@Entity
@Table(name = "MERCURY_INSTANCE")
public class MercuryInstance implements Serializable {

    @Id
    @Column(name = "MERCURY_INSTANCE_KEY", nullable = false)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seqGen")
    @SequenceGenerator(name = "seqGen", sequenceName = "MERCURY_INSTANCE_SEQ")
    private Long mercuryInstanceKey;

    @ManyToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
    @JoinColumn(name = "MERCURY_KEY", referencedColumnName = "MERCURY_KEY", nullable = false)
    private MERCURY mercury;

    @OneToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @JoinColumn(name = "MERCURY_INSTANCE_KEY", referencedColumnName = "MERCURY_INSTANCE_KEY", nullable = false)
    private MercuryInstanceTechParams MercuryInstanceTechParams;

    @ManyToMany(mappedBy = "mercuryGroupInstances")
    private List<MercuryGroupInstance> MercuryGroupInstances;
    
    @Column(name = "CREATED_DATE")
    private Timestamp createdDte;
    @Column(name = "CREATED_BY")
    private String createdBy;
    @Column(name = "UPDATED_DATE")
    private Timestamp updatedDte;
    @Column(name = "UPDATED_BY")
    private String updatedBy;
    /* getter and setters of above fields */

}

While another application(2.4.1) is similar, only difference is the sequence generator such as :

@Id
@Column(name = "MERCURY_INSTANCE_KEY", nullable = false)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seqGen")
@SequenceGenerator(name = "seqGen", sequenceName = "MERCURY_INSTANCE_SEQ", allocationSize = 1)
private Long mercuryInstanceKey;

and the database sequence is :

CREATE SEQUENCE "MERCURY_INSTANCE_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 55 NOCACHE NOORDER NOCYCLE ;

@SequenceGenerator has an allocation size of 50 by default. First of all, it is a best practice to align your allocation size with INCREMENT BY value in your db sequence.

This value tends to depend on whether your application is read or write intensive. In addition you have to think also about performance(if it often writes to db, lower values may cause performance issues). In case your application is a read only application then the impact of using allocation size 1000 or 1 is negligible.

Next generation of Sequence Id is based on allocationSize.

So for instance, the first app requests ids from 1-50 and the second one is asking every time there is an insert. In this case the ids from 1-50 are going to be used by the first application, but then the second app requests an id and will be given an id within the range of 1-50 (since INCREMENT BY=1). This results in an exception for which ever app is going to save second with the same id.

So, the easiest solution would be to change:

@SequenceGenerator(name = "seqGen", sequenceName = "MERCURY_INSTANCE_SEQ")

of the first app to:

@SequenceGenerator(name = "seqGen", sequenceName = "MERCURY_INSTANCE_SEQ", allocationsize=1)