in Standalone-teiid.xml, find the "transactions" subsytem and change the "default-timeout" value.

        <subsystem xmlns="urn:jboss:domain:transactions:1.3">
            <core-environment>
                <process-id>
                    <uuid/>
                </process-id>
            </core-environment>
            <recovery-environment socket-binding="txn-recovery-environment" status-socket-binding="txn-status-manager"/>
            <coordinator-environment default-timeout="300"/>
        </subsystem>

Ramesh..

Are you properly closing your statements before executing next one? If you are using different connections are you closing them?

Kevin,

We found a workaround this, the connection is created with autoCommit=false,

That is not true, the default Teiid JDBC connection is always created with "autoCommit = true", there must some other configuration in your environment that may be setting the "autoCommit" to false, like on setting on URL or if you are using some pooling that may be doing it. When you create a connection, you can also call "connection.setAutoCommit(false)" then you would not need to call "connection.commit()"

But is it possible that there is a bug in the transaction manager's code that cancel an active transaction that eventually generate the above exception?

I do not think that is bug, the question to find is why is transaction is being aborted, timed-out, you cancelled it, database threw a exception etc which can lead to the error.

Ramesh..

What I am saying is when u call "setAutoCommit(false)", u are instructing the database that you are starting a local transaction, where you control the boundaries of your transaction with "commit" or "rollback". When you do a commit or roll back previous transaction will be completed and next one will start automatically.

Where as in "setAutoCommmit(true)", which is default, there every statement you execute gets executed in its own transaction, and it gets commited or rolled back depending upon what happened in the database.

I am not sure why the 3 times limit you need to what you are updating,  note that even though you are ran three different queries they all participated in the single transaction. See my previous comment about what could have happened. In the commit scenario you are simulating the "setCommit(true)" behavior manually in the code that is why it works.

Yes, at the end of the transaction boundary.

Hi,

Firstly we have done connection.setAutoCommit(false), to disable auto-detect mechanism of transactions by Teiid. After that, we are fetching results from a database and inserting that in another. For that we are using jdbc batch statements. So after every executeBatch(), we are doing connection.commit() (i.e. manually commiting the transaction). This scenario works for less records, but as the records size grows, it is posting this error:

2019-06-14 07:37:34,657 WARN  [org.teiid.CONNECTOR] (Worker11_QueryProcessorQueue131) SJ9mjhVhTI9X Connector worker process failed for atomic-request=SJ9mjhVhTI9X.6.0.12: org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2a2ed99f[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@a19ba05 connection handles=0 lastReturned=1560497854655 lastValidated=1560496256186 lastCheckedOut=1560497532188 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@3592c05d mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@56266dce[pool=DSCLOUDSQL20190613155827214] xaResource=XAResourceWrapperImpl@e18cfdc[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@a19ba05 pad=false overrideRmValue=null productName=MySQL productVersion=5.7.14-google-log jndiName=java:/DSCLOUDSQL20190613155827214] txSync=null]

        at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:282)

        at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:68)

        at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:201)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:353)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:498)

        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)

        at com.sun.proxy.$Proxy36.execute(Unknown Source)

        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)

        at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)

        at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)

        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)

        at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)

        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)

        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)

        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)

        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)

        at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)

        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)

        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)

        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

        at java.lang.Thread.run(Thread.java:748)

Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2a2ed99f[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@a19ba05 connection handles=0 lastReturned=1560497854655 lastValidated=1560496256186 lastCheckedOut=1560497532188 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@3592c05d mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@56266dce[pool=DSCLOUDSQL20190613155827214] xaResource=XAResourceWrapperImpl@e18cfdc[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@a19ba05 pad=false overrideRmValue=null productName=MySQL productVersion=5.7.14-google-log jndiName=java:/DSCLOUDSQL20190613155827214] txSync=null]

Also the connections which are opened are closed properly and we are using XA-datasources for transactions.

Need to know what does this log suggests , also when we are fetching the results from one db to insert into another , is it opening a new connection which is remaining unclosed?

A quick google search revealed couple solutions, try these and see if this solves the issue.

Getting error when accessing 2 data source

https://forum.camunda.org/t/problems-with-persistance-could-not-enlist-in-transaction-on-entering-meta-aware-object/5278

Jboss-eap-6.1 error while BCC full deployment o... | Oracle Community