Hibernate 5 Sequence Generate Issue

Question

I am migrating to hibernate 5 from 3. I am seeing the sequence generator not working properly in Hibernate 5. I have sequence defined with minimum value 1000 and increment by 1. But when I am trying to create a new entity record, I am seeing a record inserted with id 951. It seems like the id was minuses 50 from actual sequence next value. In my case the ID should be 1000.

Please let me know any help.

Here is my entity and sequence:

Entity:

@Entity
@Table(name = "SOME TABLE")
public class Group {

  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "name",  sequenceName ="SEQ_name" )
  @GeneratedValue(strategy = GenerationType.AUTO, generator="name")
  private Long id;

  @Pattern(regexp = "^[^\\*]*$", message = "{3011}")
  @Size(message = "{3014}")
  @NotBlank(message = "{3000}")
  @Column(name = NAME, unique = true, nullable = false)
  private String name;

Sequence:

CREATE SEQUENCE  SEQ_name MINVALUE 1000 NOMAXVALUE INCREMENT BY 1 CACHE 20 NOORDER NOCYCLE;

Luff

Answer 1

Hibernate calls SEQ_name.nextval but if the allocationSize is greater than 1, it decrements it by the allocationSize and increments it by 1.

The next (allocationSize-1) key generation are done without communication with the database simple by increasing by 1.

As a result, if you use the default allocationSize which is 50, there are two consequences:

The sequence must have the INCREMENT BY set to the same value as allocationSize

To align the sequence with existing key in the database, set the START WITH to

max(ID) + allocationSize

Here a small example

-- START WITH = max(ID) + allocation size 
-- INCREMENT BY = allocation size
-- e.g. if 100 is the last key,
-- to start with a key 101 - set START WITH to 150
CREATE SEQUENCE hib_seq START WITH 150 INCREMENT BY 50;



select hib_seq.nextval - 50 + 1 from dual;
101
-- 49 times Hibernate performs increase by 1 - keys 102 to 150
-- new sequence generation
select hib_seq.nextval - 50 + 1 from dual;
151

Note this behaviour requires that property hibernate.id.new_generator_mappings to true as recomended

Klagenfurt

I don't think the problem with sequence cache because in my case the cache at db level is 20 only But I am getting 951 instead of 1000. If it is a 20 different I suspect the problem with cache. But here the difference is 50 which is a default allocationSize value – Luff

@Luff I got it - this could be interesting for you... – Klagenfurt

Answer 2

The sequence you defined does not start with 1000, but with 1 (minvalue 1). I also do not see where you set the mentioned sequence as the sequence used by your entity.

Ribband

Its a publish mistake. Updated with actual – Luff

@Steve Ebersole any help. – Luff

Answer 3

In some cases, your Oracle user will be able to select a SEQUENCE from another user whose SEQUENCE may not be correct.Therefore, you should check with your Oracle user：

select * from all_sequences t where t.SEQUENCE_NAME = 'HIB_SEQ';

Decapod

Please can you remove the non-english characters from your answer - see meta.stackexchange.com/questions/13676/… for info on use of english in answers – Nicolanicolai