commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Emerson Cargnin <emer...@tre-sc.gov.br>
Subject Re: DBCP Pooling question
Date Wed, 03 Dec 2003 16:36:53 GMT
I don't have access to tomcat server.xml file, how do I set a pool in a 
programatic way?

brendan.richards@draftlondon.com 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 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
> 
> 
> 


-- 
Emerson Cargnin
Analista de Sistemas
Setor de Desenvolvimento de Sistemas - TRE-SC
tel : (048) - 251-3700 - Ramal 3181


---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message