4

Hibernate is not assigning objects ids from the Oracle sequence as expected. Here's what I see in the Hibernate debug logs.

DEBUG o.h.SQL:92 - select MY_SEQ.nextval from dual
DEBUG o.h.i.e.SequenceStructure:102 - Sequence value obtained: 22643
DEBUG o.h.r.j.i.ResourceRegistryStandardImpl:73 - HHH000387: ResultSet's statement was not registered
DEBUG o.h.e.i.AbstractSaveEventListener:118 - Generated identifier: 22594, using strategy: org.hibernate.id.enhanced.SequenceStyleGenerator

The first "sequence value obtained" is correct, 22643 comes right from MY_SEQ.nextVal as it says. But then the "generated identifier" that gets used is 22594. What gives?

I've tried tweaking the generator strategy to no avail.

@Id
@SequenceGenerator(name = "generator", sequenceName = "MY_SEQ")
@GeneratedValue(generator = "generator", strategy = GenerationType.SEQUENCE)
@Column(name = "MY_ID", nullable = false, precision = 6, scale = 0)
private Integer id;

I can include my Spring Hibernate context configuration if that'll help. I don't see anything in there that looks obviously relevant.

Hibernate with Oracle sequence doesn't use it is very probably related, but that deals with gaps while the id I get is less than the sequence value obtained.

PS: Other tickets discuss sequence generator strategies that optimize efficiency. A single record of this data is inserted once a month or so, and only from this class. So, efficiency isn't a concern here.

Update 1

I am able to recreate this in HSQLDB in Oracle emulation mode too. So it's surely a Hibernate issue.

Update 2

The offset is always exactly 49. The example above correctly fetched 22643 from the sequence, but then resolved 22594 as the next value.

22643-22594=49

In another example, the next sequence value was actually 4, and Hibernate gave me -45.

4-(-45)=49

Update 3

Subsequent inserts do not call the Oracle sequence's nextVal. I suspect JPA/Hibernate is trying to fetch ids in bulk upfront for efficiency.

DEBUG o.h.SQL:92 - select MY_SEQ.nextval from dual
DEBUG o.h.i.e.SequenceStructure:102 - Sequence value obtained: 22643
DEBUG o.h.r.j.i.ResourceRegistryStandardImpl:73 - HHH000387: ResultSet's statement was not registered
DEBUG o.h.e.i.AbstractSaveEventListener:118 - Generated identifier: 22594, using strategy: org.hibernate.id.enhanced.SequenceStyleGenerator
...
DEBUG o.h.e.i.AbstractSaveEventListener:118 - Generated identifier: 22595, using strategy: org.hibernate.id.enhanced.SequenceStyleGenerator
Community
  • 1
  • 1
Sean Connolly
  • 5,692
  • 7
  • 37
  • 74

1 Answers1

6

As I mentioned in my 3rd update, JPA was "fetching 50 ids" from the sequence up front and counting through them in memory for efficiency.

This behavior is specified by javax.persistence.SequenceGenerator.allocationSize which defaults to 50.

(Optional) The amount to increment by when allocating sequence numbers from the sequence.

This isn't at all intuitive to me, or others, since my Oracle database sequence is supposed to define this behavior and 50 isn't a standard default there.

The quick and dirty solution was to specify allocationSize=1:

@SequenceGenerator(name = "generator", sequenceName = "MY_SEQ",
                   allocationSize = 1)

Now the Oracle sequence is incremented for every insert.

Community
  • 1
  • 1
Sean Connolly
  • 5,692
  • 7
  • 37
  • 74