1 2 Previous Next 20 Replies Latest reply on Oct 17, 2001 6:35 AM by paulnicklin

    Pooled Connections Not Releasing

    cbrak

      I am using JNDI lookups to get my database connections from MySQL in the following manner:

      InitialContext initialContext = new InitialContext();
      DataSource ds = (DataSource)initialContext.lookup("java:/mySQLDB");
      Connection conn = ds.getConnection("xxx","xxx");

      This all works fine and I'm getting data. The problem is that when trying to close connections, I find the connections not being released from MySQL Administrator. After turning on logging for my connection pool in jboss.jcml, it looks as if all connections are being released just fine. I then turn on blocking as well and find that that application deadlocks after a while once all connections are taken and not given back to the pool. Is there a problem with the way that JBOSS releases pooled connections?

        • 1. Re: Pooled Connections Not Releasing
          cbrak

          I just wanted to clarify one thing that might have been unclear in my previous paragraph. When I stated that pooled connections are not releasing, what I meant is that when a client closes the connection (conn.close()) the connections are not releasing from the database. Just wanted to be clear.

          • 2. Re: Pooled Connections Not Releasing
            p_d_austin

            Welcome to the world of connection pooling. When you close a connection obtained from a pool you do not actually close the connection to the database all you are doing is returning the connection back to the pool so it can be reused. So in the administration for you database the connections will always be there.

            The typical reason for the connection pools blocking that I have seen is that the user's application code is not calling conn.close() in the case of an exception being thrown by the sql. You must always have a finally block to close the connection in case of an error.

            Connection con = null;
            PreparedStatment s = null;
            ResultSet rs = null;

            try {
            // get connection from pool and execute sql statements assigning them to the variables above
            } finally {
            try {
            rs.close();
            } catch (Exception e) {
            // log the exception here
            }

            try {
            s.close();
            } catch (Exception e) {
            // log the exception here
            }
            try {
            con.close();
            } catch (Exception e) {
            // log the exception here
            }
            }

            This type of pattern of code should be used all the time for any jdbc calls to make sure all the resources are cleared properly.

            Hope this helps,
            Paul

            • 3. Re: Pooled Connections Not Releasing
              cbrak

              oh no, no, no...ok, I'm bad at explaining the situation when I want an answer to it like right away. Obviously a connection pool opens so many connections to the database and those connections always stay open (up to the minimum count that is)...my issue is that connections are not being given back to the pool from the session bean even after a client conn.close(). This is evident when turning on blocking and watching the application come to a deadlock once the max number of connections is reached. Any ideas?

              • 4. Re: Pooled Connections Not Releasing
                p_d_austin

                Can you post a copy of the code?

                • 5. Re: Pooled Connections Not Releasing
                  cbrak

                  Sure...here is a snippet of a DAO object that has been instantiated from a session bean:

                  try{

                  InitialContext initialContext = new InitialContext();
                  DataSource ds = (DataSource)initialContext.lookup("java:/mySQLDB");
                  Connection conn = ds.getConnection("xxx","xxx");

                  }catch(Exception e){

                  cat.error("MailDAO: Not able to get connection: "+e);

                  }
                  if (conn == null || !(conn instanceof Connection))
                  throw new PersistenceException("JDBC connection not available");


                  try
                  {

                  pstmt = conn.prepareStatement(CHECKMAIL);

                  pstmt.setString(1,user);

                  rset = pstmt.executeQuery();

                  rset.next();

                  currentMessageCount = (Integer)rset.getObject("Current_Message_Count");

                  }
                  catch (SQLException e)
                  {
                  cat.error("MailDAO:"+e);
                  }
                  finally
                  {

                  if (pstmt != null)
                  {
                  try
                  {
                  pstmt.close();
                  rset.close();
                  conn.close();

                  }
                  catch (SQLException e)
                  {
                  cat.error("MailDAO: Error closing connection "+e);

                  }
                  }
                  }

                  Here also is my pool configuration in jboss.jcml:

                  <!-- ==================================================================== -->
                  <!-- JDBC -->
                  <!-- ==================================================================== -->



                  org.gjt.mm.mysql.Driver





                  org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
                  mySQLDB

                  jdbc:mysql://localhost/realpeople



                  12
                  10
                  20000
                  false
                  2000
                  true
                  20000
                  false



                  I have also tried moving around the conn.close() to the passivate and remove methods of the stateless session bean. Sometimes it works just fine, but when I load test it while blocking is turned on, the application deadlocks. If I turn blocking off, I get sporadic errors in my counsel stating that a connection could not be established. I attribute these messages to timeouts when trying to get a connection and there are non currently available. When I try the same load test with a connection pool of my own, outside of an EJB(s), it works just fine without error. If there is any other info I can provide you, please let me know.

                  • 6. Re: Pooled Connections Not Releasing
                    thedug

                    We are seeing real wierd stuff where we have connection remain open for days even when the application server that oracle is indicating as the owner of the connection is physically turned off!

                    I am not sure what to make of this...

                    • 7. Re: Pooled Connections Not Releasing
                      p_d_austin

                      So your code has just shown that you are only closing the connection if the prepared statement is created correctly and in the case of any errors in the closing of the resultset or prepared stament you are not performing the closing of the connection. Therefore the connection will not be closed if there are errors.

                      You should use the following structure.

                      try {
                      rs.close();
                      } catch (SQLException sqle) {
                      // log.error(sqle);
                      } catch (NullPointerException npe) {
                      }

                      try {
                      s.close();
                      } catch (SQLException sqle) {
                      // log.error(sqle);
                      } catch (NullPointerException npe) {
                      }

                      try {
                      con.close();
                      } catch (SQLException sqle) {
                      // log.error(sqle);
                      } catch (NullPointerException npe) {
                      }

                      • 8. Re: Pooled Connections Not Releasing
                        neilmcc

                        Hi - I'm doing something very similar with mysql but am getting some weird behaviour. Where bloking=true, I'd expect the pool to expand to the MaxSize and then the client should block. This happens fine.

                        But when the client releases connections back to the pool (in a finally block like the one below), the pool blocks staing that it is full and awaiting a connection to be checked back in. It seems that the connections are not being released correctly back to the pool - any thoughts?

                        
                         Connection connection = null;
                         PreparedStatement statement = null;
                         try {
                         System.out.println("Getting connection...");
                         connection = getConnection();
                         System.out.println("Preparing call...");
                         statement = connection.prepareStatement("insert into mailing_list(id, mailing_id, customer_id, email_address, address_active) values (null,?,?,?,?);");
                         statement.setString(1, mailingList);
                         if (customer==null) {
                         statement.setNull(2, java.sql.Types.VARCHAR); // null cust id
                         } else {
                         System.out.println("Customer id in new (inserted) mailing entry is : " + customer.getId());
                         statement.setLong(2, customer.getId());
                         }
                         if (emailAddress==null) {
                         statement.setString(3, customer.getEmailAddress());
                         } else {
                         statement.setString(3, emailAddress);
                         }
                         statement.setString(4, "Y"); // address = active
                         System.out.println("Running sql...");
                         statement.executeUpdate();
                         System.out.println("sql finished.");
                        
                         statement = ((org.jboss.pool.jdbc.PreparedStatementInPool)statement).getUnderlyingPreparedStatement();
                         long thisID = ((org.gjt.mm.mysql.PreparedStatement) statement).getLastInsertID();
                         //original ! long thisID = ((org.gjt.mm.mysql.PreparedStatement) statement).getLastInsertID();
                        
                         mailingListID=thisID;
                         System.out.println("got id [PUKKA] : " + thisID);
                         MailingListPK mailingPK = new MailingListPK(thisID);
                         return mailingPK;
                         } catch (Exception e) {
                         System.out.println("sql or naming exception in insert mailing list ");
                         e.printStackTrace(System.err);
                         throw new CreateException(e.getMessage());
                         } finally {
                         try {
                         // maybe not closing?
                         System.out.print("Closing statement & connection...");
                         statement.close();
                         connection.close();
                         System.out.println("done!");
                         } catch (Exception ee) {
                         System.out.println("close db exception");
                         ee.printStackTrace(System.out);
                         }
                         }
                        
                        


                        • 9. Re: Pooled Connections Not Releasing
                          p_d_austin

                          Always have a different try catch block around the closing of statements and the connection as if there was an error closing the statement then the connection will never be closed.

                          Paul

                          • 10. Re: Pooled Connections Not Releasing
                            neilmcc

                            Sounds reasonable. In test, though, there have not been any exceptions logged - which would suggets that both the statement & the connection are being closed correctly. What I can't understand is that if the connection is released ok, why the Minerva pool does not see that connection checked in and ready for another bean to use.

                            • 11. Re: Pooled Connections Not Releasing
                              thedug

                              Do you have GCEnabled?

                              • 12. Re: Pooled Connections Not Releasing
                                neilmcc

                                Thanks for the tip.

                                As I understand it, the GCEnabled just sweeps up connections that have been unused for a (typically) long period of time. If i set this quite short (ie < 1 min) in conjunction with the IdleTimeout, it would be possible to force a rapid clean up of connections. But that wouldn't appear to be as instantaneous as closing a connection, the pool shrinking by 1 connection and then the next client invocation grabbing a new connection up to the max pool size.

                                What I don't understand is why, when checking a connection back into the pool (by closing it), that the pool doesn't shrink by one connection, thereby allowing another connection to be opened up to the MaxSize. It seems that the pool is reaching max size and stays there, even though other (concurrent) beans are releasing connections to the pool at the same time.

                                Hmm. Puzzled! Any thoughts very welcome!

                                • 13. Re: Pooled Connections Not Releasing
                                  davidjencks

                                  Try closing the PreparedStatementInPool instead of the underlying statement. If this doesn't work, I would comment out all the code that actually does anything with the connection and make sure the pool is working properly then. Then start adding stuff back in till something breaks, you will know what is the problem.

                                  • 14. Re: Pooled Connections Not Releasing
                                    ronpfeifle

                                    we're experiencing a similar problem with transactions locking up.
                                    basically, all available connections are being consumed.

                                    Are you saying that if prepared statements and/or result sets aren't
                                    properly closed before closing the connection, things could lock up?

                                    I'm pretty sure that we *are* cleaning up right -- but is that a true
                                    statement?

                                    Thanks
                                    Ron

                                    1 2 Previous Next