connection pooling
I'm having a problem that has been discussed here before, on a couple of occasions, but never actually brought to a conclusion, as far as I can tell. The discussions just stop. I have read all these posts, and been over my code line by line.
I'm working with
Windows NT 4 (sp 6a)
MySQL (mysql-3.23.46a-win.zip)
JDBC driver (mm.mysql-2.0.8)
JBoss (2.4.4.2001-12-29 02:04:40 PST) (bundled with Tomcat 4)
When I use the datasource to enable my session beans to communicate with the database, the connections are not released unless I set GC to very low levels, and even then it's suspicious.
I have been religious in making sure that everything that has a close() method has it called, that every set of calls is in a triple-try stack:
try
{
// create connection
try
{
// create statement
try
{
// create result set
...
} finally {
// close result set
}} finally {
// close statement
}} finally {
// close connection
}
... well, you get the idea. I even explicitly set each variable to null after close():
if (st != null) st.close();
st = null;
I am using the configuration recommended on another post, which has worked very well so far:
DefaultDS
org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
jdbc:mysql://localhost/jboss
false
1200000
10
1
false
false
true
120000
1800000
false
true
1.0
It is completely trivial, however, to overload the pool: 10 requests and it's over! Since I have logging enabled, here's the result:
[08:20:19,590,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@43da1b' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@88e6c'.
[08:20:22,834,DefaultDS] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[08:20:22,864,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@5349e2' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@6c5356'.
[08:20:24,306,DefaultDS] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[08:20:24,337,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@e4fd7' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@7ba502'.
[08:20:25,719,DefaultDS] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[08:20:25,749,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@58213c' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@9dc35'.
[08:20:27,010,DefaultDS] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[08:20:27,040,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@6752c9' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@3c33b7'.
[08:20:28,302,DefaultDS] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[08:20:28,332,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@82823' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@69f5cc'.
[08:20:29,564,DefaultDS] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[08:20:29,594,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@5c2843' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@1ce47a'.
[08:20:29,704,LRUEnterpriseContextCachePolicy] Resized cache for bean CDBean: old capacity = 1000, new capacity = 50
[08:20:30,786,DefaultDS] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[08:20:30,816,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@ef177' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@1480b8'.
[08:20:32,098,DefaultDS] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[08:20:32,128,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@38d09d' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@626e52'.
[08:20:33,380,DefaultDS] org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl created new Connection (org.gjt.mm.mysql.jdbc2.Connection) with XAResource org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl and XAConnection org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl.
[08:20:33,410,DefaultDS] Resource 'org.jboss.pool.jdbc.xa.wrapper.XAResourceImpl@43358b' enlisted for 'org.jboss.pool.jdbc.xa.wrapper.XAConnectionImpl@54824'.
So much for the Fast-Lane Reader pattern: that'll bring 'em up short!
It is worth noting that I am using non-transactional tables for these reads, and that they are strictly reads, that is, no inserts or updates or deletes. Must I use a transactional driver wrapper if using the datasource? (I'm using the same non-transactional tables for the entity beans, but obviously would like to move to transactional ones in the future.) I see no other advise, including in the $$ manual, which I was finally able to obtain (and for which, thanks!).
Before I try running the same app on WebLogic or trying to install another database in an attempt to isolate the problem, does anyone have any ideas?
BTW, JBoss is really very impressive so far, and I have to say that one of the really impressive things about it has been the quality of help I consistently see on these forums. I'm really hopin' this continues (at least once more!).
Thanks!
David Sills
