JDBC error Unable to commit transaction

Hi,

While using a standard (LOCAL_TRANSACTION) config thin driver to an Oracle DB, I get the following error (during business process monitoring):

Error Log:

com.wm.pkg.art.error.DetailedServiceException: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to commit transaction. Transaction state:Transaction is rolled back.

Server Log:
commit failed: more than 1 local trans enlisted. xid = [FormatId=45744, GlobalId=…/1087244965517, BranchQual=1] rxid = {2}
rollback-only flag is set. rolling back transaction, xid = …/1087244965517
Adapter Runtime: Error Logged. (^^^ See error above ^^^)

The strange thing is that my process SAYS it is completing successfully but in reality is DOES NOT.

Has anybody seen such an error? Does anybody have any ideas?

Many thanks,

Chris

Chris - Have you used explicitly made your flow transactional? Unless you do this, any JDBC Adapter service is implicitly commited or rolled back as soon as the service completes excution.

To make your flow service explicitly transactional, you must begin the flow with WmART.pub.art.transaction:startTransaction. Then in the try block, you need a commitTransaction step and in the catch block, you’ll need a rollbackTransaction step. This is described in depth in the JDBC Adapter Guide page 146.

Hope that helps,
Rajesh

Hi Rajesh,

Thanks for the feedback. The problem is that I do not want explicitly defined transactions. Mainly because of the debugging issues (separate threads of execution while debugging using the developer). So what I simply want is implicit transactions which do commit as you indicated “when the service completes execution”. Eg. Tbe identical behavior that we normally see with a service committing only once the services completes it execution successfully.

The problem is that I do not get any errors! When I run the service that performs the inserts into the DB no errors are generated. Moreover when I resubmit using the modeler step that calls my service, it also works 100% no errors. What I can’t seem to understand is why when going through my modeled process does the transaction at all steps complete successfully but all of a sudden does not want to commit my inserts…

Any ideas?

Regards,

Chris

Hello,

A little more on this issue. By using explicit commits in our process, the insertions in the tables are performed correctly. However the Adapter PRT (Process Run-Time) is the one that is generating the “unable to commit error”.

Regards,

Chris

Do you have optimize locally checked on the Model. The PRT seems to behave incorrectly with implicit transactions when this is checked. I beleive it is the default selection. You don’t need to have explicitly defined transaction, just uncheck this option.

This problem is for v6.1. What version are you running?

Hello,

Well the version is v6.1 build 132. Your answer may be valid but I am not experiencing this problem in all our environments… This is another frustration because in one environment it is fine no errors and in the other this PRT error.

The process in both environments is identical. However one of the environment has been configured for clustering.

I will however give this a try. Seems like a “reasonable” work around (Small performance hit but error handling + no PRT error).

Thanks,

Chris

I encountered this error when service-1 called service-2, both of which contained calls to jdbc adapter services. Apparently service-1 initiated a transaction then called service-2 which also initiated a transaction. Service-2 committed its transaction and returned to service-1 which tried to commit the transaction again.

I don’t know if you have solved this problem or not, but I’ve been in contact with webMethods support regarding a similar issue. We have a problem where we are receiving this error after about 24 hours of normal error-free operation:
Adapter Runtime: Error Logged. See
Error log for details. Error: [ART.117.4036] Adapter Runtime (Adapter
Service): Unable to commit transaction. Transaction state:Transaction is
active

If we restart the IS, the problem goes away for another ~24 hours. wM support has indicated that no more than one local_transaction JDBC connection can be invoked anywhere downstream of a service that is called by TN. This is not only true for JDBC connections, but for all transactional connections using the ART.

You may want to be sure that your services are not accessing more than one local_transaction JDBC connection.

A workaround suggested by wM support is to wrap your second service/connection in an explicit transaction, or use a locally-published document to invoke the second service/connection.

Not sure if this is relevant for you, but…

…/c

Chris,

Nice update,Thanks for posting.

We also seen the same traces few times in the logs,and this work around helps for everyone.

Regards,

Hi,

I was getting a similar kind of error as discussed in this posting but then I was getting this error when working with MQ Adapter. I wish to implement the implicit transaction but couldnt figure out of what changes need to be done to make sure that there is no more than one local_trasaction? We are getting this error every alternate time.

Could someone please provide the better ways? I am stuck at this point and have spend lots of time figuring different ways.

Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4036] Adapter Runtime (Adapter Service): Unable to commit transaction. Transaction state:Transaction is active

Thanks.

Hi guest
The thing is for a local transaction , webmethods will no do the transaction management and you need to explicitly use the WMART package services to do the same. Make your transaction as NO_TRANSACTION and webmethods will take care of the transaction managemant. One more thing is while using hte LOCAL_TRANSCATIOn and using the pub.art.startTransation and pub.art.commitTransation/RoolbackTransaction you should not trace the steps in the developer it throws out an exception like the one you mentioned above. For a single local transaction again there is no need of transaction management.

Regards,
Pradeep.