Hibernate GenerationType.IDENTITY not generating sequence ids-CodePudding

The

My JPA entity

@Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(unique = true, nullable = false) private Long id;

I tried some stackoverflow solutions, it's not working

I tried this, not working

spring.jpa.properties.hibernate.id.new_generator_mappings=false

I want to insert rows by sequence like 1,2,3,4. Not like this 1,2,21,22, How to resolve this problem?

CodePudding user response：

It is related to Hi/Lo algorithm that Hibernate uses for incrementing the sequence value. Read more in this example: https://www.baeldung.com/hi-lo-algorithm-hibernate.

This is an optimization used by Hibernate, which consumes some values from the DB sequence into a pool (Java runtime) and uses them while executing appropriate INSERT statements on the table. If this optimization is turned off and set allocationSize=1, then the desired behavior (no gaps in ids) is possible (with a certain precision, not always), but for the price of making two requests to DB for each INSERT.

Examples give the idea of what is going on in the upper level of abstraction. (Internal implementation is more complex, but here we don't care)

Scenario: user makes 21 inserts during some period of time

Example 1 (current behavior allocationSize=20)

#1 insert: // first cycle - need next MY_SEQ value, but MY_SEQ_PREFETCH_POOL is empty - select 20 values from MY_SEQ into MY_SEQ_PREFETCH_POOL // call DB - take it from MY_SEQ_PREFETCH_POOL >> remaining=20-1 - execute INSERT // call DB #2-#20 insert: - need next MY_SEQ value, - take it from MY_SEQ_PREFETCH_POOL >> remaining=20-i - execute INSERT // call DB #21 insert: // new cycle - need next MY_SEQ value, but MY_SEQ_PREFETCH_POOL is empty - select 20 values from MY_SEQ into MY_SEQ_PREFETCH_POOL // call DB - take it from MY_SEQ_PREFETCH_POOL >> remaining=19 - execute INSERT // call DB

Example 2 (current behavior allocationSize=1)

#1-21 insert: - need next MY_SEQ value, but MY_SEQ_PREFETCH_POOL is empty - select 1 value from MY_SEQ into MY_SEQ_PREFETCH_POOL // call DB - take it from MY_SEQ_PREFETCH_POOL >> remaining=0 - execute INSERT // call DB

Example#1: total calls to DB is 23
Example#2: total calls to DB is 42

Manual declaration of the sequence in the database will not help in this case, because, for instance in this statement\

CREATE SEQUENCE ABC START WITH 1 INCREMENT BY 1 CYCLE CACHE 0;

we control only "cache" used in the DB internal runtime, which is not visible to Hibernate. It affects sequence gaps in situations when DB stopped and started again, and this is not the case.

When Hibernate consumes values from the sequence it implies that the state of the sequence is changed on DB side. We may treat it as hotel rooms booking: a company (Hibernate) booked 20 rooms for a conference in a hotel (DB), but only 2 participants arrived. Then 18 rooms will stay empty and cannot be used by other guests. In this case the "booking period" is forever.

More details on how to configure Hibernate work with sequences is here: https://ntsim.uk/posts/how-to-use-hibernate-identifier-sequence-generators-properly

Here is a short answer for older version of Hibernate. Still it has relevant ideas: https://stackoverflow.com/a/5346701/2774914

CodePudding user response：

GenerationType.SEQUENCE Hibernate creates the sequence for our tables if we specify the column with GenerationType.SEQUENCE. We can also add our sequence to be used if needed.

We can generate the auto incremental values by configurating the separate sequences for every id auto increment field also. In this along with our entities, sequences will also be created.

Coupon Entity: (same way create entity for Store also) @Entity public class Coupon {

public Coupon() {

}

@Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "coupon_seq") @SequenceGenerator(name = "coupon_seq", sequenceName = "coupon_seq", allocationSize = 100) @Column(name = "coupon_id") private Long couponId;

// getter & setters for couponId

}

CodePudding user response：

Have you tried using SEQUENCE generator?

https://ngdeveloper.com/generationtype-identity-vs-generationtype-sequence-vs-generationtype-auto/