10 Replies Latest reply on Oct 2, 2013 4:02 PM by Ramesh Reddy

    How to set the transaction timeout?

    kchen007 Apprentice

      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?
          Ramesh Reddy Master

          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 Apprentice

            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?

            • 3. Re: How to set the transaction timeout?
              Ramesh Reddy Master

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

              • 4. Re: How to set the transaction timeout?
                kchen007 Apprentice

                We are re-using the same connection, but will always close the statement for each query.

                • 5. Re: How to set the transaction timeout?
                  kchen007 Apprentice


                  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

                  • 6. Re: How to set the transaction timeout?
                    Ramesh Reddy Master

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

                    • 7. Re: How to set the transaction timeout?
                      kchen007 Apprentice


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

                      • 8. Re: How to set the transaction timeout?
                        Ramesh Reddy Master

                        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.

                        • 9. Re: How to set the transaction timeout?
                          kchen007 Apprentice

                          Ramesh:

                           

                          Thanks.

                           

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

                           

                          Kevin

                          • 10. Re: How to set the transaction timeout?
                            Ramesh Reddy Master

                            Yes, at the end of the transaction boundary.