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

Question

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 ;

Answer 1

@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)

Answer 2

'allocationSize' doesn't mean that the entities ids will increase by this value but it is a number after which the database query will be made again to get the next database sequence value. On the application side, ids for an entities instances will always increase by 1 unless we reach the allocationSize limit. After 'allocationSize' is reached, the next id will be retrieved from the database sequence again. In case if application restarts or redeployed before allocationSize limit is reached, we will see a one-time jump in the next value. 'allocationSize' is to improve performance.

In your first scenario: The sequence generator is consistent. It's only task is to generate unique integer values, nothing else. As mentioned this behaviour is caused by oracle caching, pre-allocating, the sequences numbers (20 by default). The ID column is an surrogate/artificial primary key and only used to uniquely identify the row, no information whatsoever should be derived from it. Even if you don't cache the sequence number you will never get an uninterrupted series of ID's due to rolled back transactions, deletes, application and database server restarts. And not caching sequences has a heavy performance penalty on high volume transaction system.

In your second scenario: try putting SequenceGenerator on the top of your class to make hibernate pick up correct sequqnce.

@Entity
@Table(name = "{your_table_name}")
@SequenceGenerator(name = "seqGen", sequenceName = "MERCURY_INSTANCE_SEQ", allocationSize = 1)
public class {$your class name$} {


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