I don't know if anyone has encountered this issue before, but using the following annotation for the primary key in oracle, which seems to be the preferred solution if you search the web, hibernate doesn't use the value of MY_SEQ.NEXTVAL, but generates some other computed id, even though it increments the value of MY_SEQ. For instance, the value of MY_SEQ.NEXTVAL would be 12 and the value of id=800. Does anyone why this happen?


@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="myseq")
@SequenceGenerator(name="myseq",sequenceName="MY_SEQ")
private long id;


I know that there are 2 ways to fix, for those who have this problem, but haven't found a solution yet.You can either remove the generation strategy or add allocationSize=1.

Possible solutions:
1)
@Id
@GeneratedValue(generator="myseq")
@SequenceGenerator(name="myseq",sequenceName="MY_SEQ")
private long id;

2)
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="myseq")
@SequenceGenerator(name="myseq",sequenceName="MY_SEQ",allocationSize=1)
private long id;

I just ran into this and spent some time digging through the code. It looks like when you use GenerationType.SEQUENCE, it uses the 'seqhilo' strategy instead of the normal sequence strategy. Setting the allocationSize = 1 in this case makes it so you get the proper value.

It looks like setting it to GenerationType.AUTO (or simply leaving it blank, which has the same effect) makes it use the 'native' strategy which uses the normal sequence strategy.

I don't know that this is a bug, but the behavior is a little unexpected. It seems as though either solution works equally well.

Here is my understanding of why this happens.

Hibernate uses the allocationSize value you specify in the SequenceGenerator annotation for calculating id values it assigns to persisted entities.

Hibernate keeps a block of available ids in memory and dishes them out from there until it runs out. Then it reads once from the DB sequence, which increments it. Hibernate uses the new sequence value to calculate the next block of available ids by multiplying the sequence value by the allocationSize value.

For instance, if you have allocationSize=20 and current sequence id at 1000 you get your entities assigned ids in 20000-20019 range (20 * 1000) for this cycle.

Hibernate does this as an optimization to avoid going to the DB for every new id.

As you found out, setting allocationSize to 1 forces sequence values match those of entity ids at the cost of running a sequence.nextval() query for each insert.

Ivan Gorgiev
Amdocs

haha dude

check that there is no cache specified in the oracle's sequence

simple

igorgiev,

I found Hibernate's behavior very strange with the Sequence Generator, so I tested and searched, and came to the same conclusion.

But I still am very confused by this allocationSize parameter. If only your application with Hibernate is using your database, it seems to work pretty fine. But in many Information Systems, many different applications are working with the same data. And the "normal" way to generate a primary key ( at least with Oracle) is to use a sequence with a name that relates to the name of the table. Every application must use this sequence, and the id to use is the sequence number... So int these environments, if you use Hibernate, you must use an allocationSize set to 1.
I really would be pleased if someone could explained me the choice that was made (with an default allocationSize to 50 for JPA...). It seems a bit dangerous in a multi-application environnement. Or the developper must be very aware of the way Hibernate works and of the behavior of "older" applications...

NB :
For the cache of the sequence, it has nothing to do with the number generated (it only specifies how many sequence numbers are to be preallocated and stored in memory for faster access). So from the database user (the application) point of view, the behavior of the sequence does not depend on the cache parameter (only for the performance)

Hi

I got some doubts in allocationSize.

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="myseq")
@SequenceGenerator(name="myseq",sequenceName="MY_SEQ",allocationSize=1)
private long id;

int allocationSize: Some databases can pre-allocate groups of sequence values. This allows the database to service sequence requests from cache, rather than physically incrementing the sequence with every request. This allocation size defaults to 50.

ie you have allocationSize=20 and current sequence id at 1000 you get your entities assigned ids in 20000-20019 range.Hibernate does this as an optimization to avoid going to the DB for every new id.


So if we set allocationSize=1, it will not store in the memory. It will be like calling sequence.nextval() query for each insert. So what is the advantage for this.

why it seems a bit dangerous in a multi-application environment.? Suppose two application say app1 and app2 is accessing the same sequence, and allocationSize=20, what will be the app1 and app2 sequence id's.Will both will get ids in 20000-20019 range.

thanks dude

The problem is not two *Hibernate* applications, It is one Hibernate application using the sequence in this "unusual" way, and other non-Hibernate applications using sequences in the normal way. it will be the non-Hibernate applications that get integrity violations when they try to insert rows with PKs that are already taken.

OK, this is native queries in JPA. Not really well documented. Of course I understand that we really should not be doing that, but hey there are times where it is just plain easier. The following code snippet goes after an Oracle sequence and gets the value.
EntityManager em = this.getEntityManager();
Query query = em.createNativeQuery("SELECT BLOG_ITEM_SEQ.nextval FROM DUAL");
Vector blogItemRow = (Vector) query.getSingleResult();
Integer blogItemId = (Integer) blogItemRow.elementAt(0);
As you can see by reading the Java code if you do a native query you can map the result to a Vector and then for each row you have a Vector for all the columns. In this particular case only one and the first column is an Integer.

Hey guys,

I was searching for some information on this very topic and stumbled across your thread.

So were do we stand? Is SeqHiLo the right way to go? What are people choosing when faced with this?

There are obvious performance gains but it leaves me wondering if we are leaving ourselves open to gremlins. Especially if we were to open the database up to 'other' applications for write, given we are designing an large domain holding its only a matter of time, then there is probably going to be a point when this bites us.

Would a middle ground be to issue a set nextVal after incrementing the sequence and determining the allocated range? That way legacy or third-party apps will play nicely still and the hibernate enabled applications get the performance goodies.

I see this is an old topic, but having recently been bitten in the ass by this unexpected behavior when we recently "upgraded" our Hibernate XML schema (using v3.3.1) to annotations (using v3.5.2) I'd like to respond to this question:



For our purposes, relying solely on Hibernate to generate sequence values is astoundingly dangerous. Like, string-concatination vs. named parameters dangerous. So dangerous I would almost say stupid, unless you have some truly magnificent reason, and even then I'd try and talk you out of it.

The problem we ran into is that the default for the annotation is to use the brain-dead HiLo generator, which as noted before can quickly grow your indexes geometrically. In my mind, if you are going through the trouble of specifying a particular sequence for Hibernate to use, that would tend to indicate that you don't want Hibernate to use its own methods.



Looking at this code, where we've gone through and identified the name of the DB sequence generator in three different places, you might be tempted to think this would actually use that sequence. Nope. HiLo.

Furthermore, relying on the allocationSize is another recipe for disaster. The only way you can fully trust Hibernate is if you can guarantee that you will never, ever touch the DB manually. In my entire professional life I have never encountered such a situation. Thus, as already recommended, if you want to use your database's sequence generator, you have to specify GenerationType.AUTO.