4 Replies Latest reply on Oct 21, 2005 6:03 PM by anupama oruganti

    Connection not getting closed on JBOSS 4.0.1

    Shaji Kakkodi Newbie

      Hi,

      In order to recreate the problem of number of open connections with our application, I created a test case with a simple application. While running stress tests on this simple application I noticed that database connections are not getting closed even through the application invokes close() method on the connection obtained from the container. When I run the test with 50 concurrent users, I can see at the database end that 48 connections being created. But these connection are not getting closed even after the test is over. These connections go away only if I restart the jboss.
      Any help appreciated. Application code is listed below

      Thanks
      -Shaji

      The application is a simple struts application querying a table in oracle 9i database. Jboss 4.0.1 is running on Windows 2000

      Application code that make database query is listed below



      public List getAllCDES(String longName) throws Exception
      {
      Connection conn = null;
      Statement st =null;
      ResultSet rs = null;
      List list = new ArrayList();
      try
      {
      conn = SimpleDAOImpl.getConnectionFromContainer("java:comp/env/jdbc/CDEBrowserDS");
      //Statement st = conn.
      String sqlStmt = "SELECT * FROM data_elements_view "
      +"WHERE long_name like '"+longName+"'";

      st = conn.createStatement();
      rs = st.executeQuery(sqlStmt);

      while(rs.next())
      {
      DataElement de = new DataElement();
      de.setLongName(rs.getString("long_name"));
      de.setPublicId(rs.getString("cde_id"));
      list.add(de);
      }

      }

      catch (Exception e)
      {
      System.out.println("Exception while getting CDEs");
      throw e;
      }
      finally
      {
      if(st!=null)
      try
      {
      st.close();
      }
      catch (Exception ex)
      {
      System.out.println("Exception while Closing st");
      }
      if(rs!=null)
      try
      {
      rs.close();
      }
      catch (Exception exp)
      {
      System.out.println("Exception while Closing rs");
      }
      if(conn!=null)
      try
      {
      conn.close();
      System.out.println("Connection Closed");
      }
      catch (Exception ecexp)
      {
      System.out.println("Exception while Closing Connection");
      }
      }
      return list;

      }

      public static Connection getConnectionFromContainer(String dataSourceName)
      throws NamingException,Exception {

      Connection conn = null;
      try {
      InitialContext ic = new InitialContext();
      DataSource ds = (DataSource)ic.lookup(dataSourceName);
      conn = ds.getConnection();

      System.out.println("Connected to the database successfully using datasource "+dataSourceName);
      }
      catch (NamingException ne) {
      System.out.println("Exception occurred in getConnectionFromContainer for DataSource name="+dataSourceName);
      throw new NamingException
      ("Failed to lookup JDBC datasource. Check the datasource name");
      }
      catch (Exception e) {
      System.out.println("Exception occurred in getConnectionFromContainer");
      throw new Exception("Exception in getConnectionFromContainer() ");
      }
      return conn;
      }

      Below is the oracle-ds.xml entry


      <local-tx-datasource>
      <jndi-name>jdbc/CDEBrowserDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@myhost:1521:dev</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>user</user-name>
      mypassword
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <blocking-timeout-millis>0</blocking-timeout-millis>
      <idle-timeout-minutes>0</idle-timeout-minutes>
      <max-pool-size>100</max-pool-size>
      <min-pool-size>0</min-pool-size>
      </local-tx-datasource>


        • 1. Re: Connection not getting closed on JBOSS 4.0.1
          Darran Lofthouse Master

          Yes that is the correct bahaviour, the main reason you have deployed a data source that you access through JNDI is because the connections are pooled.

          This means that after a connection has been created it can be re-used for subsequent requests. The call to close just puts the connection back in the pool ready for another client to use it.

          Setting a value for the idle timeout other than 0 should see the connection closed after they have been idle for the time specified.

          • 2. Re: Connection not getting closed on JBOSS 4.0.1
            Shaji Kakkodi Newbie

            Thanks for the reply.

            It is working when I changed the idle-timeout-minutes to value other than 0.

            Now I see that during tests with large number of concurrent users (50-100)

            I get the sql Exception below after user count reach a certain number.
            Could this be because connections could not be opened in quick intervals?



            2005-07-06 14:02:45,506 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
            org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Io exception: The Network Adapter could not establish the connection)



            • 3. Re: Connection not getting closed on JBOSS 4.0.1
              Steven Ostrowski Newbie

               

              "kakkodi" wrote:
              Thanks for the reply.

              It is working when I changed the idle-timeout-minutes to value other than 0.

              Now I see that during tests with large number of concurrent users (50-100)

              I get the sql Exception below after user count reach a certain number.
              Could this be because connections could not be opened in quick intervals?



              2005-07-06 14:02:45,506 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
              org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Io exception: The Network Adapter could not establish the connection)





              That looks to me like an actual connection issue, not a configuration issue. Verify that your JDBC connection URL is correct and that the DB is up and running. Try to connect to the DB from a standalone client using the same connection URL.

              • 4. Re: Connection not getting closed on JBOSS 4.0.1
                anupama oruganti Newbie

                Hi.
                sorry to interrupt, but along those lines when server getting connection from connection pool if Database is down we will get SQLException Network adapter isn't able to create connection. Is there anyway we can catch that exception from CMP?

                I am using jboss 3.2.6.

                Please let me know ASAP.

                Thanks alot;