Hello Glen,
Using sequence numbers for a table that may include values not
acquired from the same sequence mechanism is quite error prone.
Whatever application is inserting the rows outside of TopLink should be
using the same sequencing mechanism and updating the sequence table to
remove those sequences.
The problem here is that the transaction that is updating the sequence
table is failing because of the Unique Constraint violation so the
update to the table is rolled back. The very next time TopLink attempts
to acquire sequences it will get the same values returned from the
sequence table and try to use those again.
If it is too difficult to have the other application use the Sequence
table for sequence values then Native Sequencing should be used.
I have filed an enhancement/bug to have TopLink attempt to track the
used sequence numbers and throw them away if they are retrieved again
but this resolves only this specific problem as the loop is only
conditionally preempted.
--Gordon
Drinkwater, GJ (Glen) wrote:
> Hi
>
> I have an application that uses JMS to persist entities to a DB, I am
> using Glassfish UR1 using table generator for the primary key generator,
> using the default allocation size as 50. One problem that I had was
> when an unexpected row in the DB appeared.
>
> The table sequence count was say 50, meaning that the application held
> 51-100 in memory for primary keys, and a row appeared in at 101
> unexpectedly. I am not sure how this came about, the application is the
> only thing using this DB, but I have been testing and restarting the
> application a lot.
>
> When a message was sent to the JMS, when the sequence count was 100, the
> entity threw a
>
> java.sql.SQLException: ORA-00001: unique constraint violated
>
> Which is correct, but because this message was rolled back, the message
> was returned back to the queue and tried again. This again failed and
> this result repeated itself. This obviously hammered the DB and
> virtually ground it to a halt.
>
> The first thing is where/how can I configure glassfish to set the retry
> limit. At the moment I am manually seeing if the message has been
> redelivered with getJMSRedelivered().
>
> Secondly, why does toplink not throw away the invalid primary key and
> then use 102. After further inspection, if I manually put any other row
> into the DB, instead of the first one after the application has held 50,
> ie not 101 but 103,104 etc , toplink tries to save the entity, throws
> the exception and then uses the next number in the sequence.
>
> So, if a row appears in the table with a primary key above the set
> sequence count in the sequence table, toplink can handle this if it is
> not the first one in the held memory count (i.e. not 51,101,151). This
> means that either if I check redelivery and then leave the entity all
> other messages sent to the queue will be left because toplink always
> tries the invalid primary key or the message is constantly resent and
> fails and hogs the DB.
>
> I know that this row should have not appeared in the table higher than
> the sequence count in the sequence table but I need to figure out
> firstly why this happened, and secondly if I can stop toplink/glassfish
> from trying the same invalid PK if this happens again.
>
> Thanks Glen
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: users-help_at_glassfish.dev.java.net
>
>