I guess it is important to know the datamodel, and triggers of your master/detail tables. Could you elaborate on that. You did not recreate the sequences?
Hi Martien - I will try to get the DB details if possible but just wondering how a migration could impact the the design. Since we did not changed anything on DB end and DB adapter setting remain same, same code is working in 11g and not in 12c.
You did not recreate the sequences? - no, we did not changed anything on sequencing side. Strange side is, sometime the request work but 70% time it fail with given error.
I see similar issue also posted in thread
Oh, did you re-build/regenerated the db-adapter specification? Did you re-enter the wizard?
You should re-check the primary key definition in your specification.
If you did not touch the database, then the primary keys should be in par with the sequences. That means that doing a new insert, the sequence would create a new, bigger value, that cannot conflict.
Oracle sequences are non-transactional and atomic, which means that you always get a new value that is not rollbacked and that is not conflicting with another session.
So that cannot be a problem.
What can be a problem is that a querying database adapter does a select, but that the pk definition does not match. The thing with that is that TopLink duplicates the first row for every row in the selection. For instance, if the select would result in 5 rows, in stead of 5 different rows, you'll get 5 times the first row. If that selection is base for the new insert, then you might get problems with keys.
So you might need to check also the adapter config of the adapters the are input for your inserting adapter.
Thanks Martien van den Akker -
Yes, you got the issue correct and there is no change in code or sequence side so ideally that should work as expected. And strangely, there is no specific pattern, say if I send 10 request now, I might get 50% successfully insert in DB and in next attempt after few minute or so, there could be 10% only.
I also dont see this as an issue the way DB configuration and sequence and primary are same.
do you have any clue on what could be changed on adapter config side. we already modified the SequencePreallocationSize to 1. it was default 50 before.
We also tried to generate the adapter freshly and saw the same issue. so even I suspect this as an issue with some adapter level stuff.
I need to know more about your code. Could you share more on how you set things up? SHow some screendumps, etc.? Because of 'java.sql.BatchUpdateException' it seems multiple rows are affected.
What do you use for sequencing ('...generate the unique id in each table...'). Do you use Oracle Sequences?
It seems that it is a session thing, where two parallel flow instances are inserting but where one session is not committed and the other session happened to determine the same id value, because it did not 'see' the commit of the other session yet.
This can happen if you generate an id as 'select max(id) + 1 from table'.
I am having the similar issue when we are testing SOA 11G composites on SOA12c. Just want to check to see that you find any resolution to this issue. Any help is highly appreciated.
Thanks for the details.
We figure out the issue. The issue is with the data source where in SOA 12c mistakenly value for this data source property SequencePreallocationSize set as 50 instead of 1.
Ahhhh. That is a good one!
Thanks for the feed back.