2 Replies Latest reply on Mar 25, 2004 11:08 AM by Murthy

    ORA-01453: SET TRANSACTION must be first statement of transa

    Murthy Newbie

      hi,

      I am using oracle-ds.xml as below.


      <local-tx-datasource>
      <jndi-name>YesmailClusterOne</jndi-name>
      <!--<connection-url>jdbc:oracle:thin:@tdng.intra.infousa.com:1521:devtd</connection-url>-->
      <!--<connection-url>jdbc:oracle:thin:@newtd.intra.infousa.com:1521:qa</connection-url>-->
      <connection-url>jdbc:oracle:thin:@test@newtd.com:1521:td</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>con</user-name>
      con
      <min-pool-size>5</min-pool-size>
      <max-pool-size>100</max-pool-size>
      <blocking-timeout-millis>5000</blocking-timeout-millis>
      <idle-timeout-minutes>15</idle-timeout-minutes>

      <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
      <!-- Checks the Oracle error codes and messages for fatal errors -->
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <!-- sql to call when connection is created
      <new-connection-sql>some arbitrary sql</new-connection-sql>
      -->

      <!-- sql to call on an existing pooled connection when it is obtained from pool - the OracleValidConnectionChecker is prefered
      <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
      -->
      <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
      </local-tx-datasource>



      Some times I get
      ORA-01453: SET TRANSACTION must be first statement of transaction
      error with below log

      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
      at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
      at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
      at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
      at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
      at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
      at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:854)
      at oracle.jdbc.driver.OracleConnection.setTransactionIsolation(OracleConnection.java:1634)
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkState(BaseWrapperManagedConnection.java:40
      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkTransaction(BaseWrapperManagedConnection.j
      at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:758)
      at org.jboss.resource.adapter.jdbc.WrappedStatement.checkTransaction(WrappedStatement.java:773)
      at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:210)

      I know it's happening after getting the connection form the pool and when executing the query. Do any one know the solution for this?



        • 1. Re: ORA-01453: SET TRANSACTION must be first statement of tr
          rajeshnarkhed Newbie

          hi,

          i m gettiing the same error under load in prod server. The error occurs intermmitently during a JDBC insert .

          But my oracle-ds.xml doesnt contain the <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
          tag.

          and it has the followinig values

          <min-pool-size>0</min-pool-size>
          <max-pool-size>100</max-pool-size>
          <blocking-timeout-millis>5000</blocking-timeout-millis>
          <idle-timeout-minutes>3</idle-timeout-minutes>

          did u manage to get any workaround/solution for this problem? pls advise if i m missing out something.

          thnks
          RN

          • 2. Re: ORA-01453: SET TRANSACTION must be first statement of tr
            Murthy Newbie

            After diagnosing the code I found in one place after getting the connection we change the transaction type and before closing the connection we are setting it back to original transaction type. After commenting that code I never got that error.

            Thanks
            Murthy