Hibernate GeneratorSequence fails after 50 entries

Question

I have an entity with the following id configuration:

public class Publication implements Serializable, Identifiable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;
}

with this generator (Liquibase syntax):

<createSequence incrementBy="10" sequenceName="sequence_generator" startValue="1" cacheSize="10"/>

and a Spring Data JPA Repository:

@Repository
public interface PublicationRepository extends JpaRepository<Publication, Long>, JpaSpecificationExecutor<Publication> {
   // ...
}

Now I have part in my application where I create about 250 new Publication objects without an id and then do publicationRepository.saveAll(). I get the following exception:

Caused by: javax.persistence.EntityExistsException: A different object with the same identifier value was already associated with the session : [mypackage.Publication#144651]

I debugged with breakpoints and found that this always happens with the 50th object, where the assigned ID suddenly is set as an ID that is already present in the set of already saved objects – so the generator seems to return the wrong value. For collections with less than 50 objects, it works fine.

What is also strange: The objects IDs created have an increase of 1, while if if execute NEXT VALUE FOR sequence_generator on my database i get IDs in increments of 10.

Am I using the generator wrong?

Answer 1

You need to sync SequenceGenerator's allocationSize with your sequence's incrementBy. The default value for allocationSize is 50, which means that after every 50th insert, the hibernate will generate select nextval('sequence_name)` (or something similar depending on the dialect), to get the next starting value for IDs.

What happens in your case is that:

• for the first insert Hibernate fetches next value for the sequence, which is 1. By first insert I mean first insert whenever the service/application is (re)started.
• then it performs 50 inserts (default allocationSize) without asking DB what is the next value for the sequence. Generated ID will be from 1 to 50.
• 51st insert fetches next value for the sequence, which is 11 (startBy + incrementBy). Previously you inserted an entity with ID=11, which is why it fails to insert the new entity (PK constraint violation).

Also, every time you call select nextval on sequence, it simply does currentValue + incrementBy. For your sequence, it'll be 1, 11, 21, 31, etc.

If you enable SQL logs, you'll see following:

1. Calling repository.save(entity) the first time would generate

select nextval('sequence_name`);
insert into table_name(...) values (...);

2. Saving second entity with repository.save(entity) would generate only

insert into table_name(...) values (...);

3. After allocationSize number of inserts you would again see:

select nextval('sequence_name`);
insert into table_name(...) values (...);

Advantage of using sequences is to minimize the number of times Hibernate would need to talk to the DB to get the next ID. Depending on your use-case, you can adjust the allocationSize to get the best results.

Note: one of the comments suggested to use allocationSize = 1 which is very bad and will have a huge impact on performance. For Hibernate that would mean that it needs to issue select nextval every time it performs an insert. In other words, you'll have 2 SQL statements for every insert.

Note 2: Also keep in mind that you need to keep initialValue of SequenceGenerator and startValue of sequence in sync as well. allocationSize and initialValue are the two values used by the sequence generator to calculate the next value.

Note 3: It worth mentioning that depending on the algorithm used to generate sequences (hi-lo, pooled, pooled-lo, etc.), gaps may occur between service/application restarts.

---

Useful resources:

• Hibernate pooled and pooled-lo identifier generators - in case you wish to change the algorithm used by the sequence generator to calculate the next value. There might be the case (e.g. in a concurrent environment) where the two service use the same DB sequence to generate values and their generated values might collide. For cases like that, one strategy is better that the other.