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 [EMAIL PROTECTED]
listing 2 reviews
GetConnection returning [EMAIL PROTECTED]
GetConnection returning [EMAIL PROTECTED]
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 [EMAIL PROTECTED]
listing 2 reviews
GetConnection returning [EMAIL PROTECTED]
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
