Transaction management with Spring and Hibernate make inactive transactions

Question

I'm managing a Java Web Application with Spring and Hibernate. I use Spring and Hibernate tools to handle the persistence level, so I don't need to commit\rollback my transactions. The application is concurrent, so the users can modify the same records and I decided to use Read Committed as isolation level.

The problem is sometimes I find JDBC errors in the log, and all the next requests go in the same error, blocking the application behaviour.

These are the components involved in the transaction management:

@Bean
    public SpringLocalSessionFactoryBean sessionFactory(DataSource dataSource){
        SpringLocalSessionFactoryBean bean = new SpringLocalSessionFactoryBean();
        bean.setConfigLocation(new ClassPathResource("hibernate.cfg.xml"));
        bean.setDataSource(dataSource);
        return bean;
    }

    @Bean
    public HibernateTransactionManager transactionManager(SessionFactory sessionFactory){
        HibernateTransactionManager tm = new HibernateTransactionManager();
        tm.setSessionFactory(sessionFactory);
        return tm;
    }

In the db session monitor, when this stuff happens, I got an INACTIVE transaction.

The error I get is the following:

WARN  - (SqlExceptionHelper.java:144) - SQL Error: 0, SQLState: null
14/03/2016 15:46:06 - ERROR - (SqlExceptionHelper.java:146) - Connection oracle.jdbc.driver.T4CConnection@1a6d7ad6 is closed.
14/03/2016 15:46:06 - ERROR - (AutoCompleterController.java:73) - could not prepare statement
org.hibernate.exception.GenericJDBCException: could not prepare statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1885)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
    at org.hibernate.loader.Loader.doQuery(Loader.java:910)
Caused by: java.sql.SQLException: Connection oracle.jdbc.driver.T4CConnection@1a6d7ad6 is closed.
    at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.checkOpen(DelegatingConnection.java:398)
    at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:279)
    at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
    ... 97 more

The problem is the transactions and the connections should be automatically opened and closed... And I expect that transactions that should fail for concurrent modification to get a rollback... But it seems they get inactive.

I attach my hibernate config.

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

<!--        <property name="hibernate.hbm2ddl.auto">update</property> -->
        <property name="hibernate.connection.isolation">2</property>

        <!-- Disable the second-level cache -->
        <property name="hibernate.cache.use_second_level_cache">false</property>
        <property name="hibernate.id.new_generator_mappings">true</property>
        <property name="hibernate.connection.autocommit">false</property>

        <!-- Show and print nice SQL on stdout -->
        <property name="hibernate.show_sql">false</property>
        <property name="hibernate.format_sql">false</property>
        <property name="hibernate.use_sql_comments">false</property>
        <property name="hibernate.generate_statistics">false</property>

    </session-factory>
</hibernate-configuration>

As connection library I use ojdbc. Any help will be appreciated. I don't know where to check anymore.

PS: I add that this error spawns like once every 2 days.

EDIT: Just another integration, this is what I have on my server.xml, could it be related to something here?

  <Resource name="jdbc/ToolSfDB" global="jdbc/ToolSfDB" auth="Container" type="javax.sql.DataSource"
      driverClassName="oracle.jdbc.OracleDriver"
      url="jdbc:oracle:thin:@//oracle01-internal.local:1521/orcl01"
      username="tools"
      password="mypwd"
      maxActive="10"
      maxIdle="2"
      minIdle="1"
      suspectTimeout="60"
      timeBetweenEvictionRunsMillis="30000"
      minEvictableIdleTimeMillis="60000"
      validationQuery="select 1 from dual"
      validationInterval="30000"
      testOnBorrow="true"
      removeAbandoned="true"
      removeAbandonedTimeout="60"
      abandonWhenPercentageFull="10"
      maxWait="10000"
      maxAge="60000"/>

does the problem happen while under heavy load? or does it happen during/after periods of little or no activity? — Nathan, Mar 25 '16 at 19:26

score 3 · Answer 1 · edited May 23 '17 at 12:19

It seems that the problem of transaction happens because it failed to open connection with oracle. If you are using oracle 10g as you have configured in hibernate.cfg.xml

    <session-factory>
      <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>

And You are using oracle thin driver:

 <Resource name="jdbc/ToolSfDB" global="jdbc/ToolSfDB" auth="Container" type="javax.sql.DataSource"
      driverClassName="oracle.jdbc.OracleDriver"
      url="jdbc:oracle:thin:@//oracle01-internal.local:1521/orcl01"

The driver class might not be configured properly.

Use oracle.jdbc.driver.OracleDriver instead of oracle.jdbc.OracleDriver.

  <Resource name="jdbc/ToolSfDB" global="jdbc/ToolSfDB" auth="Container" type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.driver.OracleDriver"
          url="jdbc:oracle:thin:@//oracle01-internal.local:1521/orcl01"

Notes: Make sure of the oracle jar version. it will work for ojdbc6.jar.

References:

Rahman, Is the issue resolved. Just Curious to find the actual reason ! — JChap, Apr 05 '16 at 16:22

SkyWalker · Answer 2 · 2016-03-29T02:39:08.623

At first, I want to give you couple of suggestions.

