Hibernate GenerationType.IDENTITY not generating sequence ids

Question

In my springboot application, I noticed one strange issue when inserting new rows.

My ids are generated by sequence, but after I restart the application it starts from 21.

Example:

First launch, I insert 3 rows - ids generated by sequence 1,2,3

After restart second launch, I insert 3 rows ids generated from 21. So ids are 21,22 ...

Every restart It increased to 20. - This increasing pattern always 20

Refer my database table (1,2 after restart 21)

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?

Answer 1

Although I think the question comments already provide all the information necessary to understand the problem, please, let me try explain some things and try fixing some inaccuracies.

According to your source code you are using the IDENTITY id generation strategy:

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

You are using an Oracle database and this is a very relevant information for the question.

Support for IDENTITY columns was introduced in Oracle 12c, probably Release 1, and in Hibernate version - I would say 5.1 although here in SO is indicated that you need at least - 5.3.

Either way, IDENTITY columns in Oracle are supported by the use of database SEQUENCEs: i.e., for every IDENTITY column a corresponding sequence is created. As you can read in the Oracle documentation this explain why, among others, all the options for creating sequences can be applied to the IDENTITY column definition, like min and max ranges, cache size, etc.

By default a sequence in Oracle has a cache size of 20 as indicated in a tiny note in the aforementioned Oracle documentation:

Note: When you create an identity column, Oracle recommends that you specify the CACHE clause with a value higher than the default of 20 to enhance performance.

And this default cache size is the reason that explains why you are obtaining this non consecutive numbers in your id values.

This behavior is not exclusive to Hibernate: please, just issue a simple JDBC insert statement or SQL commands with any suitable tool and you will experiment the same.

To solve the issue create your table indicating NOCACHE for your IDENTITY column:

CREATE TABLE your_table (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOCACHE,
  --...
)

Note you need to use NOCACHE and not CACHE 0 as indicated in the question comments and now in a previous version of other answers, which is an error because the value for the CACHE option should be at least 2.

Probably you could modify your column without recreating the whole table as well:

ALTER TABLE your_table MODIFY (ID GENERATED BY DEFAULT ON NULL AS IDENTITY NOCACHE);

Having said all that, please, be aware that in fact the cache mechanism is an optimization and not a drawback: in the end, and this is just my opinion, those ids are only non natural assigned IDs and, in a general use case, the cache benefits outweigh the drawbacks.

Please, consider read this great article about IDENTITY columns in Oracle.

The provided answer related to the use of the hilo optimizer could be right but it requires explicitly using the optimizer in your id field declaration which seems not to be the case.

Answer 2

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 NOCACHE;

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