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:

1. • startTransaction LOCAL_TRANSACTION
2. • run adapter service LOCAL_TRANSACTION
3. – run adapter service NO_TRANSACTION (for initialization)
4. • run adapter service LOCAL_TRANSACTION
5. • 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!

Just a guess: the start transaction leave some variables in the pipeline that will be used by the later service & commitTransaction. but when you run No_Transaction service, it’s overwritten.
try to save pipeline before and after No_Transaction step, see what’s modified.
Let us know if you get anything further.
Tong

I think we cannot have NO_TRANS between LOCAL_TRANS.

Don’t mix different transaction types within a transaction boundary…
In that example, I can’t see why you don’t execute that NO_TRANSACTION call before the LOCAL_TRANSACTION calls if all that’s doing is some initialization.

Hi all

Thanks for the input, I think that you are correct: the usage of NO_TRANSACTION embedded within a LOCAL_TRANSACTION does not work. We tried this because using 2 LOCAL_TRANSACTION did not work either.

Anyhow, as Phil points out: this is init, so we just placed it at the very beginning of the processing chain. This costs in performance, though hopefully not too much.

Thanks to all for the input in any case!

Loic