Re: [Springframework-developer] Problem with MySqlMaxValueIncrementer

[<tri...@tr...>]

Isabelle,

> There is a problem with MySQLMaxValueIncrementer (and I think with the Oracle
> one as well). Currently the code doesn't do any transaction management which
> has 3 consequences :
> (1) if the calling code does a rollback, the value(s) cached in / handed out
> by the object are invalid

I'm not sure what you mean by invalid.  If the caller rolls back the
transaction, then the retrieved keys are wasted - there will be gaps.  The
sequence should be independent of any transaction, at least that is how Oracle's
sequence works.  I beleive most auto-increment implementations work the same way.

> (2) the code requires the caller to do a commit, which I'm sure most people
> will forget to do if using the framework.

I'm not sure about this one either.  In what way is the caller required to do a
commit.  They are only required to do a commit if they explicitly started a
transaction.  If not then they should be in either "container managed" mode or
auto-commit.

> (3) when requesting a bunch of keys, it is possible to get a series of
> non-sequential numbers from the database. This because of the loop in the
> code, which is requred by mySQL last_insert_id() semantics. This particular
> problem could be solved by maintaining an array of values, instead of just a
> maxId. I'll make this change over the weekend. This particular problem won't
> show up in the Oracle implementation.

The MySQL implementation that I have seen for a "sequence" is this:

  create table sequence (id int not null);
  insert into sequence values(0);

and to increment it you do:

  update sequence set id=last_insert_id(id+1);

then last_insert_id() will give you the next sequnce value to use.

If you want to get a batch of id:s could you not use:

  update sequence set id=last_insert_id(id+?);

where ? is your batch size.

This way there is no need to loop and later clean up the extra inserted rows.  I
would think that a simple update would perform better than insert/delete
statements as well.

For the Oracle implementation it looks like we are using the INCREMENT_BY value
from an Oracle sequence to use as a cached batch of id:s.  I'm not sure that is
a good idea since we don't know for sure why that INCREMENT_BY value is beeing
used.  Maybe the DBA wanted to space out the keys or only use odd numbers - who
knows.  Plus, I don't think our code would work with negative INCREMENT_BY
values.  As far as I know there is no way to figure out what the initial value
of the sequence was.  There is a min and a max value, but neither one is certain
to be the value where the sequence was set to start.

I would suggest that we try to fix these issues before the 0.8 release, or if
there is not enough time then we should skip the caching for now, and just
retrieve the next value from the sequence.  We could add caching in .9 or 1.0 if
we come up with a workable solution.

--Thomas