Hi All,
I am facing a issue regarding commit and rollback of transactions in a nested service. I have the following :

Flow A

Start Transaction -A
try/Catch
try Block
Step a.1
Step a.3
Invoke service B
Invoke service C
commit Transaction -A
catch
rollback Transaction - A

Flow B

Start Transaction - B
Try/catch Block
try Block
adapter Service b.1
commit Transaction - B (INSERT)
catch Block
rollback Transaction - B

Flow C
Start Transaction - C
Try/catch Block
try block
adapter service c.1 (UPDATE)
commit Transaction - C
catch Block
rollback Transaction - C

This is the structure of the services that need to be created. I have managed to put them all under one Parent transaction where i do not have individual transactions for each flow, and that works fine for both commit and rollback.
Problem : I am unable to successfully execute the above scenario where i would like to have individual transactions for each flow. In the case that i let the art manage the transactions i was getting the error “Error unable to close/commit transaction 1001 when transaction 999 is active” (something pretty much like this. Following which i placed explict “startTransaction” names giving them “a”, “b” and “c” in this case too i faced similar problem of “Error unable to close/commit transaction c when transaction a is active”, however the INSERT(FLOW 2) happenend successfully. Finally i tried giving all the 3 transactions the same name “a” where i got another error “Unable to start transaction ‘a’ its already open”(well guess that i am pretty memorizing all the error types )

I am making use of a LOCAL_TRANSACTION connection for all of the above and they are all pointing the same database , Oracle 10g. The version of the JDBC adapter is 6.0 and the version of the IS is 6.5 SP2.

Appreciate all help
Thanks and regards
-jk

I’m not a DB expert by any means, but nested transactions seem to be universally problematic. I see a couple of possibilities that may work for what you’re trying to do:

1. Don’t nest the transactions. If they are independent activities, then create a new top-level service that calls A, B, and C sequentially, each with their own start/commit/rollback blocks.

2. Conditionally start a transaction in B and C. If you want B and C to be independently callable AND callable from the context of A, define B and C such that they accept a conditional tran ID. If tran ID isn’t provided, then start a new tran and commit/rollback. If a tran ID is present, then just use it in the insert/update steps. You’ll need to mess around a little to get the try/catch blocks to work right in both contexts.

Hope this helps.

Thanks for the suggestions Reamon,
The criterion 1 does not apply in this case, as the business case is to have individual transactions also as Flows B and C should be capable of being invoke as parts of other Transaction(definitely not entertaining a thought) and i do not have any manovering space to change this.
For the second suggestion, i had tried putting a branch at the starting of flows B and C where i check for the value of %startTransactionOutput/transactionName% (hope i have it spelt right). In the case it does not exist i have a StartTransaction step, in the other case i do nothing(expecting it to be available in the pipeline. Coming further in the CommitTransaction step i have the value of the same %startTransactionOutput/transactionName% in the input for the Commit and Rollback steps. However in this case in the debugLog step i see the error “Cannot commit transaction with transaction Name null”.
But i see a value of errors/pipeline/startTranactionOutput/TransactionName = “a” the value which is in the pipeline.
I guess that i am missing something really simple out here :o .
Thanks
-jk

If B and C can be invoked only as parts of other transactions (e.g. always in combination with some parent service) then I wouldn’t put transaction logic in those services at all.

For the issue you’re seeing, search the forums for managing variables in try/catch blocks. A var that is “declared” within a try block will not be directly visible within the catch block. There is a thread specifically on “when to use startTransaction outside try/catch.”