[dbcp] Lessons learned from another connection pool

[Kenneth Corbin <ke...@peak.org>]

I wrote my own connection pool, that I thought was pretty cool, but that was 
before I started looking at jakarta commons products.  Now it looks pretty 
pathetic compared to dbcp, but I did learn a couple things from it that might 
you might not have covered.

The big gotcha was the need to rollback any uncommited transactions before 
returning a connection to the pool.  I didn't do that originally, and sure 
that my best theory as to why we were experiencing unpredictable database 
deadlocks.  Someone released a connection with an uncommitted transaction, I 
stuck it back in the pool with locks still pending, and everyone else piled 
up behind them.  I didn't see anything in the PoolableConnection class that 
looked like you were calling rollback before returning a connection to the 
pool, but it could easily be somewhere else.

An earlier discovery is that when we loose a network connection to an Oracle 
database.  The first symptom is that an attempt to reset the autocommit 
status threw an exception.   That might be something you want to try in the 
validate method if a validation string hasn't been specified.

Featurewise, mine will pool prepared statements accross a close.   As best I 
can tell, dbcp really closes pooled statements when the the user closes the 
connection and they have to be rebuilt the next time a connection is 
allocated from the pool.  I would probably be a nice idea to at least have 
the option to keep the statement pool from one connection allocation to the 
next.


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

Re: [dbcp] Lessons learned from another connection pool

[Dirk Verbeeck <di...@pandora.be>]

When a connection is returned to the pool 
PoolableConnectionFactory.passivateObject is called on it:
http://jakarta.apache.org/commons/dbcp/xref/org/apache/commons/dbcp/PoolableConnectionFactory.html#358

As you can see a rollback is done (if needed) and setAutoCommit is 
also called. If this passivate method fails with an exception then the 
connection is destroyed.

Your third suggestion is interesting, DBCP does indeed closes its 
pooled statements and I agree that keeping a prepared statement pool 
accross a close would improve performance.
You can make an enhancement request for this performance improvement 
in Bugzilla if you want.

Other suggestions are also welcome of course.

Cheers
Dirk


Kenneth Corbin wrote:

> I wrote my own connection pool, that I thought was pretty cool, but that was 
> before I started looking at jakarta commons products.  Now it looks pretty 
> pathetic compared to dbcp, but I did learn a couple things from it that might 
> you might not have covered.
> 
> The big gotcha was the need to rollback any uncommited transactions before 
> returning a connection to the pool.  I didn't do that originally, and sure 
> that my best theory as to why we were experiencing unpredictable database 
> deadlocks.  Someone released a connection with an uncommitted transaction, I 
> stuck it back in the pool with locks still pending, and everyone else piled 
> up behind them.  I didn't see anything in the PoolableConnection class that 
> looked like you were calling rollback before returning a connection to the 
> pool, but it could easily be somewhere else.
> 
> An earlier discovery is that when we loose a network connection to an Oracle 
> database.  The first symptom is that an attempt to reset the autocommit 
> status threw an exception.   That might be something you want to try in the 
> validate method if a validation string hasn't been specified.
> 
> Featurewise, mine will pool prepared statements accross a close.   As best I 
> can tell, dbcp really closes pooled statements when the the user closes the 
> connection and they have to be rebuilt the next time a connection is 
> allocated from the pool.  I would probably be a nice idea to at least have 
> the option to keep the statement pool from one connection allocation to the 
> next.




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