When should we use explict transaction?

, ,
1

Hi,

Am using built in Update and Insert adapter service, both uses the connection1 [which configured with LOCAL_TRANSACTION].

Sequence(Try/Catch)

  • Sequence(Try)
    • Update service
    • Insert service
  • Sequence(Catch)
    • getLasterror

When Insert service gets failed, Update operation was not getting rolled back automatically.

Note*: If I use explicit transaction its working fine.

But when verified the JDBC adapters guide documents found the below points:

  1. The Integration Server can automatically manage both kinds of transactions[LOCAL_TRANSACTION & XA_TRANSACTION], without requiring the adapter user to do anything.
  2. If your flow contains adapter services that use more then one LOCAL_TRANSACTION connection, you must use explicit transactions. [Here more than one connection means, different type of connection right? example connection1 and connection2 both have the transaction type ‘LOCAL_TRANSACTION’]

Whereas in my service am using only one connection (i.e. connection1 only for both Update and Insert service) why the transaction not getting rolled back automatically?

Please correct my understanding…

Regards,
Sam

2

IME, I always use explicit transactions when modifying table data in any way. Implicit transactions have usually come back to bite me.

3

Hi Rob,

Yes its better to use explicit transaction.

But verified the JDBC adapters guide documents found the below points:

  1. The Integration Server can automatically manage both kinds of transactions[LOCAL_TRANSACTION & XA_TRANSACTION], without requiring the adapter user to do anything.
  2. If your flow contains adapter services that use more then one LOCAL_TRANSACTION connection, you must use explicit transactions. [Here more than one connection means, different type of connection right? example connection1 and connection2 both have the transaction type ‘LOCAL_TRANSACTION’]

Whereas in my service am using only one connection (i.e. connection1 only for both Update and Insert service) why the transaction not getting rolled back automatically[used implicit]?

Regards,
Sam

4

I don’t know why it doesn’t work. It’s one of the reasons I always use explicit transactions.