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

[murthytvsn]

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:@[e-mail]: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?

[rajeshnarkhed]

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

[murthytvsn]

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