I think XA transaction type needs to be used if we need to do a transaction management across different database (more than one database). Is my understanding correct?

We have a scenario and need your assistance to choose the correct transaction type

All adapter services are configured to use one JDBC connection and a single application database. We have built wrapper flow services to start transaction, commit transaction, rollback transaction (kind of utility services)
Main service calls utility start transaction service to start transactions
Main service again calls utility commit transaction to commit transaction
Main service again calls utility rollback transactions to rollback transaction

If i set Local transaction, transactions are not working properly, meaning not doing the rollback properly.
If i set XA, it is working perfectly

I do not understand why we need to set XA even we have single database transaction.

Is it mandatory to have direct wm start, commit, rollback steps in main service instead of using wrapper utility service. Will there be any boundary issue in this scenario?

I am not convinced with setting XA as during the load in production, I have noticed the below error related to Distributed locking stuffs and i guess it is because transaction type is XA, even for single database transactions

ORA-02049: timeout: distributed transaction waiting for lock " ORA-02049: timeout: distributed transaction waiting for lock

XA is for transactions involving more than one DB connection.

If you’re using different connection pools, even if to the same DB, then you need to use XA.

What are your start, commit, and rollback utilities doing?

We have only one db connection
Utility is just to execute transaction steps based on flag true or false

Mr.Nagaraj Ramanan,
Please mention what actually you are doing it in the service, is it bulk insert or bulk update? or simple DML statements?

Thanks
Saravanan S

simple insert, update statements

Try to execute those statements directly using toad or sqlplus & see how much time it takes. If it takes more time then we will get this error (In this case we need to do some config change in oracle).
It could be very simple update, but that particular record may be locked by some other thread or some other program then also we will get this error (May be for this case you can execute the jdbc step inside the repeat step).

Thanks
Saravanan S

Are you concern about transaction management?..If Not, you can prefer NO_TRANSACTION, which work absolutely fine…If Yes, LOCAL_TRANSACTION should be fine by using built in trasnaction management service…

Is there any particular reason to have a seperate utilities rather than using built in transaction management services?