Sequence Magic

Photo by Leon Contreras on Unsplash

Prelude

I’ve been working on moving SpringBoot applications to SpringCloud. It’s a complex task including libraries upgrade, infrastructure adjustment, changing the way we handle settings, etc. As a part of this task, SpringBoot should be migrated to 2nd version.

Once I’m done with an application, I test it, deploy to the dev environment, test it again there (not really much, sometimes it’s just checking logs and hitting a few endpoints using curl). If I’m happy, the application is going further to the QA environment, so I can forget about it. For a little while. Well, you know how it goes.

The Thing Comes Up

On a daily meeting, my colleague reported an issue she ran into while testing her code in the dev environment. Violation of primary key constraint, nothing out of the ordinary. We already experienced similar issues, so she got the most reasonable advice: check sequences, reset them if needed. Maybe database restore was made incorrectly. It happens.

As was mentioned above, there were a lot of changes made to migrate the application to SpringCloud. The changes were not related to the sequences or the persistence layer at all. But there were a lot of changes, I concerned and my colleague got one more advice: test her changes on a stable branch. Just to make sure, it’s not my changes. They should not, I was pretty certain. But
who knows.

We Found the Source

The next day the colleague found out that a stable branch is working, and the issue is showing up only in my feature branch. Getting into the issue we’ve found that the application tries to insert a record along with an ID that is less than the sequence’s last value.

OK, the data issue hypothesis had turned out to be wrong. The main difference between these two branches is that upgraded SpringBoot was in use. Quick googling showed that Hibernate uses optimization for sequence AUTO strategy, and suggested to turn optimization off with a flag:

hibernate.id.new_generator_mappings = false

Which could be reached in SpringBoot using

spring:
 jpa:
   hibernate:
     use-new-id-generator-mappings: false

The problem was that we don’t use AUTO strategy but SEQUENCE. Anyway, I tested this solution just in case and it worked. Strange, isn’t it?

The Explanation

To grok that behavior we went through the Hibernate documentation, codebase and several posts on the internet. This is what we got:

`SEQUENCE` uses `seqhilo` optimization by default which works this way:

• Let’s say DB sequence has an increment `N`
• The sequence is called for the next value, let’s call it hi
• Hibernate uses identifiers range `[(hi — 1) * N, hi * N)`
• As soon as all the identifiers are used, repeat the loop

Assuming that the current value is 1 and increment is 10, next hi is going to be 11, so values from 1 to 10 are going to be used for the identifiers without hitting the database.

If an increment is 1, the identifiers range is just [hi — 1, hi) which means that it contains the only value and it works as a regular DB sequence. And this is exactly what was in place before the migration.

This part was well-known, but what I didn’t know is that
* Hibernate 3.2.3 introduced new enhanced optimizers in order to allow other non-hibernate applications to use the same sequence.
* Since Hibernate 4 it’s possible to enable these optimizers for AUTO, SEQUENCE and TABLE generators with hibernate.id.new_generator_mappings property set to true.
* Starting with Hibernate 5 this property set to true by default.

Now the most interesting part comes.

The application uses SpringBoot 1.4.5 which, in turn, uses Hibernate 5. That part that I was not able to get. Hibernate 5 uses enhanced generators by default, so the old version should fail as well, right? My other colleague helped me with it. She mentioned that SpringBoot 1.4.5 turns the legacy behavior back. That is why the old code worked. But SpringBoot 2 uses Hibernate default behavior now. And that’s the exact reason why we got it after migration.

One more thing. Since we use JPA, Hibernate takes the value for increment from the SequenceGenerator::allocationSize property.

Possible Solutions

Having said that, the following solutions could be used:

Turn on legacy behavior with the Hibernate or Spring flag set to false

The optimization will be turned off which means that the application is going to hit the database every time it needs a new identifier. It works for all the entities across the application.

Use allocationSize equals to 1

More fine-grained solution. The application will hit the database at every turn only for the entities having allocationSize=1.

Set DB sequence’s increment to 50 (default allocationSize value)

This solution implies that some changes in the database should be done. You need to change and apply your migration scripts. You need to make sure this change won’t affect any other application using the same database, etc. On the other hand you’ll get efficient DB queries.

Specify `none` optimizer for the generator

I’d say this solution is the same as setting allocationSize to 1. Maybe it’s even more explicit since it does not require awareness of optimizer details such as allocationSize. Not such a big difference though.

Set IDENTITY generator if ID column is auto-incremented

If the database supports auto-incremented columns and you use them as primary keys for your entity-tables, it would be one of the best options. Now Hibernate does not need to take care of identifiers handlers, it will retrieve them in on INSERT..RETURNING query. On the other hand Hibernate is forced to call this INSERT every time you save an entity since it needs to know the identifier immediately. It means it can’t use optimizations as JDBC batching.