commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bill Schneider" <bschnei...@vecna.com>
Subject [dbcp]: Oracle "too many open cursors error" fix?
Date Tue, 21 May 2002 16:17:35 GMT
I made a patch like this on my own (releasing statements in the connection's
passivate() method), but it caused the dbcp unit tests to fail.  The problem
was that dbcp might use a statement pool (an object pool for prepared
statements) which don't like to have the underlying statements closed.  When
BasicDataSource is in play, though, it doesn't.

The key distinction, I think, is that PoolableConnection shouldn't close
statements when PoolingConnection (with statement pool) is in use, but
should whenever it isn't.

At the very bottom of this message is a patch that fixes my problem and
makes unit tests pass.

-- Bill

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

------------------------------------------------------------------

Index: src/java/org/apache/commons/dbcp/PoolableConnection.java
===================================================================
RCS file:
/home/cvspublic/jakarta-commons/dbcp/src/java/org/apache/commons/dbcp/Poolab
leConnection.java,v
retrieving revision 1.3
diff -u -r1.3 PoolableConnection.java
--- src/java/org/apache/commons/dbcp/PoolableConnection.java 16 May 2002
21:25:38 -0000 1.3
+++ src/java/org/apache/commons/dbcp/PoolableConnection.java 21 May 2002
16:01:26 -0000
@@ -62,9 +62,16 @@
 package org.apache.commons.dbcp;

 import java.sql.Connection;
+import java.sql.Statement;
+import java.sql.PreparedStatement;
+import java.sql.CallableStatement;
 import java.sql.SQLException;
+import java.util.List;
+import java.util.ArrayList;
+import java.util.Iterator;
 import org.apache.commons.pool.ObjectPool;

+
 /**
  * A delegating connection that, rather than closing the underlying
  * connection, returns itself to an {@link ObjectPool} when
@@ -79,6 +86,9 @@
     /** The pool to which I should return. */
     protected ObjectPool _pool = null;

+    private List _statements = null;
+    private boolean _shouldClose = true;
+
     /**
      *
      * @param conn my underlying connection
@@ -87,6 +97,32 @@
     public PoolableConnection(Connection conn, ObjectPool pool) {
         super(conn);
         _pool = pool;
+ _statements = new ArrayList();
+ if (_conn instanceof PoolingConnection) {
+     _shouldClose = false;
+ }
+    }
+
+    private synchronized void closeAllStatements() {
+ // return all statements
+ for (Iterator it = _statements.iterator(); it.hasNext(); ) {
+     Statement stmt = (Statement)it.next();
+     System.out.println(stmt.getClass());
+     try {
+  stmt.close();
+     } catch (SQLException e) {
+  // maybe the driver doesn't want to re-close a closed
+  // connection
+     }
+ }
+    }
+
+    public void passivate() {
+ super.passivate();
+ if (_shouldClose) {
+     closeAllStatements();
+ }
+ _statements.clear();
     }

     /**
@@ -125,5 +161,58 @@
         _conn.close();
     }

+    public Statement createStatement() throws SQLException {
+ Statement s = super.createStatement();
+ _statements.add(s);
+ return s;
+    }
+
+    public Statement createStatement(int resultSetType,
+         int resultSetConcurrency)
+        throws SQLException {
+ Statement s = super.createStatement(resultSetType,
+                                            resultSetConcurrency);
+        _statements.add(s);
+        return s;
+    }
+
+    public CallableStatement prepareCall(String sql) throws SQLException {
+        CallableStatement s = super.prepareCall(sql);
+        _statements.add(s);
+ return s;
+    }
+
+    public CallableStatement prepareCall(String sql,
+                                         int resultSetType,
+                                         int resultSetConcurrency)
+    throws SQLException {
+        CallableStatement s = super.prepareCall(sql,
+                                                resultSetType,
+                                                resultSetConcurrency);
+        _statements.add(s);
+ return s;
+    }
+
+    public PreparedStatement prepareStatement(String sql) throws
SQLException {
+ PreparedStatement s = super.prepareStatement(sql);
+        _statements.add(s);
+        return s;
+    }
+
+    public PreparedStatement prepareStatement(String sql,
+                                              int resultSetType,
+                                              int resultSetConcurrency)
+    throws SQLException {
+ PreparedStatement s = super.prepareStatement(sql,
+                                                     resultSetType,
+                                                     resultSetConcurrency);
+        _statements.add(s);
+        return s;
+    }
+
+    private Statement addStatement(Statement s) {
+ _statements.add(s);
+ return s;
+    }
 }




--
To unsubscribe, e-mail:   <mailto:commons-dev-unsubscribe@jakarta.apache.org>
For additional commands, e-mail: <mailto:commons-dev-help@jakarta.apache.org>


Mime
View raw message