Please check version of oracle driver. If not same then use correct version of oracle driver. As you are using oracle 11, so download ojdbc6.jar file and use it.
Sometimes ORACLE_HOME was not set. So when the java application accessing the database, it causes the following error. So check that ORACLE_HOME is set or not.

Caused by: java.sql.SQLException: Connection oracle.jdbc.driver.T4CConnection@1a6d7ad6 is closed.

From the error,

it is defined that It means the connection was successfully established at some point, but when you try to commit right there, the connection was no longer open. The parameters you mentioned sound like connection pool settings. In your hibernate.cfg.xml file add

<property name="hibernate.connection.provider_class">org.hibernate.c3p0.internal.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">5</property> 
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.max_statements">50</property>

UPDATE:

As you are not using c3p0 pooling, then configure the pool by setting in properties file

accessToUnderlyingConnectionAllowed = true

and then it may work.

Default is false, it is a potential dangerous operation and misbehaving programs can do harmful things. (closing the underlying or continue using it when the guarded connection is already closed) Be careful and only use when you need direct access to driver specific extensions

In your server.xml please change

initialSize="10"
maxActive="100"
maxIdle="50"
minIdle="10"

The initialSize=10 is the number of connections that will be established when the connection pool is created

The maxActive=100 is the maximum number of established connections to the database.

The minIdle=10 is the minimum number of connections always established after the connection pool has reached this size.

The maxIdle attribute is a little bit trickier. It behaves differently depending on if the pool sweeper is enabled.

Please study configuring-jdbc-pool-high-concurrency tutorial for better understanding to configure server.xml. If you study this tutorial, I hope you can solve it by yours.

Hi, Thanks for your reply. 1- If it was something related to ORACLE HOME I would always have this problem and not once every few days, right? 2- I'm not using c3p0! I tried but it generates some PermGen issue, so I decided not to use it anymore. 3- I have oracle10gdialect for oracle 11. — lateralus, Mar 25 '16 at 08:28
@lateralus for PermGen issue solving you can follow http://stackoverflow.com/questions/35618880/java-lang-outofmemoryerror-permgen-space-exception-with-eclipse-spring-and-hib/35924718#35924718 — SkyWalker, Mar 25 '16 at 08:31
@lateralus Would you please check no. 5. and inform me if possible — SkyWalker, Mar 25 '16 at 08:32
@lateralus I have provided another 2 options. Please check. and I also supplied a million dollar tutorial. Please try to make it yours. Hope it will make you happy. — SkyWalker, Mar 27 '16 at 18:26

score 1 · Answer 3 · edited Mar 28 '16 at 14:10

1

you said that I add that this error spawns like once every 2 days. if your database trafic hight, I think manage connection pools for example use C3P0 and manage total connections,timeouts ... etc

for example

<property name="hibernate.c3p0.timeout">500</property>

this idle connection is removed from the pool (500 second)

and more knowledge here.

edited Mar 28 '16 at 14:10

itzmebibin

9,199
8
48
62

answered Mar 15 '16 at 00:28

kakashi hatake

1,175
2
10
18

Hi, I used in the past C3P0 but I abandoned it as there's a known bug that generates PermGen issue. I edited the question adding informations on the db connection I use. – lateralus Mar 15 '16 at 10:12
This error many times is created by connection pools or its configuration I think increase time outs or update jdbc drivers – kakashi hatake Mar 15 '16 at 10:30

score 1 · Answer 4 · answered Mar 16 '16 at 09:49

I'm investigating further more, and I suspect a scenario that could cause this issue.

Suppose I have a transactional method in the service layer. This method can call different objects on the DAO layer, each of them having his own connections.

Suppose the first call to DAO1 goes well, and updates a record.

Now another user make some other operations, and when my transactional method calls for DAO2 operation, it finds no connections available, causing the issue and leaving the transaction inactive after a timeout.

Could it be the issue?

If so, I found out that Tomcat Jdbc configuration has other parameters that I cound add:

rollbackOnReturn    
(boolean) If autoCommit==false then the pool can terminate the transaction by calling rollback on the connection as it is returned to the pool Default value is false.

commitOnReturn  
(boolean) If autoCommit==false then the pool can complete the transaction by calling commit on the connection as it is returned to the pool If rollbackOnReturn==true then this attribute is ignored. Default value is false.

At this point, could the first parameter set to true be the solution?

JChap · Answer 5 · 2016-04-05T17:17:28.913

I ran into similar issue, After tweaking removeAbandonedTimeout got it working. Here is how my configuration looks now.

    maxActive="60"
    maxIdle="30"

    maxWait="12000"
    validationQuery="select 1"
    validationInterval="30000"
    testOnBorrow="true"
    testOnReturn="false"
    testWhileIdle="true"
    timeBetweenEvictionRunsMillis="30000"
    minEvictableIdleTimeMillis="600000"

    logAbandoned="true"
    removeAbandoned="true" 
    removeAbandonedTimeout="180"

Explanation: Earlier my removeAbandonedTimeout is configured as 60. But some of my queries are taking more that 60 seconds to execute, So while the query is being executed the connection is closed by the pool as the abandoned timeout is reached. After query execution, spring transaction tries to commit that connection which is failing because the connection has been already closed.

For now i increased the removeAbandonedTimeout to 180, while we are trying optimize the query that is taking more time.

Transaction management with Spring and Hibernate make inactive transactions

5 Answers5

UPDATE: