Hibernate, @Sequencegenerator and Allocationsize

Hibernate, @SequenceGenerator and allocationSize

To be absolutely clear... what you describe does not conflict with the spec in any way. The spec talks about the values Hibernate assigns to your entities, not the values actually stored in the database sequence.

However, there is the option to get the behavior you are looking for. First see my reply on Is there a way to dynamically choose a @GeneratedValue strategy using JPA annotations and Hibernate? That will give you the basics. As long as you are set up to use that SequenceStyleGenerator, Hibernate will interpret allocationSize using the "pooled optimizer" in the SequenceStyleGenerator. The "pooled optimizer" is for use with databases that allow an "increment" option on the creation of sequences (not all databases that support sequences support an increment). Anyway, read up about the various optimizer strategies there.

What does the jpa @SequenceGenerator.allocationSize mean?

Here a short explanation of the configuration values:

allocationSize

To minimize round trips to the database server, IDs are allocated in groups. The number of IDs in each allocation is specified by the allocationSize attribute.

It is possible that some of the IDs in a given allocation will not be used. Therefore, this strategy does not guarantee there will be no gaps in sequence values.

The default is 50.

INCREMENT BY

Specify the interval between sequence numbers.

This integer value can be any positive or negative integer, but it cannot be 0.

If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

CACHE

Specify how many values of the sequence the database preallocates and keeps in memory for faster access.

If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.

Conclusion

allocationSize and INCREMENT BY must have the same value. A higher number reduces the database round trips.

Cache is DB performance optimization and is not available for all database types.

@SequenceGenerator's initValue and allocationSize are ignored and generator not assigned to @Id field (H2, HIbernate, Spring)

Your JPA set-up is correct but you have to keep in mind that the persistence provider will only take care of generating the id for you (additional query to the database for the next value of the sequence) when inserting through Hibernate or JPA API.

This will not happen when you perform the insertions 'by hand' in the data.sql file. You would have to invoke the sequence manually there:

INSERT INTO user (ID, USERNAME, PASSWORD_ENCODED)
VALUES (NEXTVAL('my_seq')'user1', '<some_giberish>')

Edit

This property: spring.jpa.hibernate.use-new-id-generator-mappings or hibernate.id.new_generator_mappings=true (if your not using spring boot) would allow for initialValue feature support.

issue with Hibernate allocationSize for SequenceGenerator

Issue is i was using select TEST_ENTITY_ID_SEQ.NEXTVAL from dual to get the current values of the sequence from another session.

The CURRVAL is SESSION specific; that is, each session might have a
DIFFERENT value than other sessions.

So when i queried from my application it started to give me correct values.

Hibernate allocationsize default value is not 50

Your sequence was seemingly created by a DBA, and not by your JPA provider. Consequently there is absolutely zero point in specifying the allocationSize to JPA (since it is only for use in schema/sequence creation). If you want a particular allocationSize you need to tell your DBA to set this up when creating the SEQUENCE (normally via something like INCREMENT BY).

@SequenceGenerator allocationSize 'duplicate key error' issue

You need to use the pooled-lo optimizer as it can inter-operate with other systems as well, or inserts issued from an administration console:

@Id
@GenericGenerator(name = "sequenceGenerator", strategy = "enhanced-sequence",
    parameters = {
        @org.hibernate.annotations.Parameter(
            name = "optimizer", 
            value = "pooled"),
        @org.hibernate.annotations.Parameter(
            name = "initial_value", 
            value = "1"),
        @org.hibernate.annotations.Parameter(
            name = "increment_size", 
            value = "5")
    }
)
@GeneratedValue(
    strategy = GenerationType.SEQUENCE, 
    generator = "sequenceGenerator")
private Long id;

---

Related Topics