4 Replies Latest reply on Jul 17, 2003 4:51 AM by kar2000

    connection pooling

    David Sills Newbie

      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