Should javax.persistence.SequenceGenerator.allocationSize() be consistent with INCREMENT BY?

Question

I have an issue using Hibernate javax.persistence.SequenceGenerator.allocationSize() property. When it is set to 1 I face performance issues when inserting a lot of records to database. Otherwise when I set it to default (50) or even higher value I receive exceptions that entity with specified identifier is alredy exists!

The biggest problem is that a lot of developers use shared database, so it is really possible that generated identifiers will not be synchronized.

At other hand I noticed that Oracle sequence created using "INCREMENT BY 1". May be setting this accordingly to allocationSize can solve the problem?

I am using WildFly 10.1 (so Hibernate version is 5.0.10) and Oracle 12c (12.2.0.1).

Thanks in advance for suggestions!

Answer 1

Yes, the value of the allocationSize of your SequenceGenerator and the steps in which your database increments the sequence have to be identical.

If you set the allocationSize attribute to 50, you tell Hibernate that the sequence gets incremented by 50 and that it shall generate 49 values internally before it selects the next value from the sequence.

And you don't need to worry about duplicate ids if you keep the allocationSize and the increment of the sequence in sync.

Let's say; you have 2 servers which connect to the same database. Both servers use an allocationSize of 50, and you configured your database sequence to be incremented by 50.

Server1 request a new value from the sequence and gets 1000. It will store this value internally and increment it until it reaches 1049.

In the meantime, Server2 requests a new value from the sequence and gets 1050. It will increment this value until 1099.

The next value returned by the database sequence will be 1100. As you can see, there will be no duplicate ids. But there might be gaps between the primary key values, and you can't use them to order your database records chronologically. But you shouldn't use primary keys for that anyways ...