commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From brendan.richa...@draftlondon.com
Subject DBCP Pooling problem
Date Wed, 03 Dec 2003 16:10:29 GMT
I have a weird connection pool error and I hope someone can help me.

platform: linux
webapp server: tomcat 4.1
database: postgresql 7
comons-dbcp 1.1
commons-pool 1.1

My web application contains a number of java beans that each know how to 
save / load themselves to the postgresql database.
there are a number of foreign-key linked tables for various objects
They connect via a dbcp connection pool.



*********** in Server.xml (specific connection details changed)*********

    <Context path="/myDatabase" docBase="myDatabase" debug="0" 
reloadable="true">
     <Resource name="jdbc/myDatabase" auth="Container"
          type="javax.sql.DataSource"/>

<ResourceParams name="jdbc/myDatabase">
  <parameter>
    <name>factory</name>
    <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
  </parameter>
  <parameter>
    <name>driverClassName</name>
    <value>org.postgresql.Driver</value>
  </parameter>
  <parameter>
    <name>url</name>
    <value>jdbc:postgresql://127.0.0.1:5432/myDatabase</value>
  </parameter>
  <parameter>
    <name>username</name>
    <value>me</value>
  </parameter>
  <parameter>
    <name>password</name>
    <value>mypassword</value>
  </parameter>
  <parameter>
    <name>maxActive</name>
    <value>50</value>
  </parameter>
  <parameter>
    <name>maxIdle</name>
    <value>40</value>
  </parameter>
  <parameter>
    <name>maxWait</name>
    <value>3000</value>
  </parameter>
  <parameter>
    <name>removeAbandoned</name>
    <value>true</value>
  </parameter>
  <parameter>
     <name>removeAbandonedTimeout</name>
     <value>120</value>
  </parameter>
   <parameter>
    <name>logAbandoned</name>
    <value>true</value>
  </parameter>
  <parameter>
    <name>timeBetweenEvictionRunsMillis</name>
    <value>1000</value>
  </parameter>
 <parameter>
    <name>numTestsPerEvictionRun</name>
    <value>50</value>
  </parameter>
<parameter>
    <name>minEvictableIdleTimeMillis</name>
    <value>1000</value>
  </parameter>
<parameter>
  <name>testOnBorrow</name>
  <value>true</value>
</parameter>
<parameter>
  <name>validationQuery</name>
  <value>select id from users limit 1</value>
</parameter>


</ResourceParams>

*********** end server.xml snippet*********


All my webapp beans and jsps call a common static function to get a 
database connection.

public static Connection getConnection() throws Exception {
        Context ctx = new InitialContext();
        if(ctx == null ) 
          throw new Exception("DataBase Error - no Context");

        DataSource ds = 
(DataSource)ctx.lookup("java:comp/env/jdbc/myDatabase");

        if (ds == null) {
           throw new Exception("DataBase error - DataSource is null");
        }

        Connection conn = ds.getConnection();
        if (conn==null) {
            throw new Exception("Connection is null");
        }
        Logger.debugLog("GetConnection returning "+conn.toString());
        return conn;
    } 

Where Logger is a simple debug logging class

This all seems to work fine most of the time apart from this case which 
works at first (after webapp reboot) and then starts to fail:

Each bean has a number of functions that get a connection, performs a 
read/write and then releases a connection (with appropriate error catching 
etc). 
One part of my system has a jsp that iterates through a number of objects 
like so:

pseudo code:

<jsp code>
- get a connection (1)
- sql: select db keys of objects to display from a related table
- iterate through keys - while (resultSet.next()) 
        - beanObject.load(key)  - this bean function populates this bean 
with the necessary data for this object
                <bean code>
                     getConnection (2)
                             sql: select data row for this object
                     set object properties 
                     close Connection (2)
                </bean code> 
        - display details for this bean using HTML (object.getValue()) 
- end iterate
- close connection (1)
</jspcode>

The main reason for this design is manageability - all objects handle 
thier own read/write so there's only one interface for changing any 
object's data.
As you can see, the above method involves using two connection objects - 
one to iterate at the jsp level and one to perform the encapsulated bean 
load functionality. 


The error:
This code works fine immediately after a tomcat restart. Then after a 
while it starts throwing a "connection is closed" error after the first 
iteration.

Looking at the string returned by getConnection() gives a clue as to what 
is going on.
when running properly to display two objects, the debugging log looks 
something like this:

start reviewList.jsp 
GetConnection returning org.apache.commons.dbcp.PoolableConnection@6dbdc9
listing 2 reviews
GetConnection returning org.apache.commons.dbcp.PoolableConnection@1579a30
GetConnection returning org.apache.commons.dbcp.PoolableConnection@1579a30
finish review list.jsp

This log clearly shows two different PoolableConnection objects being 
created - the first to iterate from the jsp and the second is called and 
closed twice by the bean to retrieve data.


after a short while (~= 5 minutes) this happens:

reviewList.jsp film 3
GetConnection returning org.apache.commons.dbcp.PoolableConnection@6dbdc9
listing 2 reviews
GetConnection returning org.apache.commons.dbcp.PoolableConnection@6dbdc9
reviewList error: Connection is closed.  Operation is not permitted.

What seems to be happening here is that when the bean code executes, the 
connection pool passes the same connection object as is already being used 
to iterate through record keys from the jsp. 
the bean correctly closes the connection its been given so that when 
execution checks for the next while(resultSet.next()) iteration it's 
trying to work with a closed connection. 


Does anyone have any ideas on why the DBCP system is returning a 
connection object that is already in use?
How does the underlying pool keep track of which connections are available 
and how could this mechanism be compromised by what I'm doing?
Is there any way to debug the current contents of the connection pool (ie 
which connections are currently idle etc.)? 

I've looked carefully through my code searching for missing or extra calls 
to connection.close() but have not found anything. The fact that It all 
works at first makes me think that theres nothing wrong with my code at 
this point but that its something else somewhere that's adversly affecting 
the internal state of my connection pool.

Any ideas gratefully recieved.


Brendan Richards



Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message