I have a source A (Oracle Database) with JDBC connection X (Type : LOCAL_TRANSACTION) and target B (Oracle Database) with JDBC connection Y (Type : LOCAL_TRANSACTION). When trying to insert data in target B with connection Y and also I need to have connection X active.
Because of this scenario I was getting transaction management issues (more than 1 local transaction enlisted). So I created a dblink between Source A and Target B and using that dblink I’m inserting data in the target. I created synonyms for the table of the target B and now using connection X to insert the data into the target B using dblink.
What is the nature of the dependency of needing a connection to A whilst inserting data into B? You’ve described your solution. You described that you “need” a connection to A. But you haven’t described the basic scenario you’re trying to address with the implementation nor why you “need” a connection to A when inserting to B.
The need of connection to source A is to report back to the source about the status of the transaction. For example: The insert in the target B is successful or failure. That is the reason I need the connection to Source A open.
At any given point of time I need to have two sessions open to two different databases. Since I’m updating the databases I need to have transaction type as LOCAL_TRASACTION for the two connections.
So you’re inserting to B, and within the same transaction want to update A. If the insert to B fails, you won’t update A. If update of A fails, you’ll rollback the insert of B. Is that correct?
If so, then use of XA_TRANSACTION for one or both of the connections would do the trick.
Or, is it the case that you want to update A, independently of the activity on B, to record success or failure? Are you concerned about the insert to B being successful but the update to A failing? If so, what are the ramifications?
I’m guessing you tried to call an insert service and then an update service and you encountered the “more than 1 local transaction” error. This is due to the implicit transaction management provided by ART and the adapter.
Use an explicit transaction to insert to B, then another explicit transaction to update A. Refer to the JDBC Adapter User’s Guide for handling explicit transactions.
Even if the insert to B fails I want to report back to A with status as “failed” and would like to the rollback the whole transaction.
I’m using Explicit Transaction management.
I have source A and target B, 3 services (service 1, sevice 2 and service 3) and Connection X to query source A database , Connection Y to query target B database. Both the databases are oracle.
Following is the structure of the flow service. I was getting the trx management error with this structure.
Service 1 startTransaction-1
—try
-----insertDBCall a (connection X - LOCAL_TRANSACTION))
-----call service 2
---------insertDBCall b (connection X - LOCAL_TRANSACTION))
---------updateDBCall c (connection X - LOCAL_TRANSACTION))
---------call service 3
--------------startTransaction-2
-----------------try
---------------------insertDBCall d (connection Y - LOCAL_TRANSACTION))
---------------------insertDBCall e (connection Y - LOCAL_TRANSACTION)) ---------------------updateDBCall f (connection X - LOCAL_TRANSACTION))
---------------------commitTransaction-2 -----------------catch
---------------------rollbackTransaction-2
-----updateDBCall g (connection X - LOCAL_TRANSACTION))
-----commitTransaction-1
—catch
-----rollbackTransaction-1
Now I created DBlink in source A and used the connection X to insert the data in target B. The service 3 doesn’t need any explicit transaction management. Now the structure looks like :
Service 1 startTransaction-1
—try
-----insertDBCall a (connection X - LOCAL_TRANSACTION))
-----call service 2
---------insertDBCall b (connection X - LOCAL_TRANSACTION))
---------updateDBCall c (connection X - LOCAL_TRANSACTION))
---------call service 3 -----------------try
---------------------insertDBCall d (connection X - LOCAL_TRANSACTION))
---------------------insertDBCall e (connection X - LOCAL_TRANSACTION)) ---------------------updateDBCall f (connection X - LOCAL_TRANSACTION)) -----------------catch
-----updateDBCall g (connection X - LOCAL_TRANSACTION))
-----commitTransaction-1 —catch
-----rollbackTransaction-1
You’d need to put the operations that are against different DB instances within different explicit transactions (try/catches ommitted for clarity):
--------------startTransaction-2
-----------------try
---------------------insertDBCall d (connection Y - LOCAL_TRANSACTION))
---------------------insertDBCall e (connection Y - LOCAL_TRANSACTION)) ---------------------startTransaction-3 -----------------------updateDBCall f (connection X - LOCAL_TRANSACTION)) ---------------------commitTransaction-3 ---------------------commitTransaction-2 -----------------catch
---------------------rollbackTransaction-2
Operations to different DB instances cannot be in the same transaction, even explicit, unless only one of the connections is LOCAL_TRANSACTION.
You might also unnest the “updateDBCall f” operation and do it outside of the other transactions. Set a flag to know what value to use to record success/failure.
I have tried the solution you have mentioned. Infact that was my first try. Even if I unnest the updateDBCall f from the service 3 I still get that error (more than 1 local transaction enlisted) since I have two local transactions opened (connection X and connection Y). When I’m using connection Y connection X is not closed. The flow is shown below.
Service 1 startTransaction-1
—try
-----insertDBCall a (connection X - LOCAL_TRANSACTION))
-----call service 2
---------insertDBCall b (connection X - LOCAL_TRANSACTION))
---------updateDBCall c (connection X - LOCAL_TRANSACTION))
---------call service 3
--------------startTransaction-2
-----------------try
---------------------insertDBCall d (connection Y - LOCAL_TRANSACTION))
---------------------insertDBCall e (connection Y - LOCAL_TRANSACTION)) ---------------------commitTransaction-2 -----------------catch
---------------------rollbackTransaction-2
-----updateDBCall g (connection X - LOCAL_TRANSACTION))
-----commitTransaction-1
—catch
-----rollbackTransaction-1
With this excercise I found out that I should have only one open session to a database at any given point of time. If I need to open session to a different database i need to close the previous one which was opened. But this is not suitable with the architechture I’m following since I need to rollback all the changes starting from the service1 if anything fails in service 3.
The construct you show seems to me like it should work. Nested explicit transactions are supposed to allow this sort of behavior. My only guess is that explicit transactions across service boundaries don’t behave as we might expect. Perhaps a chat with wM tech support is in order.