Hi,

I got error:
[SCC.0121.0034E] commit failed: more than 1 local trans enlisted. xid = [FormatId=45744, GlobalId=CL01SMSGW/1323052155294, BranchQual=1] rxid = {2}
when commiting transaction.

The flow code is like this:

startTransaction
SEQUENCE
SEQUENCE (TRY)
Invoke: insert1_adapterService (point to jdbcAdapter1 with LOCAL_TRANSACTION type)
Invoke: insert2_adapterService (point to jdbcAdapter2 with LOCAL_TRANSACTION type)
commitTransaction
SEQUENCE (CATCH)
rollbackTransaction

If I removed the insert2_adapterService, it run fine.

I also already make sure that startTransactionOutput/transactionName is linked to commitTransactionInput/transactionName as well as the rollbackTransactionInput/transactionName.

Is this because we cannot commit more than one LOCAL Transaction ?

yes this is the normal behavior.

If you want to achieve what you’ve described, you can use an XA_TRANSACTION. But it depends on what you really want/need.

You also can check the webMethods MethodsJDBC Adapter User’s Guide, and particularly the “Built-In Transaction Management Services” section; there are samples.

Yes, I tried with XA_TRANSACTION and it succeeded.

But I read somewhere in this forum that said XA_TRANSACTION is not recommended because of the complexity. What does that mean?

What’s the pros and cons between XA Transaction and Local Transaction?

I tried successfully with a nested startTransaction-commitTransaction/rollbackTransaction solution for LOCAL_TRANSACTION, but it makes the code become complex, especially if there is more than two transaction.

Example solution code with three local transaction calls:

MAP -> set errorFlag = 0
startTransaction1
[B]SEQUENCE (TRY & CATCH 1)[/b]
[B]SEQUENCE (TRY 1)[/b]
Invoke: local_trx_insertAdapterService1
startTransaction2
[B][COLOR=Navy]SEQUENCE (TRY & CATCH 2)[/color][/b]
[B][COLOR=Navy]SEQUENCE (TRY 2)[/color][/b]
Invoke: local_trx_insertAdapterService2
startTransaction3
[B][COLOR=Red]SEQUENCE (TRY & CATCH 3)[/color][/b]
[B][COLOR=Red]SEQUENCE (TRY 3)[/color][/b]
Invoke: local_trx_insertAdapterService3
[B][COLOR=Red] SEQUENCE (CATCH 3)[/color][/b]
rollbackTransaction3
MAP -> set errorFlag = 1
[COLOR=Navy][B]SEQUENCE (CATCH 2)[/b][/color]
rollbackTransaction2
MAP -> set errorFlag = 1

BRANCH (/errorFlag)
1: throwException
commitTransaction3
commitTransaction2
commitTransaction1
[B]SEQUENCE (CATCH 1)[/b]
rollbackTransaction1
EXIT from $flow and signal Failure

Note: throwException is simply a java service that throws a ServiceException.

[URL]http://www.narendranaidu.com/2006/01/what-are-xa-transactions-what-is-xa.html[/URL]

Updating multiple resources within a single transaction is the root of the complexity.

If you’re updating 3 resources (and that cannot be factored out of the integration in some way) then using XA transaction will simplify your flow code (call startTransaction just once, do all 3 updates, then commit/rollback–XA will make sure all or none succeed). While this simplifies the flow, it is relatively complex behind the scenes and troubleshooting can be troublesome.

If you find you’ve created a nasty nested flow service like the example above, break the nested try/catches into their own services. This can help readability tremendously.