JPA SequenceGenerator with allocationSize 1 performance tuning

I had a blog last year about fixing the sequence number going wild by setting the allocationSize to 1.

Overall it solves the inconsistency problem if you are using a sequence with ‘INCREMENT BY’ value 1 in database.

Issue

One problem comes up today is I am facing some performance issue with the above setting when I was trying to persist a lot of records(entities) because for every entity need to perform a ‘select SEQ.nextval from DUAL’ in order to get a ID from the specified sequence. So when persisting hundreds of thousands of entities, this becomes a problem.

First Try

Did some search and tried to set my allocationSize to 500 also increased my sequence’s ‘INCREMENT BY’ value to 500 by

alter sequence SEQ_EQUITY_PROC_DAILY_ID increment by 500

At doing this, the saving process is much faster(10 times). However  when I query the database, i found another inconsistency that my sequence next value is ‘2549522’ but the ID I have in the db table is something like ‘1274761000’. So the problem for using the MultipleHiLoPerTableGenerator where the id will be allocationSize*sequenceValue. This generator is perfectly fine is you have a new table with sequence init value 1 given that you can tolerate this kind of inconsistency between the ID value and the actual sequence value. So how it works is, by default we have allocation size 50, so hibernate will get the 50 and use the 1-50 for the current entities. Next round it will use 51-100 when the sequence value is 2. The drawback is if there are some other JDBC connection or jpa using a different setting, we will probably get ID collision.

Solution

To solve this problem, we need to set a property in hibernate:

properties.setProperty("hibernate.id.new_generator_mappings", Boolean.toString(true));

This ‘hibernate.id.new_generator_mappings’ by default is false which uses the ‘SequenceHiLoGenerator‘ which will have that multiply behavior. Once we set it to true, it will then use the ‘SequenceStyleGenerator‘, which is more jpa and oracle friendly. It generates identifier values based on an sequence-style database structure. Variations range from actually using a sequence to using a table to mimic a sequence.

Advertisements

7 Comments Add yours

  1. Mohit says:

    Hi Great post. I liked the way you explained the problem first and then gave the solution. One question. by setting the hibernate.id.new_generator_mappings”, Boolean.toString(true)); and on next sequence 2, do JDBC will insert data at 51? and lets say hibernate resumes and it will begin to store data from 52 – 100, and after 100 records we get next sequence 3 and allocation as 101-150 ?

    1. LeOn says:

      Hey Mohit.
      The 2->51. 3->100 thing is for the high-low generator. As you mentioned, if `new_generator_mappings` is true and DB seq increment value is 50, then you sequence will match the ID you have in record. So the first seq is 1 and next seq is 51 and you also set the `allocationSize` to 50. Hope I made it clear. 🙂

      looks at this SO answer I posted for a bit more detail : https://stackoverflow.com/questions/5346147/hibernate-oracle-sequence-produces-large-gap/36816797#36816797

  2. Joel Trunick says:

    We are using 5.2+ where as the new_generator_mapping supposedly defaults to true. With Oracle, hibernate wants to increment by 1 even though the Oracle sequence is set to increment by 100. Unclear why it appears to be using HiLo still.

    1. LeOn says:

      you can still set it to use new generator explicitly i think.
      Also more importantly, do not forget to set `allocationSize` to 100 in jpa/hibernate code(java annotation).

  3. Sam says:

    Thanks your post, But I have question about it.
    In our system, we have many servers for HA souation.The first server run ok and work fine, but I start other one, and I get the error below:
    java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (******.PK_CUSTOMER_LOG) violated
    I check the source code of hibernate, the PooledOptimizer class only request oracle for the sequence once when I save a CustomerLog entity. if you save another one entity, the PooledOptimizer only increments by 1 in memony, that is the reason.
    I think the first server should request ten sequences from oracle when the allocationSize is 10, so the other server can get correctly sequence after save new object.
    Do you konw how to fix this issue? I really cannot find any solution, thanks.

    1. LeOn says:

      i am not sure i understand the issue correctly. Theoretically even if you have multiple servers, every time you increase sequence, it should add the allocation size. Did you set the `increment by ` for your sequence accordingly in database?

      1. Sam says:

        Thanks your reply, I get the result from stackoverflow. The reason is the increment value is 1, not 10, the allocation size and sequence increment value should be same.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s