[SCC.0121.0034E] commit failed: more than 1 local trans enlisted

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.

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

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.