Markus,
It's not a bug, OracleSequenceDefinition behaves as it should.
In 9.1.37 SequenceGenerator Annotation, the spec. states:
...
int allocationSize (Optional) The amount to increment by when allocating
sequence numbers from the sequence.
That's exactly what OracleSequenceDefinition does.
allocationSize has no correlation with Oracle sequence cache size (BTW it's
available in Oracle 9, and I think in Oracle 8, too),
though they play similar roles - one for TopLink-enabled application,
another for Oracle database.
Here's how it works:
suppose the last value obtained from Oracle sequence my_seq was 50 (that
means the numbers <= 50 could have been already used);
suppose that it's increment by parameter is 50, too;
select my_seq.nextval from dual returns 100;
TopLink allocates the following allocationSize numbers for its exclusive
use: 100 - allocationSize + 1,..., 100
For TopLink application to use all the numbers, allocationSize should be
equal to "increment by" parameter of the corresponding Oracle sequnce
my_seq:
in that case the numbers allocated by TopLink are: 51, ..., 100.
TopLink application may choose to use allocationSize < 50, for instance,
allocationSize=1 causes preallolcation of a single value: 100 - and
therefore numbers 51, .., 99 will be lost.
However allocationSize>50 may cause unique constraint vialation - in case
allocationSize=51, TopLink allocates for its exclusive use 50,...,100 - but
50 may have been already used by another app., and possibly as a pk value
for the same table.
Summing up:
1. "allocationSize" of TopLink sequence should be the same as "increment by"
of the corresponding Oracle sequence.
2. The higher allocationSize is, the less times TopLink application selects
sequence from the db (50 vs 1 means 50 times less queries).
3. The draw back of high preallocation size is that when TopLink application
shuts down the sequence values it has preallocated are lost.
Thanks,
Andrei
>
>
> -------- Original Message --------
> Subject: Bug in OracleSequenceDefinition.java
> Date: Sat, 04 Nov 2006 11:14:36 +0100
> From: Markus KARG <markus.karg_at_gmx.net>
> Reply-To: dev_at_glassfish.dev.java.net
> To: dev_at_glassfish.dev.java.net
>
>
>
> 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
>
>