4 Replies Latest reply on May 31, 2015 4:57 PM by Steven Hawkins

    Transaction error when accessing 2 data source

    kchen007 Apprentice

      We created a simple vdb that will access 2 data sources(both are Microsoft SQL Server 2008 R2), for simplicity, each data source contains only one table. Then we create a view model that will contains only one table that will combine the 2 source table with INNER join.

       

      If we started a transaction and call the select the view model table, I got the following exception, how can I fix it?

      Thanks.

       

      14:15:27,249 WARN  [com.arjuna.ats.arjuna] (Worker1_QueryProcessorQueue17) ARJUNA012140: Adding multiple last resources is disallowed. Trying to add LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@278195ae[connectionListener=65010b20 connectionManager=2c89e6c5 warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffff2201e92e:-54aa0af2:53fe2e31:9, node_name=1, branch_uid=0:ffff2201e92e:-54aa0af2:53fe2e31:d, subordinatenodename=null, eis_name=java:/PROC_FIELD > productName=Microsoft SQL Server productVersion=10.50.1617 jndiName=java:/PROC_FIELD])), but already have LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@184684cb[connectionListener=38dd6c5 connectionManager=118da65b warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffff2201e92e:-54aa0af2:53fe2e31:9, node_name=1, branch_uid=0:ffff2201e92e:-54aa0af2:53fe2e31:c, subordinatenodename=null, eis_name=java:/PROC_WELL > productName=Microsoft SQL Server productVersion=10.50.1617 jndiName=java:/PROC_WELL]))
      14:15:27,268 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue17) Connector worker process failed for atomic-request=lBsM4BVUJsmj.1.3.1: org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@65010b20[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@389e98fa connection handles=0 lastUse=1409166927267 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubject@344d6220 pool internal context=SemaphoreArrayListManagedConnectionPool@b54b047[pool=PROC_FIELD] xaResource=LocalXAResourceImpl@278195ae[connectionListener=65010b20 connectionManager=2c89e6c5 warned=false currentXid=null productName=Microsoft SQL Server productVersion=10.50.1617 jndiName=java:/PROC_FIELD] txSync=null]
      at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:285)
      at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:57)
      at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:186) [teiid-api-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:289) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:163) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:211) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:563) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:68) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:356) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:283) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:257) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:444) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:326) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.7.0.Final.jar:8.7.0.Final]
      at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [rt.jar:1.7.0_60]
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [rt.jar:1.7.0_60]
      at java.lang.Thread.run(Unknown Source) [rt.jar:1.7.0_60]
      Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@65010b20[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@389e98fa connection handles=0 lastUse=1409166927267 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubject@344d6220 pool internal context=SemaphoreArrayListManagedConnectionPool@b54b047[pool=PROC_FIELD] xaResource=LocalXAResourceImpl@278195ae[connectionListener=65010b20 connectionManager=2c89e6c5 warned=false currentXid=null productName=Microsoft SQL Server productVersion=10.50.1617 jndiName=java:/PROC_FIELD] txSync=null]
      at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:147)
      at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:281)
      ... 47 more
      Caused by: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@65010b20[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@389e98fa connection handles=0 lastUse=1409166927267 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.PoolBySubject@344d6220 pool internal context=SemaphoreArrayListManagedConnectionPool@b54b047[pool=PROC_FIELD] xaResource=LocalXAResourceImpl@278195ae[connectionListener=65010b20 connectionManager=2c89e6c5 warned=false currentXid=null productName=Microsoft SQL Server productVersion=10.50.1617 jndiName=java:/PROC_FIELD] txSync=null]
      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:604)
      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:467)
      at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:139)
      ... 48 more
      Caused by: javax.resource.ResourceException: IJ000461: Could not enlist in transaction on entering meta-aware object
      at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:474)
      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:599)
      ... 50 more
      Caused by: javax.transaction.SystemException: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff2201e92e:-54aa0af2:53fe2e31:9 status: ActionStatus.ABORT_ONLY >
      at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:552)
      at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:282)
      at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:467)
      ... 51 more

        • 1. Re: Transaction error when accessing 2 data source
          kchen007 Apprentice


          Just want to make sure that the data source is defined as xa-datasource, I used the jboss management console to add them:

           

                          <xa-datasource jndi-name="java:/PROC_FIELD" pool-name="PROC_FIELD_FROMCONSOLE" use-ccm="false">

                              <xa-datasource-property name="URL">

                                  jdbc:sqlserver://localhost\NORTHWIND;DatabaseName=TestDB1

                              </xa-datasource-property>

                              <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

                              <driver>sqlserver</driver>

                              <xa-pool>

                                  <is-same-rm-override>false</is-same-rm-override>

                                  <interleaving>false</interleaving>

                                  <pad-xid>false</pad-xid>

                                  <wrap-xa-resource>false</wrap-xa-resource>

                              </xa-pool>

                              <security>

                                  <user-name>removed</user-name>

                                  <password>removed</password>

                                 

                              </security>

                              <validation>

                                  <validate-on-match>false</validate-on-match>

                                  <background-validation>false</background-validation>

                              </validation>

                              <statement>

                                  <share-prepared-statements>false</share-prepared-statements>

                              </statement>

                          </xa-datasource>

          1 of 1 people found this helpful
          • 2. Re: Transaction error when accessing 2 data source
            Steven Hawkins Master

            Are you still seeing the exception after using xa data sources?

            • 3. Re: Transaction error when accessing 2 data source
              Allen Yu Newbie

              can you do a screen by screen show about how you do this? can't figure out how you did this. thanks.

              • 4. Re: Transaction error when accessing 2 data source
                Steven Hawkins Master

                Allen,

                 

                You may want to start a new thread with your specific issue as this topic is old.

                 

                Steve