I have a problem with using local transactions. I have a flow service in which I am using two adapter services one pointing to Oracle and the other pointing to Sybase systems. Both the adapter connections are using LOCAL_TRANSACTION only.
When i tried executing the service it worked fine initially. Later on I got a problem saying “Transaction is active”. Then I went in implementing the start and commit transactions for which it worked fine for some days. But now it throws an error saying “unable to commmit transaction”. I have put the start and commit transactions to the adapter service which is connecting to Sybase system and the Oracle adapter service do not have any transaction.
I have tried many ways to solve this but could not. I have tried in disabling and enabling the cascade adapter connection multiple times and also tried by removing the transactions totally.It still throws the same error. Can anyone throw some light on this.
Try to build a wrapper service for each connection and invoke in a main service,this the best practice when using different db connections especially for Local_Transactions.
In my flow service I am using JDBC adapter service which inserts into a database. I am using Start, commit and Rollback transactions in this flow service.
When I am running the service i am getting the error in commit transaction step and the transaction is rolling back. I have set the transaction names correctly. I am not sure why the error is coming out.
Please make sure the transactionName set in the start/commit/rollback should be the same and see if there is any typo or space put in the commit step.Always map the startTransaction output/transactionName from the pipeline to input of commitTransaction.So this makesure names are correctly mapped.
what is the exact error are you seeing when you directly run the service?do not step thru.
Anil,
It is my understanding that you cannot use two different connections with LOCAL_TRANSACTION declared. I believe this is stated in the JDBC Adapter user’s guide, but as support explained it to me once, a “transaction context” is created when the first adapter service is executed and this context can only use the connection with which it was established.
They also mentioned that you shouldn’t use the explicit transaction services (start/commit/rollback) when LOCAL_TRANSACTION is declared, as the transactionality will be handled implicitly by the Adapter and this can cause overlap.
In your case, you may have to use connections of type XA_TRANSACTION. I believe you would then need to continue using the explicit transaction services in order to start/commit/rollback. HTH.
Anil,
I made use of Start Transaction/Commit Transaction for every JDBC call I make and used the roll back in the CATCH Sequence. It never gave me a problem. Yes it says the Transaction is active when you are UNIT Testing or debugging the code by using the Trace step by step option but under normal execution it never gave me any probs and worked well. Like RMG said just make sure of the Transaction names and that should solve the issue.
If you are having multiple DB calls it would be better if you drop the individual TransactionNames at each Commit as in the CATCH SEQ, you would roll back the open Transaction.