Working with LOCAL_TRANSACTION in adapter services

[Thahir_KK]

IS version 6.5.

Would it be advisable to use a JDBC adapter defined as LOCAL_TRANSACTION as transaction type to be used to automatically commit transactions.

I need most of my transactions to the database need to be commited immediately. But I have a scenario where I need to explicitly commit or rollback.

Let me put it in detail.

>>> pub.art.transaction:startTransaction
>>>>>> adapterA.service1
>>>>>> adpaterB.service2
>>>>>> adapterA.service3
>>> pub.art.transaction:commitTransaction
On error case
>>> pub.art.transaction:rollbackTransaction

How would this work if ‘adpaterA’ is of transaction type ‘NO_TRANSACTION’ and ‘adapterB’ is configured for ‘LOCAL_TRANSACTION’.

Would it be advisable to convert adapterA to be of ‘LOCAL_TRANSACTION’ type without handling the ‘pub.art.transaction:startTransaction’ and ‘pub.art.transaction:commitTransaction’ part.

I am having 100’s of adapter services with ‘NO_TRANSACTION’, I do not want the pain to put a transaction around them.

I await your response to proceed.

Thanks and regards

Thahir

[mt_wmc]

Thahir

for auto-commit use LOCAL_TRANSACTION

for manual-commit/rollback use XA_TRANSACTION

MT

[wMusers.Com1 (wMusers.Com)]

MT, you’ll discover, when you face a situation similar to Thahir’s, that your answer is insufficient. Thanks for wanting to help out though.

Thahir, I don’t have a quick answer other than you should prototype this behavior and test it thoroughly before moving ahead with a large change.

Maybe someone else can chime in who is more well-versed in the black magic that is IS ART transaction managemnet.

Mark

[reamon]

I assume you’ve reviewed the “webMethods JDBC Adapter User’s Guide”, particularly Appendix B, which talks about the transaction types and implicit/explicit transactions. If you’re like me, you found yourself scratching your head wondering “if I use explicit transactions with a connection that is set for NO_TRANSACTION, what happens?”

The release notes state:

The JDBC Adapter supports Automatic (NO_TRANSACTION), Controlled (LOCAL_TRANSACTION), and 2-phased (XA_TRANSACTION) transactions. In Automatic transaction mode, the adapter handles the transaction boundaries. In Controlled transaction mode, users have control over the transactions. Using XA JDBC drivers, the adapter can support 2-phase transactions executed across multiple databases. The adapter works with the webMethods Integration Server to act as the transaction manager for 2-phase transactions.

This implies that the adapter manages the transaction context of connections set to NO_TRANSACTION, regardless of if you call startTransaction or not–but it doesn’t come right out and say that. Urgg.

One thing that is clear, though, is that only one LOCAL_TRANSACTION connection can be used within a transaction context. So in your example with adapterA and adapterB, if they are both LOCAL_TRANSACTION connections it won’t work. One or both of them will need to be XA_TRANSACTION. I’ve no idea what will occur with NO_TRANSACTION though I suspect that it won’t work either as all the connections will try to use the same transaction context and without the connections being set to XA, it will fail.

As Mark points out, you should set it up and test scenarios.

You mentioned you have “100s of adapter services with ‘NO_TRANSACTION’”. I assume you meant 100s of adapter connections. But if you really meant 100s of services and you have only a few connections (relatively speaking) then changing the transaction type of the connections might not be that much of an effort?

[system]

@reamon

I assume you’ve reviewed the “webMethods JDBC Adapter User’s Guide”, particularly Appendix B, which talks about the transaction types and implicit/explicit transactions. If you’re like me, you found yourself scratching your head wondering “if I use explicit transactions with a connection that is set for NO_TRANSACTION, what happens?”

The release notes state:

The JDBC Adapter supports Automatic (NO_TRANSACTION), Controlled (LOCAL_TRANSACTION), and 2-phased (XA_TRANSACTION) transactions. In Automatic transaction mode, the adapter handles the transaction boundaries. In Controlled transaction mode, users have control over the transactions. Using XA JDBC drivers, the adapter can support 2-phase transactions executed across multiple databases. The adapter works with the webMethods Integration Server to act as the transaction manager for 2-phase transactions.

This implies that the adapter manages the transaction context of connections set to NO_TRANSACTION, regardless of if you call startTransaction or not–but it doesn’t come right out and say that. Urgg.

One thing that is clear, though, is that only one LOCAL_TRANSACTION connection can be used within a transaction context. So in your example with adapterA and adapterB, if they are both LOCAL_TRANSACTION connections it won’t work. One or both of them will need to be XA_TRANSACTION. I’ve no idea what will occur with NO_TRANSACTION though I suspect that it won’t work either as all the connections will try to use the same transaction context and without the connections being set to XA, it will fail.

As Mark points out, you should set it up and test scenarios.

You mentioned you have “100s of adapter services with ‘NO_TRANSACTION’”. I assume you meant 100s of adapter connections. But if you really meant 100s of services and you have only a few connections (relatively speaking) then changing the transaction type of the connections might not be that much of an effort?

Sorry for the very late reply after years.

I tested the scenario.

Created a insert adapter service A with no transaction of conn type A.
Created an insert adapter service B with local transaction of conn type B.

service logic was to insert the data to service A and service B. The service worked well without any transaction error.

I feel the explicit transaction is needed only when two different local transactions are used.

Please correct me if i am wrong.

Thanks,
Sathya