[dbcp]: Oracle "too many open cursors error"

[Bill Schneider <bs...@vecna.com>]

I've been using the dbcp BasicDataSourceFactory with Tomcat, as a
replacement for Tyrex brokenness.  I ran into some trouble with it under
heavy load: after running it for a while I end up with an Oracle "too many
open cursors error".

The problem is a combination of dbcp code and application code.  The
application code was written with the (possibly incorrect) assumption that
calling conn.close() resets the connection, closing any associated
statements, such that the connection is good-as-new if it's recycled and
reassigned.

This worked fine until bringing in DBCP, because the statements are


to being able to call conn.close(),


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: [dbcp]: Oracle "too many open cursors error"

[Glenn Nielsen <gl...@voyager.apg.more.net>]

Last week I committed some changes to DBCP that made sure that all
ResultSet's and Statement's were closed on a Connection close.

You might try the latest nightly build or build from CVS.

Regards,

Glenn

Bill Schneider wrote:
> 
> I've been using the dbcp BasicDataSourceFactory with Tomcat, as a
> replacement for Tyrex brokenness.  I ran into some trouble with it under
> heavy load: after running it for a while I end up with an Oracle "too many
> open cursors error".
> 
> The problem is a combination of dbcp code and application code.  The
> application code was written with the (possibly incorrect) assumption that
> calling conn.close() resets the connection, closing any associated
> statements, such that the connection is good-as-new if it's recycled and
> reassigned.
> 
> This worked fine until bringing in DBCP, because the statements are
> 
> to being able to call conn.close(),
> 
> --
> To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
> For additional commands, e-mail: <ma...@jakarta.apache.org>

-- 
----------------------------------------------------------------------
Glenn Nielsen             glenn@more.net | /* Spelin donut madder    |
MOREnet System Programming               |  * if iz ina coment.      |
Missouri Research and Education Network  |  */                       |
----------------------------------------------------------------------

--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

RE: [dbcp]: Oracle "too many open cursors error" fix?

[JD Evora <jd...@saadian.com>]

> >
> > I've been using the dbcp BasicDataSourceFactory with Tomcat,
> > as a replacement for Tyrex brokenness.  I ran into some
> > trouble with it under heavy load: after running it for a
> > while I end up with an Oracle "too many open cursors error".
>
> This is a common problem with any connection pooling code.
>
> The problem has to do with ResultSets that are not closed.
>
> Conn.close() is used to return a connection to the pool.
> Perhaps the correct approach is to cache references to all
> ResultSets and such so that we can auto close them to give
> the same affect.
>
> This is something I've been meaning to add to Avalon's
> DataSource code, and I just never have gotten around to it.
>


 Seems be easy to fix. Or am I too optimistic??

 In PoolableConnection,  overload createStatement() to add the statement to
an ArrayList and in
 PoolableConnection.close() go through the ArrayList and close all
statements before return the connection to the pool.

 Something like:

  //New protected ArrayList with all the statements
  protected ArrayList _statements= null;


  // change the constructor
  public PoolableConnection(Connection conn, ObjectPool pool) {
        super(conn);
        _pool = pool;
        _statements = new ArrayList();
  }

  // Override createStatement()
  public Statement createStatement() throws SQLException {
    Statement statement = super.createStatement();
    _statements.add(statement);
    return statement;
  }

  // Add some code to close()
  public void close() throws SQLException {
   Iterator it =_statements.iterator();
   while (it.hasNext()){
     ((Statement) it.next()).close();
   }
   _statements.clear();

   //Actual close() code here.
  }



  Do you think that this should do the trick?

  Best regards
    JD Evora




--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

RE: [dbcp]: Oracle "too many open cursors error"

[Berin Loritsch <bl...@apache.org>]

> From: Bill Schneider [mailto:bschneider@vecna.com] 
> 
> 
> I've been using the dbcp BasicDataSourceFactory with Tomcat, 
> as a replacement for Tyrex brokenness.  I ran into some 
> trouble with it under heavy load: after running it for a 
> while I end up with an Oracle "too many open cursors error".

This is a common problem with any connection pooling code.

The problem has to do with ResultSets that are not closed.

Conn.close() is used to return a connection to the pool.
Perhaps the correct approach is to cache references to all
ResultSets and such so that we can auto close them to give
the same affect.

This is something I've been meaning to add to Avalon's
DataSource code, and I just never have gotten around to it.


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>