Hibernate with Oracle sequence doesn't use it

Question

I have configured hibernate to use oracle sequence. Sequence is created with cache=20, increment=1.

All works fine, hibernate persisting entities. The id value is strange: 50,51....76,201,202...209,1008,1009,5129,5130 ....

If I ask for sequence value (select hibernate_sequence.nextval from dual) I get value like 2,3,4 ....

If I turn on hibernate sql debug, there is time to time call "select hibernate_sequence.nextval from dual" but number assigned by hibernate to ID doesn't relay on sequence!

@Id
@Column(name = "ID", insertable = false, updatable = false)
@SequenceGenerator(name = "SequenceIdGenerator", sequenceName = "HIBERNATE_SEQUENCE")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SequenceIdGenerator")
private Long id;

Hetzel

Answer 1

This is because the SequenceGenerator is not really a sequence generator. It's a sequence hi-lo generator. This means that the first time it's invoked, it gets the next value from the sequence (6 for example), then multiplies this value by 50 and gives you the result (300). The next time it's invoked, it returns 301 (without going to the sequence), and so on until it reaches 349. Then it asks the sequence for the next value and obtains 7, which it multiplies by 50 again to give you 350. My algorithm description could be off by one, but you get the idea.

If you stop and start your application, it will thus have gaps. But it's more efficient than a pure sequence generator because it only makes a database call once in 50 generations.

See http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#mapping-declaration-id-enhanced-optimizers and http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#mapping-declaration-id-generator for details.

Lamellicorn

You don't have to increment by 50 every time. That's just the default. Use @SequenceGenerator(name="SEQ_ID",sequenceName="SEQ_ID", allocationSize=1) To increment by only one. allocationSize is the key. – Valenza

Is there anyway to not use this? Since It makes a huge problem for me, having other triggers using this same sequence for this table. Then they might hit this number in the future, right? – Gosplan

Look at the comment just above yours. Use allocationSize. – Lamellicorn

Wow, thanks for this answer. Sounds like Hibernate has reinvented Oracle's sequence cache... – Pelfrey

I do have allocationSize=1 as I explained in my SO here #28078 and I get ORA-00001 Unique constraint violation on my table PK every once in a while – Sleuth

Answer 2

I take it that your question is that the values of the ID column in the database are not a natural sequence, but why you are seeing gaps:

A bit of background:

• Every time you call select HIBERNATE_SEQUENCE.nextval from DUAL the value of the sequence is increased.
• As your sequence name is generic rather than specific to the table, if you've got multiple entities which all use the HIBERNATE_SEQUENCE as id generator, then the values from the sequences are used in all entities.
• If some other application uses HIBERNATE_SEQUENCE, then the value is skipped as well.
• As you are using CACHE=20, Oracle will grab sequence numbers in blocks of 20 and then use an internal cache to return the numbers. This can lead to numbers being skipped if the cache is lost (e.g. if the DB is shut down).
• If rows are deleted from your database, the sequence value does not change

For example, consider the following scenario:

You've got two entities Entity1 and Entity2 using HIBERNATE_SEQUENCE as the ID generator:

1. Current HIBERNATE_SEQUENCE value is 100
2. An Entity1 is inserted (uses HIBERNATE_SEQUENCE which returns 101)
3. An Entity2 is inserted (uses HIBERNATE_SEQUENCE which returns 102)
4. An Entity2 is inserted (uses HIBERNATE_SEQUENCE which returns 103)
5. The Entity2 with ID 103 is deleted
6. You manually execute select HIBERNATE_SEQUENCE.nextval from DUAL (returns 104)
7. An Entity1 is inserted (uses HIBERNATE_SEQUENCE which returns 105)
8. An Entity2 is inserted (uses HIBERNATE_SEQUENCE which returns 106)

So at the end of it you'll have:

• Entity1 with IDs (101, 105)
• Entity2 with IDs (102, 106)

which explains the gaps.

EDIT:

Even if the @SequenceGenerator were setup to use the SequenceGenerator rather than the SequenceHiLoGenerator (as pointed out by JB Nizet, which I think is a better explanation for the gaps), gaps in IDs generated by sequences are a common occurrence.

Cannes

Thank you but i have SEQUENCE_A on TABLE-A. 2 applications call only this SEQUENCE_A to insert into TABLE-A. APP1 (C++ desktop app) calls APP2 to do insert but if APP2 (RESTAPI) is not available for whatever reason (maybe down), APP1 will proceed and do direct insert into Oracle TABLE-A by generating SEQUENCE_A number. I described it in this SO #28078 and I still have the issue with ORA-00001 Unique Constraint Violation on my PK every once in a while and sequence generated by APP1 and APP2 are very far appart – Sleuth

@Sleuth this sounds like a new question rather than a comment – Cannes

It is a new question, you are right. I mistakenly added wrong URL in my previous comment unfortunately. Here is the correct link to the SO #78350234 – Sleuth

Answer 3

CREATE SEQUENCE SEQ_SEQUENCENAME INCREMENT BY 1 START WITH 1 MINVALUE 1;
grant all on SEQ_SEQUENCENAME to public;

@Id
@Column(name = "ID", unique = true, nullable = false)
@SequenceGenerator(name = "SequenceIdGenerator", sequenceName = "SEQ_SEQUENCENAME")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SequenceIdGenerator")
private int Id;

Madelenemadelin

describe your answer instead of copy-paste – Dellinger