I have further narrowed the issue. My earlier posts describe an out-of-sync SEQ table in Oracle. For an attempted workaround, I modified dba.OraclePKGenerator to return a 0 if the table was the one in question. This would force my trigger in the database to assign a primary key, thus bypassing the "select inbox_seq.nextvalue from dual".
When I ran the query again, the INBOX_SEQ was set to 40. Cayenne assigned the following primary keys in the batch insert:
40,1,2,3,4,5,6,7,8,9,10,11,12,....
This leads me to believe that batch updates will go to the SEQ for the first pk, then increment the pk, NEVER going back to the SEQ to keep it in sync.
Are my suspicions correct?
---------- Original Message -------------
Subject: Re: JBoss transactions
Date: Tue, 24 Aug 2004 14:52:07 -0400
From: Andrus Adamchik <andru..bjectstyle.org>
To: cayenne-use..bjectstyle.org
On Aug 24, 2004, at 1:31 PM, David Norwood wrote:
> this is always in the log file after each DB access (probably correct):
>
> 2004-08-23 18:03:10,305 INFO
> [org.objectstyle.cayenne.access.QueryLogger] +++ no commit -
> transaction controlled externally.
Yes, that's expected. Cayenne simply tells that it is done working with
DB, and now it is up to the container to commit.
> I'm getting table updates ok, (ie, the table gets the row) but the
> Oracle sequence doesn't seem to be incrementing. So when I enter
> another row, I get a pk unique violation, and it's because the
> sequence hasn't been incremented. If I then force several updates,
> even though I get an error each time, the sequence increments, and
> eventually the table will accept the row.
Hmm, as far as I know Oracle sequences are not transactional in a
traditional sense. So new sequence value is immediately visible to all
transactions without a need to do a commit. I just did a quick test to
confirm my sanity, and this is indeed so, at least when using SQLPlus
on Oracle 9i.
Could you track down what exactly happens in your case? Do you have a
log confirming that the same PK number is being used twice within an
application? The one that you posted shows the failure, but not where
the other record was inserted. There maybe other reasons why you have
duplicate PKs in the db (e.g. manual rows insert).
Andrus
This archive was generated by hypermail 2.0.0 : Wed Aug 25 2004 - 02:53:00 EDT