|sequence getting out of sync summary [message #506950]
||Mon, 11 January 2010 13:30
| Tom Eugelink
Registered: July 2009
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
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.
Powered by FUDForum
. Page generated in 0.02126 seconds