| sequence getting out of sync summary [message #506950] |
Mon, 11 January 2010 08:30  |
Eclipse User |
|
|
|
I have the impression that if one posts too many messages, they are not
read. So therefore a summary:
Sequences done with a table (@TableGenerator) can get out of sync if
JOIN_EXISTING_TRANSACTION is set to true.
With this setting, all SQL is done via the same connection; both entity
SQL and sequence table SQL. If during the database interaction a
database exception occurs (e.g. not null constraint), all updates are
rolled back, including changes to the sequence table. A sequence that
was increases is rolled back, but the value remains assigned to the
entity in memory.
In a second store attempt (after the reason of the exception was
removed) all is stored without error. However, because the entity
already had a PK assigned from the previous attempt, the sequence is not
incremented this time. This results in the situation where entities are
stored with a PK higher than the current value of the sequence.
When a new entity is stored, an exception will be thrown because of
duplicate keys.
The essence of the problem is that sequences can hand out the same value
multiple times. If @TableGenerator is emulating database sequences, then
that should not happen and would mean that sequence table SQL should
ALWAYS be done through its own connection, committed immediately, and
not be influenced by JOIN_EXISTING_TRANSACTION.
The only workaround, if using JOIN_EXISTING_TRANSACTION is a must, is to
use real database sequences. For more detail see the "sequence getting
out of sync" thread.
Tom
|
|
|
|
| Re: sequence getting out of sync summary [message #506974 is a reply to message #506966] |
Mon, 11 January 2010 10:12  |
| Eclipse User |
|
|
|
On 2010-01-11 10:57, Doug Clarke wrote:
> Tom,
>
> Thanks for the summary. Have you filed a bug with this summary?
>
> Doug
I have now
Bug 299285 has been added to the database
James had some suggestions on how to better to this, so I'll test that.
Tom
|
|
|
Powered by
FUDForum. Page generated in 0.03130 seconds
Home