Transaction management with Spring and Hibernate makes inactive transactions

Question

I control a Java web application using Spring and Hibernate. I use the Spring and Hibernate tools to handle the persistence level, so I do not need to commit / rollback my transactions. The application is parallel, so users can modify the same entries, and I decided to use Read Committed as the isolation level.

The problem is that sometimes I find JDBC errors in the log, and all of the following queries go with the same error, blocking the behavior of the application.

These are the components involved in 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 happens, I got transaction INACTIVE.

The error I am getting 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 with transactions, and connections should automatically open and close ... And I expect that transactions that should have failed for simultaneous modification, get rolled back ... But it seems that they are inactive.

I am attaching a hibernate configuration.

 <?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 a connection library, I am using ojdbc. Any help would be appreciated. I do not know where to check.

PS: I add that this error occurs just every 2 days.

EDIT: Another integration is what I have on my .xml server, can 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 transaction problem is happening because she was unable to open a connection to oracle . If you are using oracle 10g , as you configured in hibernate.cfg.xml

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

---

And you use the 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 may not be configured correctly.

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 the version of the oracle box is. he will work for ojdbc6.jar .

Literature:

• hibernate cfg xml settings for derby oracle and h2
• difference between oracle jdbc driver classes
• configuring jdbc pool with high concurrency

Answer 2

First I want to give you a couple of suggestions.

• Please check the version of oracle driver . If not, then use the correct version of the oracle driver . Since you are using oracle 11, download the ojdbc6.jar file and use it.
• Sometimes ORACLE_HOME not been set. Therefore, when a java application accesses the database, it causes the following error. Therefore, check that the ORACLE_HOME parameter is set or not.

Called: java.sql.SQLException: connection oracle.jdbc.driver.T4CConnection@1a6d7ad6 closed.

3. Out of error

it is determined that this means that the connection was successfully established at some point, but when you try to commit right there, the connection was no longer open. The options you mentioned look like connection pool settings . In the 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:

• hibernate connection pool configuration with c3p0 example
• docs

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

4. Do you use oracle 10g ? if so, then this is normal, otherwise Oracle10gDialect needs 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. Since you are not using the c3p0 pool, then configure the pool by setting the properties file

   accessToUnderlyingConnectionAllowed = true

and then it can work.

The default value is false, this is a potentially dangerous operation and incorrect programs can do harmful things. (close the main one or continue to use it when the protected connection is already closed). Be careful and should only be used when you need direct access to a specific driver. Extensions

7. In server.xml please change

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

initialSize=10 - the number of connections that will be established when creating the connection pool.

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

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

The maxIdle attribute maxIdle bit more complicated. It behaves differently depending on whether the sweeper is turned on.

Check out the configuring-jdbc-pool-high-concurrency tutorial for a better understanding of server.xml settings. If you study this tutorial, I hope you can solve it on your own.

Answer 3

you said that I add that this error spawns like once every 2 days. if your database traffic is high, I think you C3P0 to manage connection pools, for example, use C3P0 and manage shared connections, timeouts ... etc.

eg

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

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

and more knowledge here .

Answer 4

I am further exploring and suspecting a scenario that might cause this problem.

Suppose I have a service level transactional method. This method can call different objects at the DAO level, each of which has its own connections.

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

Now another user performs some other operations, and when my transactional method calls the DAO2 operation, it does not detect available connections, causing a problem and leaving the transaction inactive after a timeout.

Could this be a problem?

If so, I found out that there are other options in the Tomcat Jdbc configuration that I clamp:

 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, can the first parameter set to true be the solution?

Answer 5

I ran into a similar problem, after setting removeAbandonedTimeout made it work. This is what my configuration looks like.

  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: Previously, my removeAbandonedTimeout was set to 60. But some of my requests take more than 60 seconds to complete, so during the execution of the request, the connection is closed by the pool when an abandoned timeout is reached. After executing the request, the spring transaction tries to commit this connection, which is not working because the connection is already closed.

At the moment, I have increased removeAbandonedTimeout to 180, while we are trying to optimize a query that takes longer.