I encountered a strange error that doesn’t seem to be found in any forums.

2018-01-16 04:51:53.0 [ART.114.303] Adapter Runtime (Transaction): Unable to commit transaction. [ART.117.4013] Adapter Runtime (Adapter Service): Internal Error: Unable to nest connection states.

My flow is as follows:

SEQUENCE (exit on SUCCESS)
-SEQUENCE (exit on FAILURE)
–pub.art.transaction:startTransaction
–(business logic here with insert & update adapter services using a LOCAL_TXN connection)
–pub.art.transaction:commitTransaction
–logging using an existing framework service using NO_TXN connection
-SEQUENCE (exit on DONE)
–getLastError
–pub.art.transaction:rollbackTransaction
–logging using an existing framework service using NO_TXN connection

This was tested successfully in test environment with concurrent transations with no issues, but in production environment, I encountered the error above. As for why I’m mixing NO_TXN and LOCAL_TXN services in a service, the logging service using NO_TXN are part of a framework that depend on, so this flow was written to group all the logging messages AFTER the commit was done. Anyone have any idea why this error is happening? Thanks in advance.

I must admit, I haven’t seen that particular error myself. I can try to replicate it but before I do, let me ask this: do you have the ability or permission to change the logging framework itself?

I ask because one thing that you would benefit from tremendously is making the framework asynchronous via messaging. By that I mean, instead of calling a framework service that performs a database insert, your framework service should publish a message to your messaging system (e.g. UM). A subscriber would then be responsible for picking up the message and writing it to the database. This approach has a number of advantages:

(1) It eliminates the issue you’re dealing with
(2) It speeds up your main services because, typically, publishes are faster than DB inserts
(3) It safeguards you against issues when the database is down (i.e. instead of losing your log message, your trigger can automatically retry it until the database is back up)
(4) It opens the door for you to introduce other subscribers (e.g. in addition to logging to the database, perhaps you want to send an e-mail if the attribute ‘level’ of the message is set to ‘error’, etc).

So, if you have the ability to make those changes, I would encourage you. If you don’t, then one thing you can try is to get rid of the startTransaction, commitTransaction, and rollbackTransaction calls. If I recall correctly, the IS will handle transactions properly when dealing with a LOCAL_TRANSACTION and a NO_TRANSACTION connection within the same context. I haven’t tried this personally in a while because, like I said, I typically try to decouple scenarios like this via async messaging.

Percio

Hi,

just some more words about this:

You should place the startTransaction outside of the “ON SUCCESS” sequence as otherwise the transaction id will not be visible to rollbackTransaction in the case there is an exception in the “ON FAILURE” sequence.

Is there only one conenction with local transaction involved in your logic or are there multiple of them?
In the second case you change them to XA transaction type.

Please have a look at the JDBC Adapter Users Guide

Regards,
Holger

I will try the suggestions mentioned and report back.

As for the below:

You should place the startTransaction outside of the “ON SUCCESS” sequence as otherwise the transaction id will not be visible to rollbackTransaction in the case there is an exception in the “ON FAILURE” sequence.

Reply: I map the transaction id inside the lastError/pipeline, so that should address this.

Is there only one conenction with local transaction involved in your logic or are there multiple of them?
In the second case you change them to XA transaction type.

Reply: I used only one local transaction connection in the logic.

Hi,

You should place the startTransaction outside of the “ON SUCCESS” sequence as otherwise the transaction id will not be visible to rollbackTransaction in the case there is an exception in the “ON FAILURE” sequence.

Reply: I map the transaction id inside the lastError/pipeline, so that should address this.

No, as the variable is still empty when entering the “ON DONE” sequence.
With your current code outline the transaction id is only filled inside the “ON SUCCESS” sequence even if Designer is pretending something else.

Please check the JDBC Adapter Install and Users Guide, Appendix C for further informations about Transaction Handling.

Regards,
Holger

Holger,

I do like your approach better, in fact, in the rare occasions where I’ve had to use startTransaction, I also kept mine outside the try block. However, what yeeck is suggesting also works. He is simply saying that in the catch block, instead of grabbing the transactionName from the pipeline itself, he’s grabbing it from the pipeline document inside of lastError.

Percio

Hi Percio,

there is a clear note in the JDBC Adapter Install and Users Guide to place the startTransaction invocation outside of the sequence.

@Yeeck:
Which wM version are you running on and what is your JDBC Adapter version?

Regards,
Holger

Hi,

are you overwring connection parameters in adapter service invocations with different user/password information?
This is not possible as inside of one transaction all services have to use the same connection paramters.

This is also stated in the Users Guide.

Regards,
Holger

Thanks for pointing this out. Although I was familiar with, and personally adhere to, that practice, I was unaware of that note in the guide. I wonder whether they put that note there because it makes it easier for people not to make the mistake of mapping a null into transactionName when doing a rollback or if there’s a functional reason to put the startTransaction outside the SEQUENCE. I imagine it’s likely the former given that I’ve seen other people successfully take the approach yeeck has taken, and I’ve also seen people take the approach of keeping the startTransaction inside the try block while hard-coding the transaction name in the start, commit, and rollback steps.

Either way, your point is well taken. There’s no reason to deviate from the documented approach given that it’s indeed the cleanest.

Percio

This might be it, but I’m curious why it didn’t fail in Testing environment as opposed to Production environment earlier. To be safe I I have mapped out the $connectionName used by the logging framework to another variable while sticking to my own $connectionName used by my adapter services. I’ve also removed the start,commit,rollback steps while maintaining the local connection adapters services as suggested by Percio. So far testing seems fine. Let’s see how it goes.

To make sure we’re on the same page: removing the start, commit, and rollback will cause your local_transaction steps to be part of an implicit transaction. I’m a big fan of implicit transactions (except in a BPM context - I can explain if needed) because they keep things simple. Now, this means that in order to rollback a transaction, your service needs to throw an exception. In your catch block, it doesn’t look like you’re currently doing that so you’ll need to add an ‘EXIT and signal failure’ step or something similar. You’ll also want to make sure that your logging service handles its own exceptions because if it does not, then a failure in that service could accidentally cause a rollback of your main transaction. This is another reason why I like to make my logging frameworks asynchronous.

Hope this helps,
Percio