Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Rollback failures with Oracle under load
PostPosted: Mon Mar 12, 2012 12:06 pm 
Newbie

Joined: Tue Aug 31, 2010 9:41 am
Posts: 5
Hi,

we get sporadical errors on rollback with Oracle when executing selects/inserts/updates/deletes simultaneously by several threads (e.g. 20 threads working non-stop).

Here is the stack dump of such an error:

Code:
org.springframework.transaction.TransactionSystemException: Could not roll back JPA transaction; nested exception is javax.persistence.PersistenceException: unexpected error when rollbacking
   at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:498)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:845)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:822)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:411)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:114)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
   at $Proxy71.nextFreeNumber(Unknown Source)
   at de.vps.act.kernel.numberrange.service.runnables.CallNextFreeNumber.doSingleOperation(CallNextFreeNumber.java:10)
   at de.vps.act.kernel.numberrange.service.runnables.BaseRunnable.run(BaseRunnable.java:52)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
   at java.lang.Thread.run(Unknown Source)
Caused by: javax.persistence.PersistenceException: unexpected error when rollbacking
   at org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:110)
   at org.springframework.orm.jpa.JpaTransactionManager.doRollback(JpaTransactionManager.java:494)
   ... 14 more
Caused by: org.hibernate.TransactionException: JDBC rollback failed
   at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:204)
   at org.hibernate.ejb.TransactionImpl.rollback(TransactionImpl.java:107)
   ... 15 more
Caused by: java.sql.SQLException: Getrennte Verbindung
   at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
   at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
   at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:3450)
   at oracle.jdbc.OracleConnectionWrapper.rollback(OracleConnectionWrapper.java:135)
   at org.hibernate.transaction.JDBCTransaction.rollbackAndResetAutoCommit(JDBCTransaction.java:217)
   at org.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:196)
   ... 16 more


Some years ago we had the same problem with Oracle using another persistence framework (http://db.apache.org/torque/).
At that time we solved this problem by using oracle.jdbc.pool.OracleDataSource and oracle.jdbc.pool.OracleConnectionCacheManager
instead of oracle.jdbc.pool.OracleConnectionPoolDataSource and org.apache.torque.dsfactory.SharedPoolDataSourceFactory.

Is there any experience about that problem?
Is there a better solution than using Oracle's own pool implementation?

Here is our current Spring configuration:
Code:
    <bean id="dataSource" class="oracle.jdbc.xa.client.OracleXADataSource" />
   
    <bean id="dataSourcePool" class="org.apache.commons.dbcp.datasources.SharedPoolDataSource">
        <property name="connectionPoolDataSource" ref="dataSource"/>
        <property name="defaultTransactionIsolation" value="2"/>
        <property name="maxActive" value="10"/>
        <property name="maxIdle" value="5"/>
        <property name="maxWait" value="5000"/>
        <property name="testOnBorrow" value="true"/>
        <property name="testOnReturn" value="true"/>
        <property name="testWhileIdle" value="true"/>
        <property name="validationQuery" value="select 1 from dual"/>
        <property name="timeBetweenEvictionRunsMillis" value="30000"/>
        <property name="minEvictableIdleTimeMillis" value="30000"/>
        <property name="numTestsPerEvictionRun" value="10"/>
    </bean>


Thanks and regards
MaMue


Top
 Profile  
 
 Post subject: Re: Rollback failures with Oracle under load
PostPosted: Tue Mar 13, 2012 3:39 am 
Expert
Expert

Joined: Tue Jun 16, 2009 3:36 am
Posts: 988
Quote:
Caused by: java.sql.SQLException: Getrennte Verbindung


This means the physical connection to your database was reset (broken).
I don't think I lies on the connection-pool implementation.
You should first try to find out, why the connection was reset.
Had the database-engine-process or the machine resource-problems at this time?
Check the database-logs and system logs of the machine where the database runs.
Had the network some problems?


Top
 Profile  
 
 Post subject: Re: Rollback failures with Oracle under load
PostPosted: Tue Mar 13, 2012 12:51 pm 
Newbie

Joined: Tue Aug 31, 2010 9:41 am
Posts: 5
Hi pb00067,

thank you for your response!

Quote:
This means the physical connection to your database was reset (broken).
I don't think I lies on the connection-pool implementation.
You should first try to find out, why the connection was reset.
Had the database-engine-process or the machine resource-problems at this time?

I don't think so.
We are testing by some simple jUnit tests running on my machine.
The oracle server also runs on my machine.
There is enough free physical RAM on my machine (2.8 of 3.3 GB are used)

Quote:
Check the database-logs and system logs of the machine where the database runs.

There are no warnings/errors in Windows-EventLog.
There are no *.log files below c:\Programme\Oracle\product\11.1.0\db_1 with current file date+time except a single file with a single line: a current timestamp.

Quote:
Had the network some problems?

There is no network involved, database and JVM run on the same machine.

I think there is a problem between apache commons dbcp implementation and Oracle's javax.sql.ConnectionPoolDataSource implementation oracle.jdbc.xa.client.OracleXADataSource.
Today I changed the properties of the org.apache.commons.dbcp.datasources.SharedPoolDataSource:
I increased maxActive from 10 to 50 and set maxWait to -1 (= infinite, formerly: 5000 msec).

Now I get complete different errors: A lot of "ORA-01453: SET TRANSACTION muss erste Anweisung der Transaktion sein", and nothing else.
We set the isolation level to 2 (ReadCommitted, property defaultTransactionIsolation of the SharedPoolDataSource instance).

Thanks and regards
MaMue


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.