java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction

Spring JIRA | Sam Carr | 1 decade ago
tip
Click on the to mark the solution that helps you, Samebug will learn from it.
As a community member, you’ll be rewarded for you help.
  1. 0

    It seems that when an exception of any sort is thrown out of a *non-transactional* DAO call, the Oracle DB connection can get left in a strange limbo state. Thus, the next *transactional* code to use the same DB connection will see the following exception: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168) at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1687) at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653) at oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:1600) at oracle.jdbc.OracleConnectionWrapper.setTransactionIsolation(OracleConnectionWrapper.java:162) at org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:173) at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:192) at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:283) at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:226) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:89) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176) at $Proxy29.qqDemonstrateSetTransactionBugStep2(Unknown Source) at com.py.controller.SETTRANSACTIONTestController.handleRequestInternal(SETTRANSACTIONTestController.java:22) ...snipped. The issues is further debated in the Spring forums, with another user able to recreate the issue: http://forum.springframework.org/showthread.php?t=25423 Presumably on the non-transactional call, some code is calling Oracle with "SET TRANSACTION ..." to setup some transactional info, but is never resetting that transaction. Perhaps the transaction is being left as neither committed or rolled back, so when the next DAO call comes along to start a real transaction Oracle barfs. Note that it is a documented oddity of the Oracle JDBC driver that it explicitly does not reset transactional state on connections when they are returned to the pool. This means that the next user of the connection stumbles over the half-baked transaction. Presumably Spring should either not be doing anything transactional in the first place when making the non-transactional call, or it should correctly clean up after itself when the exception is thrown, leaving the DB connection in a proper state? Once a DB connection has been broken in this way, it stays broken until I restart Tomcat, hence this can totally bring a website to its knees.

    Spring JIRA | 1 decade ago | Sam Carr
    java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction
  2. 0

    It seems that when an exception of any sort is thrown out of a *non-transactional* DAO call, the Oracle DB connection can get left in a strange limbo state. Thus, the next *transactional* code to use the same DB connection will see the following exception: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207) at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168) at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1687) at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653) at oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:1600) at oracle.jdbc.OracleConnectionWrapper.setTransactionIsolation(OracleConnectionWrapper.java:162) at org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:173) at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:192) at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:283) at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:226) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:89) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176) at $Proxy29.qqDemonstrateSetTransactionBugStep2(Unknown Source) at com.py.controller.SETTRANSACTIONTestController.handleRequestInternal(SETTRANSACTIONTestController.java:22) ...snipped. The issues is further debated in the Spring forums, with another user able to recreate the issue: http://forum.springframework.org/showthread.php?t=25423 Presumably on the non-transactional call, some code is calling Oracle with "SET TRANSACTION ..." to setup some transactional info, but is never resetting that transaction. Perhaps the transaction is being left as neither committed or rolled back, so when the next DAO call comes along to start a real transaction Oracle barfs. Note that it is a documented oddity of the Oracle JDBC driver that it explicitly does not reset transactional state on connections when they are returned to the pool. This means that the next user of the connection stumbles over the half-baked transaction. Presumably Spring should either not be doing anything transactional in the first place when making the non-transactional call, or it should correctly clean up after itself when the exception is thrown, leaving the DB connection in a proper state? Once a DB connection has been broken in this way, it stays broken until I restart Tomcat, hence this can totally bring a website to its knees.

    Spring JIRA | 1 decade ago | Sam Carr
    java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction

    2 unregistered visitors

    Root Cause Analysis

    1. java.sql.SQLException

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

      at oracle.jdbc.driver.DatabaseError.throwSqlException()
    2. Oracle jdbc
      OracleConnectionWrapper.setTransactionIsolation
      1. oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      2. oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      3. oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      4. oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
      5. oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
      6. oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
      7. oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
      8. oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1687)
      9. oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1653)
      10. oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:1600)
      11. oracle.jdbc.OracleConnectionWrapper.setTransactionIsolation(OracleConnectionWrapper.java:162)
      11 frames
    3. Spring Framework
      DataSourceTransactionManager.doBegin
      1. org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:173)
      2. org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:192)
      2 frames
    4. Spring Tx
      TransactionInterceptor.invoke
      1. org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:283)
      2. org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:226)
      3. org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:89)
      3 frames
    5. Spring AOP
      JdkDynamicAopProxy.invoke
      1. org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
      2. org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
      2 frames
    6. Unknown
      $Proxy29.qqDemonstrateSetTransactionBugStep2
      1. $Proxy29.qqDemonstrateSetTransactionBugStep2(Unknown Source)
      1 frame
    7. com.py.controller
      SETTRANSACTIONTestController.handleRequestInternal
      1. com.py.controller.SETTRANSACTIONTestController.handleRequestInternal(SETTRANSACTIONTestController.java:22)
      1 frame