Announcement Announcement Module
Collapse
No announcement yet.
ORA-01453 when using DataSourceTransactionManager Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ORA-01453 when using DataSourceTransactionManager

    I am using Springs DataSourceTransactionManager and the TransactionProxyFactoryBean to provide declarative tx support over a number of service classes. Almost all methods proxied utilize the default isolation level. However, there are 3 methods that utilize SERIALIZABLE. In order to ensure that these methods utilize a new transaction their propogation behavior is set to PROPOGATION_NESTED.

    Once in a blue moon I receive a "java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction" exception on our production systems when one of these serializable methods is invoked.

    This exception occurs at:

    org.springframework.jdbc.datasource.DataSourceUtil s.prepareConnectionForTransaction(DataSourceUtils. java:160)

    when the DataSourceUtils attempts to set the isolation level.

    Once this error occurs in production it occurs with great regularity - most likely as a result of pulling the same "crippled" connection out of the pool. We then have to bounce tomcat, we don't have other access to the DataSource, to stabilize the system.

    We use DBCP as our connection pool. DBCP is configured to validate all connections when they are drawn from the pool. Neither the defaultAutoCommit or defaultTransactionIsolation parameters are set. So all connection should come out of the connection pool with autocommit set to true and the isolation level set to READ_COMMITTED.

    The only way I think this could happen, is if a connection in the pool is set to autocommit false. When Spring requests the connection from the pool, DBCP would validate it thereby starting a JDBC transaction. When Spring then tries to set the isolation level to SERIALIZABLE the driver complains because a statement in a pre-existing JDBC tx has already been executed.

    However, the DataSourceTransactionManager and the DataSourceUtils seem very particular about cleaning and resetting the connections before returning them to the pool. So no connection should ever be returned to the pool with its autoCommit set to false.

    We never see this behavior in development. However, on those servers, tomcat is rarely up for more than a few hours between builds.

    Can anyone think of another cause of this issue? How Spring would return a connection back to the pool without resetting its autoCommit back to true, perhaps when a checked exception is thrown?

    thanks in advance,

    carlos

    Production is using:
    Spring v1.2.1
    Oracle DB v8.1.7
    Oracle JDBC Driver v10.2.0.1.0
    Apache DBCP v1.2.1
    Tomcat v5.5.9
    Sun JVM v1.5.0_05-b05
    Windows Server 2003

    TxManager and ProxyFactoryBean definitions:
    <bean id="TransactionManager"
    class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
    <property name="dataSource">
    <ref local="EDISDataSource" />
    </property>
    </bean>

    <bean id="TransactionProxyTemplate"
    class="org.springframework.transaction.interceptor .TransactionProxyFactoryBean"
    abstract="true">
    <property name="transactionManager">
    <ref local="TransactionManager" />
    </property>
    <property name="transactionAttributes">
    <props>
    <prop key="authenticate*">PROPAGATION_REQUIRED</prop>
    <prop key="add*">PROPAGATION_REQUIRED</prop>
    <prop key="update*">PROPAGATION_REQUIRED</prop>
    <prop key="save*">PROPAGATION_REQUIRED</prop>
    <prop key="delete*">PROPAGATION_REQUIRED</prop>
    <prop key="next*">PROPAGATION_NESTED,ISOLATION_SERIALIZA BLE</prop>
    <prop key="*">PROPAGATION_SUPPORTS,readOnly</prop>
    </props>
    </property>
    </bean>

    STACK TRACE

    WARN [http-80-Processor22][11 Apr 2006 13:08:22,124] org.springframework.remoting.support.RemoteInvocat ionTraceInterceptor(RemoteInvocationTraceIntercept or.java:78) - Processing of HessianServiceExporter remote call resulted in fatal exception: gov.usitc.eof.ws.EDISWebService.getNextImageID

    org.springframework.transaction.CannotCreateTransa ctionException: 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(T4CTTIoe r.java:331)

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoe r.java:288)

    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java: 743)

    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStateme nt.java:207)

    at oracle.jdbc.driver.T4CStatement.executeForRows(T4C Statement.java:946)

    at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:1168)

    at oracle.jdbc.driver.OracleStatement.executeInternal (OracleStatement.java:1687)

    at oracle.jdbc.driver.OracleStatement.execute(OracleS tatement.java:1653)

    at oracle.jdbc.driver.PhysicalConnection.setTransacti onIsolation(PhysicalConnection.java:1600)

    at org.apache.commons.dbcp.DelegatingConnection.setTr ansactionIsolation(DelegatingConnection.java:277)

    at org.apache.commons.dbcp.PoolingDataSource$PoolGuar dConnectionWrapper.setTransactionIsolation(Pooling DataSource.java:308)

    at org.springframework.jdbc.datasource.DataSourceUtil s.prepareConnectionForTransaction(DataSourceUtils. java:160)

    at org.springframework.jdbc.datasource.DataSourceTran sactionManager.doBegin(DataSourceTransactionManage r.java:178)

    at org.springframework.transaction.support.AbstractPl atformTransactionManager.getTransaction(AbstractPl atformTransactionManager.java:234)

    at org.springframework.transaction.interceptor.Transa ctionAspectSupport.createTransactionIfNecessary(Tr ansactionAspectSupport.java:217)

    at org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:50)

    at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :144)

    at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:174)

    at $Proxy1.nextId(Unknown Source)

    at gov.usitc.eof.ws.EDISWebServiceImpl.getNextImageID (Unknown Source)

    at sun.reflect.GeneratedMethodAccessor264.invoke(Unkn own Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)

    at java.lang.reflect.Method.invoke(Unknown Source)

    at org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:288)

    at org.springframework.aop.framework.ReflectiveMethod Invocation.invokeJoinpoint(ReflectiveMethodInvocat ion.java:155)

    at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :122)

    at org.springframework.remoting.support.RemoteInvocat ionTraceInterceptor.invoke(RemoteInvocationTraceIn terceptor.java:68)

    at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :144)

    at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:174)

    at $Proxy7.getNextImageID(Unknown Source)

    at sun.reflect.GeneratedMethodAccessor263.invoke(Unkn own Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)

    at java.lang.reflect.Method.invoke(Unknown Source)

    at com.caucho.hessian.server.HessianSkeleton.invoke(H essianSkeleton.java:188)

    at org.springframework.remoting.caucho.HessianService Exporter.handleRequest(HessianServiceExporter.java :87)

    at org.springframework.web.servlet.mvc.SimpleControll erHandlerAdapter.handle(SimpleControllerHandlerAda pter.java:44)

    at org.springframework.web.servlet.DispatcherServlet. doDispatch(DispatcherServlet.java:684)

    at org.springframework.web.servlet.DispatcherServlet. doService(DispatcherServlet.java:625)

    at org.springframework.web.servlet.FrameworkServlet.s erviceWrapper(FrameworkServlet.java:386)

    at org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:355)

    at javax.servlet.http.HttpServlet.service(HttpServlet .java:709)

    at javax.servlet.http.HttpServlet.service(HttpServlet .java:802)

    at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:252)

    at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:173)

    at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:213)

    at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:178)

    at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:126)

    at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:105)

    at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:107)

    at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:148)

    at org.apache.coyote.http11.Http11Processor.process(H ttp11Processor.java:856)

    at org.apache.coyote.http11.Http11Protocol$Http11Conn ectionHandler.processConnection(Http11Protocol.jav a:744)

    at org.apache.tomcat.util.net.PoolTcpEndpoint.process Socket(PoolTcpEndpoint.java:527)

    at org.apache.tomcat.util.net.LeaderFollowerWorkerThr ead.runIt(LeaderFollowerWorkerThread.java:80)

    at org.apache.tomcat.util.threads.ThreadPool$ControlR unnable.run(ThreadPool.java:684)

    at java.lang.Thread.run(Unknown Source)

  • #2
    I believe the problem is with Oracle 8 (see this post). The only solution I found was to explicitly mark the isolation level as Unspecified. Hope that helps.

    Comment

    Working...
    X