Sequences in Hibernate

Sequences in Hibernate

What are we looking at

Why do we even use Hibernate? Well it's convenient and easy to understand!

Do we know how Hibernate works? Of course! We use it in production for years!

Those are usually the answers from everybody that uses Hibernate. Well in general I agree with the first answer, but most of the people don't really know what Hibernate is doing and why. Today I will not go through Hibernate in general but I will focus on one part of it... and that is sequence generation. Very few people know how it works or even have an interest, but everybody is using it and it somehow works. The problems only come when you want to replace Hibernate or do a manual insert on the database... That's when you realize that you don't know what Hiberante is doing! Nowadays that Hibernate 5 and is set to true, which means newer IdentifierGenerator for AUTO, TABLE and SEQUENCE will be used, you may have problems even with the code that was working previously.

Did you ever have to answer this question: Why do we have gaps between IDs and our sequence? If the answer is YES keep on reading...

Hibernate 5

Let's take a look what does that mean for your entities and your configuration if you are using Hibernate 5. Also the new mapping strategy is true by default. = true

What does it mean? Let's look at the example... Our typical ID definition:

@Id @SequenceGenerator(name = "TEST_SEQ", sequenceName = "TEST_SEQ") @GeneratedValue(strategy = SEQUENCE or AUTO, generator = "TEST_SEQ") @Column(name = "ID") private Long id;

H2 or Oracle driver:

If you define your ID like above and use the mentioned drivers Hibernate will select the following generator - with The hidden trick is here how does your sequence need to look like:

create sequence TEST_SEQ start with 1 increment by 50

Now you're all probably wondering why increment by 50? If you take a closer look at @SequenceGenerator and the parameter allocationSize you will soon realize what this property does and maybe wonder right about now: what is it doing to my ID?!

PooledOptimizer is the implementation that uses a pool of values, storing the next low value of the range in the database. It means that it will do only one query for next value of the sequence and it will be used for the next 50.

So to get the first ID value Hibernate issues next value once and than uses the e.g. values from 1 - 51, than another next value DB call then uses the range between 52 - 101 and so on. Pretty neat not calling next value on every call right? Yes if you know that it behaves like that! If you expect that for every new ID you call next value then you need to put allocationSize = 1. In case of default allocationSize The DB calls would look like this.

# Current value TEST_SEQ = 51

call next value for TEST_SEQ; #TEST_SEQ = 101

insert into your_test_table (id) values (?);

insert into your_test_table (id) values (?);

insert into your_test_table (id) values (?);

... total of 50 inserts before calling next val

call next value for TEST_SEQ; #TEST_SEQ = 151

... inserts again

So far so good! Let's take a look at what happens if we use legacy support. = false

H2 and strategy AUTO:

IdentityGenerator will be used. But we wanted sequence! It is generally a better approach to use sequences instead of identity so I won't go into details here. You can search for lots of good posts that tell you the advantages of sequences over identity.

In order to enable sequences you have to set explicitly strategy to SEQUENCE. After you set the strategy to SEQUENCE, Hibernate will use SequenceHiloGenerator and LegacyHiloAlgorithmOptimizer. So most likely if you never heard about allocationSize and you are using SEQUENCE strategy you have an unwanted behaviour. What does this strategy do? First of all create sequences statement in this case looks like:

create sequence TEST_SEQ start with 1 increment by 1

and Hibernate will use Hilo algorithm to calculate IDs, which means he will do a next value call on sequence and then calculate the values he will use from memory with the following formula (e.g. allocationSize is 50 and current sequence value is 5):
upperLimit = (5 * 50) + 1 = 251

lowerLimit = (251 - 50) = 201.

So one next value DB call and the next 50 ID values would be generated from the application itself (201 until 251). Probably this is not what you wanted since the ID's are not following your sequence value any more. Therefore you cannot any more use next value directly in INSERT statement or switch Hibernate with some other persistence library without implementing getting the IDs the same way. How DB calls look like:

# Current value TEST_SEQ = 51

call next value for TEST_SEQ; #TEST_SEQ = 52

insert into your_test_table (id) values (?);

insert into your_test_table (id) values (?);

insert into your_test_table (id) values (?);

... total of 50 inserts before calling next val

call next value for TEST_SEQ; #TEST_SEQ = 53

... inserts again

Let's see what happends when using Oracle driver and strategy AUTO:
It would cause an error since there is a bug somewhere in Hibernate! :)

HINT: Upgrading to Spring Boot 1.4.0 will cause this issue if you have such a configuration!

So you need to put explicitly the strategy to SEQUENCE:
After you set the strategy to SEQUENCE Hibernate will use SequenceHiloGenerator and LegacyHiloAlgorithmOptimizer which is the same as H2 that we explained above.

Hibernate 4

How is all this working in Hibernate 4? Well it is pretty much the same because you had all of this in Hibernate 4 as well but, new_generator_mappings was set to false there by default, whereas in Hibernate 5 it is true by default. Don't panic if you are using Spring Boot 1.4.0 because Spring overrode it to false again for backwards compatibility but keep this in mind if you are using it outside Spring Boot.

Since we mainly use (or want to use) sequences I did not mention that there is yet another strategy in Hibernate called TABLE. This is another strategy that is database independent alternative to generating sequences or using identity. So here you basically trade performance (because you need to use row level locking) for database portability. The example of DB queries:

select next_val from seq_table where sequence_name = 'example_seq' for update;

update sequence_table set next_val = ? where next_val = ? and sequence_name = 'example_seq'

insert into your_test_table (id) values (?);


I encourage you too try it out by yourself to get a feeling how to use it best for you use case. Keep in mind that if you are using AUTO strategy that the results may vary depending on the DB provider you are using so better always be explicit (even with allocationSize).