JCA Binding execute of Reference operation 'merge' failed due to: DBWriteInteractionSpec Execute Failed Exception

Another problem I faced today while using the DbAdapter for merging rows from another web service (as per my previous blog) was that commits failed. This turned out to be because Global Transactions were enabled on the Data Source that was referenced to by the DbAdapter configuration (see below).

Global Transactions can be disabled on the data source by going to: Home -> Data Sources -> Data source name -> Transactions -> Un-tick: Supports Global Transactions and then Save the data source.

 [2013-01-23T18:57:09.109+00:00] [AdminServer] [ERROR] [] [oracle.soa.mediator.serviceEngine] [tid: [ACTIVE].ExecuteThread: '3' for queue: 'weblogic.kernel.Default (self-tuning)'] [userId: <anonymous>] [ecid: 9509de3c0774d59e:-2c8c14e5:13c6452f049:-8000-0000000000017281,0:2] [WEBSERVICE_PORT.name: PPRInvocation_pt] [APP: soa-infra] [composite_name: PPR] [component_name: PPRDataRouter] [component_instance_id: A72C5CC0658E11E2AF67035B1A7C2490] [J2EE_MODULE.name: fabric] [WEBSERVICE.name: pprinvocation_client_ep] [J2EE_APP.name: soa-infra] [composite_instance_id: 30006] Post 2 mess failed[[  
 oracle.fabric.common.FabricInvocationException: BINDING.JCA-12563  
 Exception occured when binding was invoked.  
 Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'merge' failed due to: DBWriteInteractionSpec Execute Failed Exception.  
 merge failed. Descriptor name: [PPRPersistenceService.PriceData].  
 Caused by java.sql.SQLException: Cannot call Connection.commit in distributed transaction. Transaction Manager will commit the resource manager when the distributed transaction is committed..  
 Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered retriable, likely due to a communication failure. To classify it as non-retriable instead add property nonRetriableErrorCodes with value "0" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers.  
 ".  
 The invoked JCA adapter raised a resource exception.  
 Please examine the above error message carefully to determine a resolution.  
     at oracle.integration.platform.blocks.adapter.fw.jca.cci.EndpointInteractionException.getFabricInvocationException(EndpointInteractionException.java:75)  
     at oracle.integration.platform.blocks.adapter.AdapterReference.getFabricInvocationException(AdapterReference.java:307)  
     at oracle.integration.platform.blocks.adapter.AdapterReference.post(AdapterReference.java:293)  
     at oracle.integration.platform.blocks.mesh.AsynchronousMessageHandler.doPost(AsynchronousMessageHandler.java:142)  
     at oracle.integration.platform.blocks.mesh.MessageRouter.post(MessageRouter.java:197)  
     at oracle.integration.platform.blocks.mesh.MeshImpl.post(MeshImpl.java:215)  
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)  
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)  
     at java.lang.reflect.Method.invoke(Method.java:597)  
     at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)  
     at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)  

Transaction Timeouts in Oracle SOA Suite SCA Applications

I am currently working with Oracle SOA Suite 11.1.1.6 on WebLogic 10.3.6 and ran into a problem with a deployed SCA application that retrieves data from a web service and merges the results in a table using a DbAdapter to an Oracle database data source.

However, if the service took more than 30 seconds to complete (typically when most of the result set already existed in the database during the merge) then the service would timeout (see below).

The reason for this is the default WebLogic JTA timeout, which is set to 30 seconds. You can adjust it in the WebLogic /console -> Home -> JTA -> Timeout Seconds. In my case 120 seconds did the trick.

 <Jan 23, 2013 8:25:47 PM GMT> <Warning> <oracle.soa.mediator.common> <BEA-000000> <Transaction commit failed due to excetipn  
 weblogic.transaction.RollbackException: Transaction timed out after 31 seconds  
 BEA1-674F6821D9B2  
 ....  
 <Jan 23, 2013 8:25:47 PM GMT> <Error> <oracle.soa.mediator.serviceEngine> <BEA-000000> <Rolling back transaction due to ORAMED-03503:[Transaction Commit]Unable to commit transaction. Transaction has been rolled back.Possible Fix:Check log file for any exceptions and contact Oracle Support Services for further help.>  
 <Jan 23, 2013 8:25:47 PM GMT> <Warning> <oracle.soa.mediator.common> <BEA-000000> <Transaction rollback failed due to exception  
 java.lang.IllegalStateException: Transaction does not exist  
 ...  
 <Jan 23, 2013 8:25:47 PM GMT> <Error> <oracle.soa.mediator.serviceEngine> <BEA-000000> <Error in rolling back transaction due to ORAMED-03504:[Transaction Rollback]Unable to rollback transaction.Possible Fix:Check log file for any exceptions and contact Oracle Support Services for further help.  
 oracle.tip.mediator.infra.exception.MediatorException: ORAMED-03504:[Transaction Rollback]Unable to rollback transaction.Possible Fix:Check log file for any exceptions and contact Oracle Support Services for further help.  
 ...  
 >  
 <Jan 23, 2013 8:25:47 PM GMT> <Error> <oracle.soa.mediator.serviceEngine> <BEA-000000> <Updating fault processing DMS metrics>  
 <Jan 23, 2013 8:25:47 PM GMT> <Error> <oracle.soa.mediator.serviceEngine> <BEA-000000> <Got an exception: weblogic.transaction.RollbackException: Transaction timed out after 31 seconds  
 BEA1-674F6821D9B2  
 oracle.tip.mediator.infra.exception.MediatorException: ORAMED-03503:[Transaction Commit]Unable to commit transaction. Transaction has been rolled back.Possible Fix:Check log file for any exceptions and contact Oracle Support Services for further help.  
     at oracle.tip.mediator.common.JTAHelper.commitTransaction(JTAHelper.java:265)  
     at oracle.tip.mediator.serviceEngine.MediatorServiceEngine.process(MediatorServiceEngine.java:881)  
     at oracle.tip.mediator.serviceEngine.MediatorServiceEngine.post(MediatorServiceEngine.java:663)  
     at oracle.integration.platform.blocks.mesh.AsynchronousMessageHandler.doPost(AsynchronousMessageHandler.java:142)  
     at oracle.integration.platform.blocks.mesh.MessageRouter.post(MessageRouter.java:197)  
     at oracle.integration.platform.blocks.mesh.MeshImpl.post(MeshImpl.java:215)  
     at sun.reflect.GeneratedMethodAccessor1974.invoke(Unknown Source)  
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)  
     at java.lang.reflect.Method.invoke(Method.java:597)  
     at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)  
     at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)  
     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)  
     at oracle.integration.platform.metrics.PhaseEventAspect.invoke(PhaseEventAspect.java:59)  
     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)  
     at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)  
     at $Proxy331.post(Unknown Source)  
     at oracle.integration.platform.blocks.soap.WebServiceEntryBindingComponent.doMessageProcessing(WebServiceEntryBindingComponent.java:1542)  
 ...