How to commit or roll back all local transactions at the same time?

, ,
1

Hi,
I’m building a few adapter services using one db connection with local_transactions type. These services will update the data in a few different tables. If any one of update fails, I want to roll back all transactions, so it means all transactions commit or roll back must be completed at the same time. I did not see any hint in the Adapter User’s Guide. How can I solve this issue?
Your help is really appreciated!

Thanks,

2

Use explicit transactions. The JDBC guide has info on doing that.

3

Hi Rob,
Thanks for your help. I used the explicit transactions, but you can not put multiple insert/update services under one local transaction. The pseudo-code below is invalid, but I want to know is there any way I can implement this function? I want to commit after all insert or update services completed sucessfully and rollback all transactions if anyone of them fails.

start transaction
sequence (exit on sucess)
sequence (exit on failure)
–insert service 1
–insert service 2
–update service 1
–commit
sequence (exit on done)
–rollback

Regards,
Mike

4

Have you run into an error when doing this? My understanding is that as long as all the inserts/updates/deletes are for the same connection, it will work. It’s when multiple connections, which are configured with LOCAL_TRANSACTION, are used that it won’t work. In that case, only one of the connections can be LOCAL_TRANSACTION and the others need to be XA.

5

Hi Rob,
Thanks. It does work when using one connection with local transaction type, but I found a minor trick. Please see the following code.

Scenario #1 - WORK:
start transaction
sequence (exit on sucess)
sequence (exit on failure)
–insert service 1
–insert service 2
–update service 1
–commit
sequence (exit on done)
–rollback

Scenario #2 - NOT Work:
start transaction
sequence (exit on sucess)
sequence (exit on failure)
–insert service
–call a flow service which includes a select service using same connection with local transaction type
–update service
–commit
sequence (exit on done)
–rollback

Anyway, it solves my problem.

Thanks again,

6

You should be able to make the second scenario work as well. The key is to make sure the transaction ID is available to the flow service. If the ID isn’t available, it will try to start another tx, which you’ve seen causes trouble.

7

You are right. That’s the way to solve the issue in the Scenario #2.
Appreciate your help.