In my last Project, I was using a DB adapter in my BPEL code. It was using an Oracle seq 'DEMO_INSERT_S' as below -
Problem -
Even though the sequence was an Oracle generated sequence, I faced a unique issue in PROD. The sequence was incrementing of its own accord with no relation whatsoever with the actual seq.NEXTVAL. So while the DB adapter was generating a seq value say 101, it so happened that 101 already existed in the database resulting in a UNIQUE CONSTRAINT error! It took us some time to figure out the issue to be as follows -
So instead of providing the SEQUENCE in the DB adapter and let it have a mind of its own, we removed it from there and provided the Oracle sequence generated NEXTVAL in the xsl transformation.
So here's the mapping for the primary key of the DEMO_INSERT_TABLE. We are no longer dependent on the DB adapter for the sequence, instead we use the bpel extension function oraext:sequence-next-
<ns1:
 |
| DB Adapter with Sequence |
Problem -
Even though the sequence was an Oracle generated sequence, I faced a unique issue in PROD. The sequence was incrementing of its own accord with no relation whatsoever with the actual seq.NEXTVAL. So while the DB adapter was generating a seq value say 101, it so happened that 101 already existed in the database resulting in a UNIQUE CONSTRAINT error! It took us some time to figure out the issue to be as follows -
Oracle BPEL DB Adapter follows or rather maintains a sequence of its own and does not exactly use the oracle generated seq.NEXTVAL.Workaround -
So instead of providing the SEQUENCE in the DB adapter and let it have a mind of its own, we removed it from there and provided the Oracle sequence generated NEXTVAL in the xsl transformation.
So here's the mapping for the primary key of the DEMO_INSERT_TABLE. We are no longer dependent on the DB adapter for the sequence, instead we use the bpel extension function oraext:sequence-next-
<ns1:
<xsl:for-each select="/ns0:
<ns1:GglPlmItemUpload>
<!--ns1:id>
<xsl:value-of select="oraext:sequence-next-
</ns1:id-->
<!-- other column mappings -->
</ns1:
This resolved my issue and hope it helps you too! 
I have an idea what happened. The sequence you selected probably had an increment by of 1. However there is a setting in DbAdapter/EclipseLink which is called sequencePreallocationSize, defaulting to 50. You should see it in DbAdapter connection factory definition. This value has to match the increment by exactly. If it is greater, then on getting number say 500, it will "cache" keys 500-549. Then after using all those, it will get seq.NEXTVAL, which may return 501, leading to unique constraint violation.
ReplyDeleteIf you create a new sequence from the UI it will create it with increment by 50 to match the EclipseLink default. There is definitely a vulnerability if you use a different sequence. Unfortunately there is no easy way to detect a potential mismatch.
The DbAdapter sequencing feature may be a little faster than the xsl approach, if only because it can cache multiple keys leading to better performance.
Thanks Stephen... Saved my day
DeleteThanks Stephen and Aparajeeta. Your post and reply saved our day. Interestingly, if we change the db sequence to increment by 50, do you see this to be an advantage in terms of performance and clustered setup.
ReplyDeleteHi Guys Thanks for this post it really helped in Prod
ReplyDelete