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"/>

Answer 1

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:

1. hibernate cfg xml settings for derby oracle and h2
2. difference between oracle jdbc driver classes
3. configuring jdbc pool high concurrency

Answer 2

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

1. 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.
2. 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.

3. 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>

Related Link:

1. hibernate connection pool configuration with c3p0 example
2. docs

If you want to add pool size in properties file, then follow this tutorial.

4.Are you using oracle 10g? if yes then it's ok otherwise Oracle10gDialect need to be changed for lower version.

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

5. Use hibernate.hbm2ddl.auto = validate mode. It is used in oracle 11g.

UPDATE:

6. 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

7. 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.

Answer 3

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?

Answer 4

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.