Hi experts,
Thanks for reading this thread. We have a problem with embedded database transactions:
We want to run a number of statements in a LOCAL_TRANSACTION, with a startTransaction at the very beginnning of the top-level service and a commitTransaction at the end. Pretty regular stuff, though worthwhile noting that the same process will be triggered in parallel a number of times, with different values obviously.
Then, we may need to initialize some common database values in this transaction, but to be thread-safe yet still performant, we want to do this in another database transaction. This way, all the other threads are not waiting for the commit of the first thread. We decided to use a NO_TRANSACTION connection for this initialization.
The complete processing goes a bit like this:
-
- startTransaction LOCAL_TRANSACTION
-
- run adapter service LOCAL_TRANSACTION
- – run adapter service NO_TRANSACTION (for initialization)
-
- run adapter service LOCAL_TRANSACTION
-
- commitTransaction LOCAL_TRANSACTION
The problem is we are now not getting any response returned anymore after step 3. The first adapter which needs to run, after the NO_TRANSACTION connection was used, never seems to finish invocation. The thread is not liberated and eventually we get a timeout.
We tried configuring both transaction differently, playing with NO_TRANSACTION, LOCAL_TRANSACTION and XA_TRANSACTION. When we use XA_TRANSACTIOn for both the connections, we get the error: “ORA-02049: timeout: distributed transaction waiting for lock”.
Anybody got good ideas as to what we are doing wrong? All suggestions appreciated!