I have a flow service that inserts data in to 3 tables (each table insert via a JDBC Adapter service). The 3 inserts are done via the same JDBC Connection. Im using JDBC Adapter 6.5, patch 23.
Implicit transactions work when the flow service is invoked directly from developer (run, not debug). That is, if the third insert fails, the first and second inserts are not committed to the database.
This is what I expect.
Implicit transactions DON’T work when the same flow service is invoked from a process model. That is, if the third insert fails, the first and second insert are retained in the database – they are NOT rolled back.
I’ve tried XA_TRANSACTION and LOCAL_TRANSACTION as the transaction type on the JDBC Connector – it does not make a difference. Basically, I’m getting auto commit on each insert when the flow service is invoked from a Process Model.
Question(s):
Is this expected behavior?
Is there anything I can do to have Implicit Transactions work in both cases?
Thanks! I know that there have been a good number of threads on the subject, but I still have yet to figure it out.
Not sure if this is the case for you or not. When you say the process model makes the call, is your code within the process model flow code? If yes I would try and create a seperate service.
e.g.
PrcMyProcess\MyProcess:stepx calls MyIntegration\MyIntegration\folder:doWhateverJDBC
Back in the days I found that having code in the process flow step itself caused some strange issues. Can’t remember exactly what the problem was though.
Good point of clarification and thank you for your response.
The Process step does call a flow service as you describe.
(PrcMyProcess\MyProcess:stepx calls MyIntegration\MyIntegration\folder:doWhateverJDBC)
I think I know what the deal is. The Process owns the parent Transaction context.
In reviewing the logs, I see that
When the Process starts, a “Beginning transaction” is logged.
When the Process finishes, a “Committing transaction” is logged.
The process always does a commit - even when my IS flow service Exits $flow with FAILURE.
There is never an attempt to rollback the transaction. The Process makes its own db calls as to its processing state. Those have to be committed. And since the flow service is in its transaction context, its db inserts are implicitly committed also.
I was able to get the flow service to commit or rollback via Explicit transaction management. I would rather use Implicit transactions, but it works.
BTW - I was not able to do Explicit transaction management because my JDBC Adapter Services were using a JDBC Adapter connection that was already in the parent transaction context. I needed to create a new JDBC Adapter connection (in IS) and change my JDBC Adapter Services to use the new connection. Then I could explicitly call startTransaction and or commitTransaction/rollbackTransaction in my flow service.
I doubt that any of this is addressed in the Integration Server docs (been looking). Its most likely in the domain of “Designer” documentation.
Thanks to anyone who made it to the end of this post.
Blee,
Good observation… but i would like to point out something here…
As you said, Implicit tran of a service (when invoked through process model activity step) will not maintain tran as it maintains in normal flow service.
Only through explicit tran, transaction functionality can be achieved.
"The process always does a commit - even when my IS flow service Exits $flow with FAILURE. "
DO NOT USE EXIT while checking this behaviour… When you have a update statement, exit, followed by another update, both implicit and explicit tran behaves the same way.