Fix Hibernate JPA sequence generating odd id with allocationSize

I config my jpa entity class as usual using Intellij generated stub from DB schema with below config:


@Entity
@javax.persistence.Table(name = "IMPORT_RUN", schema = "SCRCOWNER", catalog = "")
public class ImportRunEntity
{
    private Long importRunId;

    @SequenceGenerator(name = "IMPORT_RUN_ID_GEN", sequenceName = "SEQ_IMPORT_RUN_ID")
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "IMPORT_RUN_ID_GEN")
    @javax.persistence.Column(name = "IMPORT_RUN_ID", nullable = false, insertable = true, updatable = true, precision = -127)
    public Long getImportRunId()
    {
        return importRunId;
    }

    public void setImportRunId(Long importRunId)
    {
        this.importRunId = importRunId;
    }
.....
}

However the id that is inserted into DB are way different(larger) than it should be. For example, id should be 10012 but it becomes 5223326. This odd number is actually getting from some other sequence.

To fix this, I have to add the

    @SequenceGenerator(name = "IMPORT_RUN_ID_GEN", sequenceName = "SEQ_IMPORT_RUN_ID", allocationSize=1)

to the ‘SequenceGenerator’ to make sure it get its own sequence and add 1. By default the allocationSize is 50.

TheJavadoc of the javax.persistence.SequenceGenerator class provided an partial answer, because I found out that it contained a property called allocationSize, which is specified as follows:

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

After this modification the value inserted to the id column of the Persons table was the same than value fetched from the person_sequence sequence. I am not sure how common this problem is, but I have never encountered this problem when building web applications by using Hibernate without JPA. This leads me to wonder, if this is a Hibernate specific problem or are other JPA2 implementations behaving in the same way. However, in my opinion the default value of the allocationSize property should be one, because it is the most common use case of id generation by using a sequence.
And this is a related discussion
Some useful notes:

AllocationSize is a range of primarykey values reserved for Hibernate. And the select ….nextval from dual will be done only after hibernate consumed this range of primarykeys.

So you MUST declare the same value on both allocationSize (Hibernate) AND sequence increment (DB)

When explicitly set allocationSize=500, e.g. on Oracle

create sequence SEQ_ACE_WORKERS_QUEUE_STATS_ID
       MINVALUE 1 
       MAXVALUE 999999999999999999999999999 
       START WITH 1
       INCREMENT BY 500 
       NOCACHE 
       NOCYCLE;

Otherwise, you’ll notice negative values or constraint errors raised from your DB because of primary key collisions.

When the app server is restarted, you’ll notice the ‘jump’ between the latest primarykey allocated, and the ‘newly’ sequence number selected at restart.

Final comment : default value is 50. So if you don’t specify allocationSize on the Hibernate side, you MUST declare INCREMENT BY 50 on the DB side.

For clustering

If you’re using load-balancers, but the DB is shared between the instances, you cannot cache the sequences’ values.

You have to ask for the next sequence value each time you insert into DB. And each insert has to be inside a transaction.
Hibernate, (by default) has allocationSize = 50, so to fix the problem, you have to explicitly set it to 1.

 @SequenceGenerator(allocationSize=1 ...)

Hi/Lo generator

Using the JPA @SequenceGenerator along with the legacy Hibernate identifiers will give you theSequenceHiLoGenerator that applies a HI/LO optimization algorithm anyway.

But for Sequences, the actual identifier value is applied during flush-time, so you won’t get the actual value until the session gets flushed (a manual flush or the commit-time flush).

For IDENITY generator, you get the identifier generated prior to flushing, but that disables JDBC batching so it’s not a silver-bullet either.

If you want full control, you need to resort to assigned identifiers and UUID surrogate keys are perfect for this job.

Advertisements

2 comments

  1. gauravtyagi77 · July 29, 2015

    Really a nice content , I faced the same problemand only able to resolve it by making allocationSize as 1 . I am also using hibernate over JPA. I was thinking about too to write blog about this problem because i was also able to find solution for this problem after a long struggle.

  2. Pingback: JPA SequenceGenerator with allocationSize 1 performance tuning | Life in USA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s