Announcement Announcement Module
No announcement yet.
about maximum open cursors exceeded and Spring 3 HibernateTransactionManager Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • about maximum open cursors exceeded and Spring 3 HibernateTransactionManager

    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

           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;
    						return true;

    This is the Service

    		public class SampleDelegate implements SampleService{
            private SampleDao saDao;
    		   return saDao.isPositionOccupied((Integer)seMapStrInt.get(pos.getStorageName()),

    this is the web.xml

    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">
                    <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>
            <bean id="txManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
            <property name="sessionFactory" ref="sessionFactory"/>
        <tx:annotation-driven transaction-manager="txManager"/>

  • #2
    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.


    • #3
      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?


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


        • #5
          Hi Michael,

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


          • #6
            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 .........";
            Last edited by cihan; Nov 28th, 2012, 03:23 AM.