[EMAIL PROTECTED] wrote:
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
-- Emerson Cargnin Analista de Sistemas Setor de Desenvolvimento de Sistemas - TRE-SC tel : (048) - 251-3700 - Ramal 3181
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
