Hibernate and negative primarykeys on Oracle

I encounter today a common problem with Hibernate generating negative primary keys on Oracle.

If you are facing the problem described in the title of this post, you may jump directly to the end / the Solution.

Or, you may read the full story ;)

On JBoss 7.1.1 + Oracle, I declare entities like

@Id
@Column(name = "pk", insertable = false, updatable = false)
@GeneratedValue(strategy = GenerationType.AUTO, generator = "seq") 

@SequenceGenerator(name = "seq", sequenceName = "participant_pk_seq") 

Then, I added parameter allocationSize = 1 to @SequenceGenerator, because most of posts of smart people on the Web tells you to do it, but don't explain really why. And that this also mentioned on that confusing issue / still open on Hibernate bugtracker : :

https://hibernate.atlassian.net/browse/HHH-6486

So I did it, without understanding and it worked.
... Except from a oneToMany entity leaf, that was still set to
allocationSize = 12

And that was the begining of a very bad afternoon.

I noticed that generated primary keys, starts from 1 and then
-11
-10
-9
...
and then bang up to 1 with a ORA-00001.

So after a truncate, restart, it seems ok.
After having restarting my app server app : bing ! an other contraint exception is raised. Reloading my app, save my form oups seems OK ? sequence seems ok.
But after the next app server restart : bang again ! Another ORA-00001.
Cool down. This is only the fouth dimention.. great !

The Jboss RTFM wasn't not very helpful

http://docs.jboss.org/hibernate/stable/annotations/reference/en/html_single/#entity-mapping-identifier

Confusing : they copy-pasted the recommendation of hibernate about persistence.xml property
"We recommend all new projects to use hibernate.id.new_generator_mappings=true"

But actually, Jboss 7 is doing it behind the scene / just for you, because they are very kind, and that you are very stupid and don't have to know about those very complicated settings.

the other RTFM adds some more confusion
https://docs.jboss.org/author/display/AS71/JPA+Reference+Guide#JPAReferenceGuide-Persistenceunitproperties
with the differences between GenerationType.AUTO and GenerationType.SEQUENCE

Then, I had 2 options.

I could either
1) hide the shit under the carpet with
allocationSize=1,
Meaning that a select ....nextval from dual will be done before each inserts.

and/or playing all the night long with the settings
hibernate.id.new_generator_mappings to false in my persistence.xml

OR
2) digging deeper into the Internet.

 I choosed option2.

I found this very intersting blog post talking about the risks of setting all allocationSize=1:

http://itdevworld.wordpress.com/2009/12/20/hibernate-sequencegenerator-with-allocationsize1-leads-to-huge-contention/

that post saved my day...

Actually allocationSize is a range of primarykey values reserved for Hibernate.
And the select ....nextval from dual is be done only after hibernate consumed this range of primarykeys.

So the same value MUST be the same on both allocationSize (Hibernate) AND sequence increment (DB)

When allocationSize=12, sequence creation on Oracle is

    create sequence participant_pk_seq
           MINVALUE 1
           MAXVALUE 999999999999999999999999999
           START WITH 1
           INCREMENT BY 12
           NOCACHE
           NOCYCLE;


On the Hibernate side, first call to persist my entities will allocate primary key range for instance
1...12

asking Oracle select participant_pk_seq.nextVal from dual
returns 13
and Hibernate fills its range until matching the end of its allocation / at 12.
Only after reaching primarykey=12 Hibernates asks again for  seq.nextVal for a new allocation.

So the only change to do in my application was... to change nothing in my application ;)
The change was on the DB side !

Post Scriptum.

@Target({TYPE, METHOD, FIELD})
@Retention(RUNTIME)
public @interface SequenceGenerator {

    String name();

    String sequenceName() default "";

    String catalog() default "";

    String schema() default "";

    int initialValue() default 1;

    int allocationSize() default 50;
}

Conclusion:

If allocationSize is not declared on the entity side, as default allocation size is 50 ALL sequences  MUST declare INCREMENT BY 50 on the DB side.

This would be the recommended approach

1) By Default, declare all sequences with
      INCREMENT BY 50
2) Do not specify any allocationSize on entities
3) Except from entities that are expected to be inserted more than 50 at once, In that case, both allocationSize and increment size must be the same.