Re: Production use of Connection Pooling

[Frank Lawlor <fl...@athensgroup.com>]

Jason Lea stated:

 

*       If you have a bad connection and you think it shouldn't be
reused you can call pool.invalidateObject(Object). This also decrements
the active count.

 

Thnaks for the info Jason.  I'll note this in the paper.

 

However, a couple of points.

 

1)    The connection-using code only has the connection, not the pool,
reference.  This means adding code for mapping the connection to the
pool and if the application has more than one pool this gets a bit more
complex.  This is information which the pooling code has and already
uses on a normal close().  It would be better if the pooling code
provided some form of connection.invalidateObject() which does the
mapping.



2)    You also state that the pooling code does a reallyColse() when you
return a connection, which I don't believe is true.

 

Thanks again for the good comments,

 

  -- Frank

Re: Production use of Connection Pooling

[Jason Lea <ja...@kumachan.net.nz>]

Frank Lawlor wrote:
> Jason Lea stated:
> 
>  
> 
> *       If you have a bad connection and you think it shouldn't be
> reused you can call pool.invalidateObject(Object). This also decrements
> the active count.
> 
>  
> 
> Thnaks for the info Jason.  I'll note this in the paper.
> 
>  
> 
> However, a couple of points.
> 
>  
> 
> 1)    The connection-using code only has the connection, not the pool,
> reference.  This means adding code for mapping the connection to the
> pool and if the application has more than one pool this gets a bit more
> complex.  This is information which the pooling code has and already
> uses on a normal close().  It would be better if the pooling code
> provided some form of connection.invalidateObject() which does the
> mapping.

True, but you have to get the connection from somewhere...

What I have been doing is setting up a singleton that I use for getting 
and closing connections.  I call my global.getConnection() method to get 
a connection, and global.closeConnection(conn) to close it.  I also have 
global.initPool to create the pool and it keeps the reference to it.  I 
can then call global.invalidateConnection(conn) if I want to invalidate 
it.  I don't need a reference to the pool in this case.  Of course this 
only deals with one pool, but I'm sure something else could be worked 
out to deal with more than one pool.

The other benefit is that I can put a hashcode of the connection and 
date into a hashmap when getConnection is called, remove it when close & 
invalidate are called, so that at anytime I can see what connections I 
have open and for how long - useful for tracking down lingering connections.

Of course if you use testOnReturn then the DBCP could do a check when 
you close the connection and discard if necessary.  Your code doesn't 
need to call invalidateObject().

There are some bugs where some resources may not be released and I have 
logged a bug related to that.

> 2)    You also state that the pooling code does a reallyColse() when you
> return a connection, which I don't believe is true.

The connection doesn't call reallyClose() when the connection is 
returned (this would defeat the purpose of DBCP :))

Here is the process:

1. App gets Connection from GenericObjectPool's PoolableConnectionFactory
2. This provides a PoolableConnection (which extends DelegateConnection)
2. App uses Connection then calls close()
3. PoolableConnection overrides DelegateConnection's close() (because 
DelegateConnection.close() actually closes the underlying connection), 
and uses GenericObjectPool.returnObject() instead.
4. GenericObjectPool.returnObject() then calls 
PoolableConnectionFactory.passivate() on the connection
5. PoolableConnectionFactory.passivate() calls 
DelegateConnection.passivate() which closes all of the 
DelegateStatements and DelegateResultSets that belong to the 
DelegateConnection.

If the pool decides to close the underlying connection (usually due to 
validation failure, or eviction of connections that are idle for too 
long, or the pool is closed) the pool will call 
PoolingObjectFactory.destroyObject() on the connection.  destroyObject() 
calls PoolableConnection.reallyClose(), this closes the underlying 
connection.

The main point is that the connection remains open while it is in the pool.

The bugs I mentioned refer to the fact that DBCP does not always 
passivate the connection when returning the connection to the pool.  If 
testOnReturn is used and validation fails, 
PoolableConnectionFactory.destroyObject() is called straight away 
(without passivate() being called), so the underlying connection is 
closed but any DelegateStatements and DelegateResultSets that were left 
open will not have been closed.  This is also a problem with 
invalidateObject() which just calls destoryObject().

There are certainly improvements that could be made.  I would like to 
have logging built into DBCP so I could see if it closed any Statements 
or ResutlSets that I had left open - this would help me tidy-up my code. 
  And help confirm when it is really closing connections etc.  Also I 
would like to see some stats on connection usage - this was the main 
reason for my global.getConnection() & global.closeConnection usage. 
But it also makes sense I think to have an application level interface 
to the database connection so that if I decide to switch to another 
pooling implementation I don't have to go through all of my code to add it.

> 
> Thanks again for the good comments,
> 
>  
> 
>   -- Frank
> 
> 


-- 
Jason Lea


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