Connection Pool Not Reusing Connections

[Thaddeus Gean Cowan Thompson <tc...@cs.uoregon.edu>]

Tomcat Users -

I am trying to implement connection pooling.  I seem to be able to get a
connection from the pool without any problems, by following the steps
in the Tomcat docs, but it does not seem to reuse any of the connections
from the pool.  The reason that I believe this is because when I grab a
connection from the pool I do a toString on it, resulting in someting like
'org.gjt.mm.mysql.jdbc2.Connection@60bf50', but the same toString never
appears twice.  In addition after using (creating and closing) about
3,000 connections in rappid succession I start revieving null pointers
when I try to obtain a connection from the pool.

I have edited my web.xml as follows.
<resource-ref>
  <description>
    Reference to the Connection Pool defined in server.xml
  </description>
  <res-ref-name>jdbc/customer</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

I have edited my server.xml as follows.
<Context path="/grocery_site"
         reloadable="true"
         debug="0"
         docBase="grocery_site">
  <Resource name="jdbc/customer"
            auth="Container"
            type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/customer">
    <parameter>
      <name>driverClassName</name>
      <value>org.gjt.mm.mysql.Driver</value>
    </parameter>
    <parameter>
      <name>driverName</name>
      <value>jdbc:mysql://localhost:3306/grocery_customer</value>
    </parameter>
    <parameter>
      <name>user</name>
      <value>grocery</value>
    </parameter>
    <parameter>
      <name>password</name>
      <value>grocery</value>
    </parameter>
  </ResourceParams>
  <Resource name="jdbc/product"
            auth="Container"
            type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/product">
    <parameter>
      <name>driverClassName</name>
      <value>org.gjt.mm.mysql.Driver</value>
    </parameter>
    <parameter>
      <name>driverName</name>
      <value>jdbc:mysql://localhost:3306/grocery_product</value>
    </parameter>
    <parameter>
      <name>user</name>
      <value>grocery</value>
    </parameter>
    <parameter>
      <name>password</name>
      <value>grocery</value>
    </parameter>
  </ResourceParams>
</Context>

I have tried the addion of the maxIdle, maxActive, and removeAbandoned
parameters to my configuration, but they seemed to have no effect.

The utility method that is used to get a connection from the pool is as
follows.  The gc stuff is ran to clean up the Connections that the pool
seams to be discarding rather then reusing (this is a hack untill I get
this problem solved).
  private static Connection getPooledConnection(String data_source){
    Connection connection = null;
    try{
      Context context = (Context) new InitialContext().lookup("java:comp/env");
      DataSource ds = ((DataSource) context.lookup(data_source));
      connection = ds.getConnection();
    }
    catch(Exception e){
      System.err.println("DatabaseUtil.getPooledConnection(): " +
			 "Unable to retrive connection from pool. " +
			 "Running System.runFinalization() and System.gc()");
      System.runFinalization();
      System.gc();
    }
    return connection;
  }

In reading some docs I found that I should call ResultSet.close(),
Statement.close(), and Connection.close() to ensure that the connections
get reused.  So I have check all my code over for those type of leaks, but
had no success.  I should mention that all the methods in a single Object
instance share a Connection between methods (but use a fresh Statement and
ResultSet for each query/update), and I close the Connection in the
Object's finalize method as follows.
  protected void finalize() throws Throwable{
    System.out.print("Closing " + connection + "...");
    connection.close();
    System.out.println(connection);
  }

When executed the method prints somthing like
Closing org.gjt.mm.mysql.jdbc2.Connection@60bf50 ...
org.gjt.mm.mysql.jdbc2.Connection@60bf50


I should also mention that even though an Object's method may share a
Connection, the Object's static methods create and close thier own
Connections.  This means that two connections may be checked out of the
pool.  An archive message said this could mess up the pool, but I have a
hard time beleiving this (what if two separate Objects both need access to
the database concurrently?).

The software I am using for development is as follows.
tomcat   4.0.3
mm.mysql 2.0.14
j2se     1.4.0_01
j2ee     1.3.1

I have ran out of ideas as to what can be causing this behavior, so any
pointers leading to an answer would be greatly appreciated.  Thanks.

 - Thadd




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