12 Replies Latest reply on Jun 14, 2019 7:03 AM by rareddy

How to set the transaction timeout?

kchen007 Oct 1, 2013 6:46 PM

We have some long running queries that will run in SEQUENCE(one after the other) and from time to time, the server will thow exceptions saying the transaction has been "aborted" when it try to get a managed connection.

Strange thing is that if we run each one of the queries in the SEQUENCE individually, the query will pass without any error.

Once the server throw exception, we cannot issue any query to that datasource, we have to restart server.

Is this related to transaction timeout? How can I adjust it? we are running Teiid 8.4 final + JBoss EAP6.1

Here is the exception:

11:54:50,737
TRACE [com.arjuna.ats.jta] (Worker0_QueryProcessorQueue1201)
TransactionImpleManager.resume

11:54:50,737 TRACE [com.arjuna.ats.arjuna] (Worker0_QueryProcessorQueue1201)
BasicAction::addChildThread () action 0:ffff868498c3:6cc3d41e:524afb89:10
adding Thread[Worker0_QueryProcessorQueue1201,5,main]

11:54:50,737 TRACE [com.arjuna.ats.arjuna] (Worker0_QueryProcessorQueue1201)
BasicAction::addChildThread () action 0:ffff868498c3:6cc3d41e:524afb89:10
adding Thread[Worker0_QueryProcessorQueue1201,5,main] result = false

11:54:50,739 TRACE [com.arjuna.ats.jta] (Worker0_QueryProcessorQueue1201)
TransactionImple.getStatus

11:54:50,741 DEBUG [org.teiid.COMMAND_LOG]
(Worker0_QueryProcessorQueue1201)    ERROR SRC
COMMAND:      endTime=2013-10-01 11:54:50.741
requestID=knUuht8CMFW+.111
sourceCommandID=0       executionID=7
txID=TransactionImple < ac, BasicAction: 0:ffff868498c3:6cc3d41e:524afb89:10
status: ActionStatus.ABORTED >
modelName=OW5000        translatorName=delegate
sessionID=knUuht8CMFW+
principal=user@dsds-security-domain     finalRowCount=null

11:54:50,741 DEBUG [org.teiid.COMMAND_LOG]
(Worker0_QueryProcessorQueue1201)    ERROR SRC
COMMAND:      endTime=2013-10-01 11:54:50.741
requestID=knUuht8CMFW+.111
sourceCommandID=0       executionID=7
txID=TransactionImple < ac, BasicAction: 0:ffff868498c3:6cc3d41e:524afb89:10
status: ActionStatus.ABORTED >     modelName=OW5000
translatorName=delegate sessionID=knUuht8CMFW+
principal=user@dsds-security-domain     finalRowCount=null

11:54:50,741 WARN [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue1201)
Connector worker process failed for atomic-request=knUuht8CMFW+.111.0.7:
org.teiid.translator.TranslatorException: TEIID60000 javax.resource.ResourceException:
IJ000460: Error checking for a transaction

        at
org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:171)
[teiid-api-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:190)
[teiid-api-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.translator.BaseDelegatingExecutionFactory.getConnection(BaseDelegatingExecutionFactory.java:116)
[teiid-api-8.4.0.Final.jar:8.4.0.Final]

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:218)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:446)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:285)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:306)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:149)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:112)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:153)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:435)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:320)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:248)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
[teiid-engine-8.4.0.Final.jar:8.4.0.Final]

        at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
[rt.jar:1.6.0_27]

        at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
[rt.jar:1.6.0_27]

        at
java.lang.Thread.run(Thread.java:662) [rt.jar:1.6.0_27]

Caused by: javax.resource.ResourceException: IJ000460: Error checking for a
transaction

        at
org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:362)
[ironjacamar-core-impl-1.0.15.Final.jar:1.0.15.Final]

        at
org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464)
[ironjacamar-core-impl-1.0.15.Final.jar:1.0.15.Final]

        at
org.teiid.resource.spi.WrappedConnectionFactory.getConnection(WrappedConnectionFactory.java:58)
[teiid-api-8.4.0.Final.jar:8.4.0.Final]

        at
org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:169)
[teiid-api-8.4.0.Final.jar:8.4.0.Final]

1. Re: How to set the transaction timeout?

rareddy Oct 2, 2013 7:37 AM (in response to kchen007)

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..

2. Re: How to set the transaction timeout?

kchen007 Oct 2, 2013 9:33 AM (in response to rareddy)

Thanks Ramesh.

I have changed the default value from 300 to 600, still got the same exception and the query does not take 10 minutes. Maybe something else is wrong?

We also tried another case, we run the same query in a loop , when the loop goes into 3rd time, the above exception thrown. Maybe something was cached?
Actions
3. Re: How to set the transaction timeout?

rareddy Oct 2, 2013 9:39 AM (in response to kchen007)

Are you properly closing your statements before executing next one? If you are using different connections are you closing them?
Actions
4. Re: How to set the transaction timeout?

kchen007 Oct 2, 2013 11:19 AM (in response to rareddy)

We are re-using the same connection, but will always close the statement for each query.
Actions
5. Re: How to set the transaction timeout?

kchen007 Oct 2, 2013 3:08 PM (in response to kchen007)

We found a workaround this, the connection is created with autoCommit=false, since this is a query, we did not call the connection.commit(), this leave the transaction wrapper open in the server. After timeout, the transaction manager will wake up and cancel the transactions.

After we added call to connection.commit(), we were able to finish the query.

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?

thanks
Kevin
Actions
6. Re: How to set the transaction timeout?

rareddy Oct 2, 2013 3:19 PM (in response to kchen007)

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..
Actions
7. Re: How to set the transaction timeout?

kchen007 Oct 2, 2013 3:29 PM (in response to rareddy)

when we create the connection, we call the setAutoCommit(false) from client code.

there is no exceptions from database, no intentional abort of the transaction.

the new test we did, we issue the same query in a loop, if we did not call connection.commit() after each query, the exception will be thrown at 3rd time.

If we add the connection.commit(), the loop will finish(10 times).
Actions
8. Re: How to set the transaction timeout?

rareddy Oct 2, 2013 3:45 PM (in response to kchen007)

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.
Actions
9. Re: How to set the transaction timeout?

kchen007 Oct 2, 2013 3:52 PM (in response to rareddy)

Ramesh:

Thanks.

Just want to confirm this: in case of setAutoCommit(false), we need call connection.commit() for queries?

Kevin
Actions
10. Re: How to set the transaction timeout?

rareddy Oct 2, 2013 4:02 PM (in response to kchen007)

Yes, at the end of the transaction boundary.
Actions
11. Re: How to set the transaction timeout?

barfa.anil Jun 14, 2019 4:46 AM (in response to kchen007)

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?
Actions
12. Re: How to set the transaction timeout?

rareddy Jun 14, 2019 7:03 AM (in response to barfa.anil)

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
Actions

Go to original post