5 Replies Latest reply on Jul 17, 2003 5:23 AM by kar2000

    Leaking Connection pool

    Sven Welte Newbie

      The problem:
      I'm running out of connections to my database
      The software:
      JBoss 2.4.3 jdk1.3@win2k, DBMS:PostgreSQL 7.1 (later DB2)
      The code:
      class MySessionBean extends .... {
      .
      .
      public Connection getConnection () {
      Context context = new javax.naming.InitialContext();
      Object object = context.lookup("java:/DefaultDS");
      DataSource ds = (DataSource) PortableRemoteObject.narrow(object, DataSource.class);
      return ds.getConnection ();
      }

      public void methodA () {
      Connection conn = null;
      try {
      conn = getConnection ();
      Statement stmt = conn.createStatement();
      **BB**
      ResultSet rs = stmt.executeQuery("SELECT * FROM BlahBlah");
      }
      catch (Exception ex) {
      throw new EJBException(ex);
      }
      finally {
      try {
      if (conn != null) {
      conn.close();
      System.out.println("Closed DBConnection");
      }
      }
      catch (Exception ex) {
      throw new EJBException(ex);
      }
      }
      Calling methodA 100 times works as expected and JBoss correctly is at [0/1/30] connections at the end.
      Now replace **BB** with getConnection() and with each call of methodA a connection has leaked.
      In the real world instead of getConnection there would be a call to another seesion bean (which could use getConnection or might call another bean)

      Thats whats in the docs:
      "If you request more than one connection from a DataSource in the context of the same transaction, JBoss will return the same connection every time."

      As far as I understand my code should work because the second getConnection is in the same transaction and is returning the same connection.
      Where is the error: my code, in the docs or jboss ?

      bye
      Sven

        • 1. Re: Leaking Connection pool
          John Moore Newbie

          May be unrelated but make sure you closing the statement and result set.

          • 2. Re: Leaking Connection pool
            Sven Welte Newbie

            John, you are right but closing the connection should close also resultset.
            But this is not the problem because if I don't replace **B** with the second getConnection everything works fine.

            regards
            Sven

            PS: just tried jboss 2.4.4b but it did't help

            • 3. Re: Leaking Connection pool
              David Jencks Master

              I think the docs are wrong, although I am mostly familiar with how it works in 3.0 or 2.4 using ConnectionFactoryLoaders. All the connections you get will be enrolled in the same transaction. If you are using LocalTransaction (i.e. a non xa jdbc driver) you will get different handles to the same underlying physical connection. If you are using an xa driver you will actually be using different underlying connections. In either case, it wouldn't make much sense to return the same Connection handle: you wouldn't want closing one to affect another.

              So... be sure to close every Connection you get. Depending on how bad the jdbc implementation is, you may need to also close every resultset and statement.

              • 4. Re: Leaking Connection pool
                Sven Welte Newbie

                After doing some research I found out that my interpretation of the docs were wrong.
                My understanding was that if I get a second connection from jboss it is the same object and calling close() on the second connection would also close/invalidate my first connection.
                So the docs are right but misleading.

                • 5. Re: Leaking Connection pool
                  kar2000 Newbie

                  Well, I have a doubt in this regard - is there any means of finding in the jboss 3.0.7 server side and not in the Oracle 8 database server side,

                  how many connections are there in the pool,

                  how many connections are in use and

                  how many connections are being returned at a given point of time?