about maximum open cursors exceeded and Spring 3 HibernateTransactionManager

[sse.michael]

Hi all,

I have an application using Spring 3, Hibernate 3, Tomcat 6 and Oracle.

All services and Daos are managered by spring IoC. All services are annotated by `@Transactional`. And an `OpenSessionInViewFilter` is defined in the web.xml
The application has a file upload function. it parses a excel spreadsheet and save it to the database.
before persistent, it verifies if each cell value is valid using the loop. so if there are 1000 rows, it will loop 1000 times.
in the loop there is a service dao method, which check's database by creating a query. when this method is called 297 times , the system got
ORA-01000: maximum open cursors exceeded
This makes sense because the oracle database has open_cursor=300;

I have added the `<prop key="hibernate.statement_cache.size">0</prop>`, but it did not work
i also added `poolPreparedStatements="false"` in the tomcat 6 context.xml datasource definition.

The Spring 3 localtransactionmanager supports plain hiberante code style, so i don't need use hiberantetemplate.
but is there a preparedstatemetn leak when using plain hbierante code style in Spring 3, or i missed something?
How can i solve this problem?

this is the Dao method

   @Repository
       public class SampleDaoImpl mplements SampleDao {
         ....
         ....
         public boolean isPositionOccupied(int se, int pos1, int pos2, int pos3,int pos4) {
					// TODO Auto-generated method stub
					session= sessionFactory.getCurrentSession();
			
					String sql2 = "SELECT count(*) from table1 t1 and table2 t2 where .........";
			
					
					Query q = session.createSQLQuery(sql2);
					q.setParameter("parameter1", se);
					q.setParameter("parameter2", pos1);
					.......
					
					Object obj = q.uniqueResult();
					
					if (Integer.valueOf(obj.toString())==0)
						return false;
					else
						return true;

				}
  
          }

This is the Service

  @Service
		@Transactional
		public class SampleDelegate implements SampleService{
		
		    @Autowired
        private SampleDao saDao;
		
		   return saDao.isPositionOccupied((Integer)seMapStrInt.get(pos.getStorageName()),
													   pos.getPosition1(),
													   pos.getPosition2(),
													   pos.getPosition3(),
													   pos.getPosition4());
		
		
		}

this is the web.xml

		....
		....
			<filter>
	    <filter-name>openSessionViewFilter</filter-name>
	    <filter-class>org.springframework.orm.hibernate3.support.OpenSessionInViewFilter</filter-class>
	    <init-param>
				<param-name>singleSession</param-name>
				<param-value>true</param-value>
			</init-param>
			<init-param>
				<param-name>sessionFactoryBeanName</param-name>
				<param-value>sessionFactory</param-value>
	   </init-param>
	 </filter>
	 
	 
	  <filter-mapping>
	    <filter-name>openSessionViewFilter</filter-name>
	    <url-pattern>/*</url-pattern>
	 </filter-mapping>
	 
	 ...
	 ...

this is the hibernate-context.xml

	 	<jee:jndi-lookup id="dataSource" jndi-name="java:comp/env/jdbc/datasource" resource-ref="true" />

    
    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="hibernateProperties">
            <props>           
                <prop key="hibernate.connection.datasource">java:comp/env/jdbc/ktbDB</prop>
                <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
                <prop key="hibernate.show_sql">false</prop>
                <prop key="hibernate.statement_cache.size">0</prop>
            </props>
        </property>
        
        ....
        ....
        
        <bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="sessionFactory"/>
    </bean>
     
    
    <tx:annotation-driven transaction-manager="txManager"/>

[Marten Deinum]

One question why?!... Why not simply do all that stuff in a single transaction because what you now have is opening a connection/session/transaction for each call not very efficient nor performant. Also you get a long from the count and you do some additional conversions again performance will not be improved by this.

However your problem in general indicates something amiss with your transactional setup. One thing I notice is that you assign the session to a variable which doesn't seem to be scoped to the method but as an instance variable.

[sse.michael]

well, i cannot change much about the code. it's a legacy system and we are transforming it using spring.
We replaced the code using JDBC and spring jdbc template and it worked just fine.~

i suspect that, because the opensessioninviewfilter is used and single session is set to true, the session is closed only when the request thread is over.
in this case of file upload , parse and data persist, where a loop of 1000 queries may occur, the session is closed after the thread is over. So the new queries were built up and released till the controller returned, thus resulted in the open cursor over flow problem.

because spring jdbc template closes the prepared statement every time the method returns , queries are not built up and the program got through.

what do you think?

[dominic_veit]

I think , refactor it, you gotta close your statements and result sets.

[petertn]

Hi Michael,

Did you happen to figure out a solution or cause of this error?

[cihan]

I had a similar problem with almost the same configuration in a project:

Spring 3, Hibernate 3, JBOSS 7 and Oracle

When I converted all my native queries into prepared statements, I stopped getting:
'ORA-01000: maximum open cursors exceeded' error. The number of cursors was still increasing till some point but never reached the maximum then on.

What I noticed later was, the latter increase was because of the queries which has 'WHERE value IN' clause in it.
For instance, a cursor is opened for:
.... where value in (?,?)
and another cursor is opened for:
... where value in (?,?,?)

After a while, when all the possible 'WHERE value IN' queries (by means of the number of parameters in brackets) are consumed, the increase in the number of cursors stopped.

I thought this issue may be relevant, Michael, as I can't see the details in your query:

String sql2 = "SELECT count(*) from table1 t1 and table2 t2 where .........";