Local Transaction and NO Transaction

, ,
1

Hi Team,

I wanted to understand if the local transaction can be used as NO Transaction and vice versa.

If i have created Local Txn and i am not using rollback transaction service , will this work as No transaction.

Also, if I have created NO txn, and i use rollback transaction service. Will this work?

Thoughts please.

TIA.

2

Keep it simple,

SELECT - Use No Tx

INSERT, UPDATE, DELETE, MERGE - Use Local Tx so that you have the leverage to either commit/rollback the transaction set.

Refer JDBC guide for more details.

If you have any implications in your code, do let us know.

3 Likes
3

Hi Mahesh,

Thanks for your reply.

I wanted to know what if i will use No txn but still giving the rollback for some insert operations.

Can this be done?

Thanks

4

Hi,

Rollback scenario only work if you have the JDBC connection configured as LOCAL_TRANSACATION and also you can set/define the explicitly set/commit/rollback art Transaction services in your flow try/catch blocks.

HTH
RMG

5

Hi RMG and others,

I have an issue that I am facing currently. I am connecting to a HANA database which is LOCAL_TRANSACTION. we have setup this as LOCAL_TRANSACTION since it supports “batchInsert”.

Now, when we are trying to process the data. we have 4 tables.

  1. we insert some basic info ( insert statement )
  2. we insert into process control table ( insert statement )
  3. BatchInsert actual data into main table.
  4. Update the same main table with 2 other colums. ( update statement )
  5. Update the process control table to success.

Now, we are getting errors like " Error occurred while closing connections" (or) Unable to connect to resource…The resource is already being used in the parent transaction.

I am using explicity start, commit and rollback transactions for all the above tables. is there any missing. I am new to these database transactions. Any help is much appreciated.

Thank you.

6

Share your code/flow screenshot, this error happends if the transaction boundary is not correctly handled. Refer http://techcommunity.softwareag.com/ecosystem/documentation/webmethods/adapters_estandards/Adapters/JDBC/JDBC_9-10/9-10_Adapter_for_JDBC_Install_and_Users_Guide.pdf

Read chap. 13 “Built-In Transaction Management Services”

7

Hi Kumar,

Yes I too believe that some issue in the code where the parent/sub-services being handled by the transactional logic with start/commit/rollback being called… Please evaluate and check it more closely as some times this kind issues will resolve by troubleshooting exercise.

HTH,
RMG

8

some where in the flow u have adapter using Local connection which implicitly creates transaction and now u r trying to create a txn on same connection. So you got the failure
Implicit txn boundary can be flow or completion of thread also.

9

Please check the value for the Statless Property for all services, I guess all should be set as “False” to use the transaction feature