Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] How eclipselink handles Sequences?

Hi James,
    I think I found a solution to my problem...
   Here is what I did:
           Application Server: Glassfish V2.1
           Database Sequence: Increment=1,Cache=100
           Sequence Pre Allocation Size Annotation on Entity=100
  I tried with parallelly/simultaneously populating the data with three different applications: eclipselink based application,pgAdmin, psql (command line shell for postgres); I'm happy to say that everything worked fine with out any collisions  and no gaps in numbers generated by the sequence. Of course, if any of the client has fetched a set of numbers(specified by the Cache size) and closed the connection before completely using up the set of numbers fetched, then there will be unused ids. However, we can eliminate this by reducing the cache size to 50 or 25, based on our needs.
So, the solution is not to synchronize allocation size with sequence increment value but to sysnchronize the allocation size with Cache size of the sequence.
In this way, we can eliminate the collision of numbers as well as reduce the gaps that may be produced in case of Increment_Value Vs allocationSize sync.. 
If you find any issues with this approach, can you please explain those?
Thanks and Regards,
On Wed, Nov 11, 2009 at 1:28 PM, Samba <saasira@xxxxxxxxx> wrote:
Hi James,
    Thanks for your response.
I have a need where the same database is used by three different applications: eclipselink , JDBC(legacy), and stored procedures which are used mainly during bulk operations and upgrade scenarios.

In such a scenario, I cannot have that luxury of unused id values, as there will be too many over a period of time and the sequence soon becomes exhausted; may be I'm exaggerating when I say that this may happen soon but nevertheless, there will be 50 times lesser available ids, provided allocation size is set at 50.

By the way, I suppose that incrementing a sequence value will be DML and not DDL, after all we are not altering the name of the sequence  or dropping it, and databases are capable of handling concurrency.

In fact, eclipselink is behaving correctly in case of Table Sequences and updating the SEQUIENCE_COUNT column by 50 when ever it prefetches id values by that number.

So, why is this differential treatment in case of native Sequences? shouldn't eclipselink increment the sequence value by 50  as it does in case of Table Sequence?


On Tue, Nov 10, 2009 at 9:41 PM, James Sutherland <jamesssss@xxxxxxxxx> wrote:

The purpose of sequence preallocation is to improve performance.  If you want
your increment to be 1, then set your preallocation size to 1, and there
will be no issues.  If you want to improve performance, then you need to set
your increment to the preallocation size, if this is say 50, this will not
cause any issues with the other application if it assumes it is 1, ids will
not collide, you will have some unused ids, but that should not be an issue.

Changing DDL objects on the fly, is not a very good idea.

saasira wrote:
> This following is quoted from the eclipselink User Guide :
> "The key difference between Oracle Type Sequencing process and the process
> involved in table sequencing is that EclipseLink is unaware of the
> INCREMENTconstruct on the
> SEQUENCE object. EclipseLink sequencing and the Oracle SEQUENCE object
> operate in isolation. To avoid sequencing errors in the application, set
> the
> EclipseLink preallocation size and the Oracle SEQUENCE object INCREMENT to
> the same value. Note that the Oracle sequence object must have a starting
> value equal to the preallocation size because when EclipseLink gets the
> next
> sequence value, it assume it has the previous preallocation size of
> values."
> We have a scenario where the database is updated not only by an eclipse
> link
> based application but by stored procedures as well. for that matter, I
> believe that we cannot make an assumption that the eclipse link is owning
> the database and operates on it in isolation.
> So, if we use the increment value of the  sequence to the preallocation
> size
> defined on  the eclipse link entity definition, then there will be a lot
> of
>  sequence values unused.
> But if we set the increment value of the sequence to nominal 1, then there
> is every chance of collision with the values generated by sequence and
> that
> generated by eclipse link.
> Now my question is why can't the eclipse link update the sequence object
> by
> the preallocation size when ever it want to prefetch values? why is it
> updating only by the original increment size of the sequence even though
> it
> is presuming it acquired the amount o values mentioned in the
> preallocation
> size in the entity?
> for example, had eclipse link done some thing like this:
> on Oracle:
> ALTER SEQUENCE SEQUENCE_NAME INCREMENT BY 'preallocation_size'; where ever
> eclipse link wanted to prefetch the values
> and then resetting it back to
> then there can be no error at any point , who ever may update the database
> from any where as long as sequence is properly used.
> Can this be achieved by any setting by which I can force elipselink to
> always update the sequence value by preallocation size and not the
> Thanks and Regards,
> Samba

View this message in context:
Sent from the EclipseLink - Users mailing list archive at

eclipselink-users mailing list

Back to the top