Re: [eclipselink-users] Sequencing strategy and collisions

[cowwoc <cowwoc@xxxxxxxxxxxxxxxx>]

Haha! I especially enjoyed reading
http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Advanced_Sequencing
section "Running Out of Numbers" for "paranoid delusional [...] programmers"
such as myself :)

I think you misunderstood my question about having one row per table,
though. What I meant is that I expected Eclipselink to have a single
SEQUENCE table, where each row would contain the name of a separate table
and its own maximum-id number. I seemed to have gotten that desired behavior
using pure JPA calls:

@GeneratedValue(generator = "myTableName")

I also wrote "doesn't this mechanism make it easier to cluster or split the
database in the future as your business grows?" What I meant here is that
say in the future you split your application across multiple computers, each
having a logically separate database such that computer 1 contains one set
of tables and computer 2 contains a different set of tables. In that case,
wouldn't you be better off splitting a row-per-table sequence number
generator than trying to split and potentially re-join (in the future) a
single logical sequence number table?

Thank you,
Gili


James Sutherland wrote:
> 
> You can set the "initialValue" of a sequence TableGenerator to the current
> maximum assign id if you are migrating existing data.  You could also
> update the sequence table row directly using SQL to the max assigned
> value.  You could continue to use IDENTITY if you wish, but in general I
> would not recommend IDENETITY because you cannot pre-allocate.
> 
> JPA does not support a single row sequence table, but EclipseLink does
> using its UnaryTableSequence.  With a logical sequence pre-allocation size
> the sequence allocation is rarely a performance or contention issue, so I
> would not be concerned with having a single table.
> 
> For more information see,
> 
> http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Sequencing
> 
> and,
> 
> http://en.wikibooks.org/wiki/Java_Persistence/Identity_and_Sequencing#Advanced_Sequencing
> 
> 
> 
> cowwoc wrote:
>> 
>> Hi,
>> 
>> I am migrating a database from Hibernate to EclipseLink and in the
>> process the sequencing strategy has migrated from IDENTITY to AUTO under
>> the MySQL database. All of a sudden I am getting ID collisions because
>> the "sequence" table only contains one row ("seq_gen", maximumId) and in
>> my case I've already got existing Ids larger than maximumId (which
>> defaulted to zero).
>> 
>> My question is:
>> 
>> - How is the default sequencing mechanism supposed to avoid collisions?
>> Does it simply assume that the id field will be big enough and will never
>> roll over? Even if I fix maximumId to be large enough for my existing
>> dataset, how does it guarantee no collisions in the future?
>> 
>> - What are the benefits of a global sequence number versus per-table
>> sequence number?
>> - How does one get JPA to use one row per table it is sequencing? Doesn't
>> this mechanism make it easier to cluster or split the database in the
>> future as your business grows?
>> - What is the ideal sequencing strategy in MySQL?
>> 
>> Thank you,
>> Gili
>> 
> 
> 

-- 
View this message in context: http://www.nabble.com/Sequencing-strategy-and-collisions-tp19266315p19272275.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.