I think I have found a severe bug in OracleSequenceDefinition.java and
like to ask the original author of that file to verify this. If it is a
bug, I like you to add an item to the bug tracker on your own. I don't
want to file a bug that maybe is not a bug but a feature. So I leave it
up to your decision, since I am not an Oracle user actually.
The problem I detected is: OracleSequenceDefinition.java mixes up
"preallocationSize" and "increment". Let me explain why it is wrong to
do that:
Java EE 5 has added support for SequenceGenerator by using the tag
@GeneratedValue(type = SEQUENCE). SequenceGenerator can be customized by
supplying "preallocationSize" and "startValue". "startValue" is the
first number that shall be returned. "preallocationSize" is the count of
numbers that the database should allocate in one block and then kept in
memory for better performance. In fact, it is NOT the difference between
two generated IDs. That value would be "increment", which is neither
existing nor constrained in Java EE 5 -- most users would expect it to
be 1, as this is the default with Oracle.
Oracle 9 did not support preallocating but only "startValue" and
"increment".
Oracle 10 supports preallocating by the new "CACHE" key word.
Unfortunately, the implementation of OracleSequenceDefinition.java is
using the "preallocationSize" value with the "INCREMENT BY" keyword. As
a result, the sequence will not produce numbers "startValue,
startValue+1, startValue+2, ..." but instead produce numbers
"startValue, startValue+preallocationSize,
startValue+2*preallocationSize, ...). In fact, this is wrong and leads
to overly fast running out of values, since the default preallocation
size is 50!
Please check the definitions for "startValue" and "preallocationSize" of
"SequenceGenerator" using the following links:
http://java.sun.com/javaee/5/docs/api/javax/persistence/SequenceGenerator.html#allocationSize()
http://solarmetric.netmar.com/Software/Documentation/4.0.0/docs/full/html/ejb3_overview_mapping_sequence.html
The latter says: "|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.", which is
quite clear I think: The allocationSize is NOT the increment value. The
increment value is expected to be 1 by most users!
Also please check the oracle SQL description to learn about the
difference between INCREMENT BY and CACHE:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm
It says: "CACHE Specify how many values of the sequence the database
preallocates and keeps in memory for faster access.". Here clearly is
described that CACHE is the right choice for the "allocationSize" value.
I hope that my email is of any use for you and that I helped to identify
a bug.
Have Fun
Markus