Explicit transaction and NO_TRANSACTION connection

, ,
1

Hi everybody,

I’ve just a small question…
Let’s say that “selectAdapter1” is an JDBC Adapter on a NO_TRANSACTION connection.

Is it possible to do something like that in a service :

  • INVOKE selectAdapter1
  • START TRANSACTION
  • TRY
    – INVOKE selectAdapter1
    – Commit
  • CATCH
    – RollbackTransaction

Obviously this service has no logical sense, but it fails…
I’ve the basic error “Unable to connect to resource xxx. The resource is already being used in a parent transaction” on this NO_TRANSACTION connection.

Do you think it’s normal that it fails like that ?

Thks.

2

To use transaction you need to use the other two types of connections.
There is no point of starting a transaction on a NO_TrANSACTION connection. It’s all auto commited.

3

I know there is no need of explicit transaction, I’m allo using Local_transactio inside the TRY sequence.

I’m just asking if it’s normal that it fails when I’m using No_transaction outside the explicit transaction AND outside at the same time…

4

Yes for LOCAL_TRANSACTION you can try explicity start/commit/rollback a transaction in the error handling scenarios and that is the way to go implementing:

What is your question for NO_TRANSACTION did not quite get you?

HTH,
RMG

5

Please read the wM JDBC adapter guide from SAG :slight_smile: they have expalined with examples.

Mainly the last chaps of the PDF.

6

Let me reformulate :slight_smile:

I’ve something like this: (in fact it’s a more complex service, but I’ve reduced it to the minimum in order to reproduce my case)

  • INVOKE jdbcAdapter1 //NO_TRANSACTION
  • StartTransaction
  • TRY
    – INVOKE jdbcAdapter2 //LOCAL_TRANSACTION
    – INVOKE jdbcAdapter1 //NO_TRANSACTION same as the first one
    – CommitTransation
  • CATCH
    – RollbackTransaction

Then I’ve the error that my NO_TRANSACTION connection is used in a parent transaction.
(This service has no parent service)

I attached a simple screenshot.
I also checked the “wM JDBC adapter guide” but I’ve found nothing relevant about that.

I just want to know if it’s a normal behavior that the NO_TRANSACTION connection fails.

2014-03-06_110301.jpg

7

You can try with “startTransaction” as first step in the flow. I hope it will works…

8

“You can try with “startTransaction” as very first step in the flow”

Yes this can do the trick:

HTH,
RMG

9

Arnaud,
The problem is, you had first adapter service without transaction context being defined. When the flow service executes, it would encounter the adapter service call, and as there is no explicit transaction defined, it will start one implicit transaction context, and this will complete during the service completion. However, in your flow, you have another piece of code that follows transaction. Since IS has already locked this service for a particular transaction context, it is not able to add one more for the same before it completes.

You can have several ‘start’, ‘commit’ one after the other, which will work fine.
You can have start/commit context, inside another start/commit (trans inside trans).

However, you cannot mix implicit and explicit transaction in the same service. That’s the reason, by adding startTransaction as first step, you will avoid this issue. In this case, commitTrans can be either present in end of the actual service, or soon after the first adapter invocation. Both will work. Pass the right transaction id to the catch block to check roll-back scenarios.

HTH
Senthil

10

Very well said senthil…

Basically the code is written in such a way that ART Trasaction Manager is confused between implicit and explicit transactions started