5 Replies Latest reply on Nov 8, 2013 5:20 AM by Ravi Patil

    connection pool issue in jboss as 7

    Ravi Patil Newbie

      Hi Everyone,

       

      I am using jboss 7.2.0 and spring framework for jdbc connections.

      I have configured datasources in jboss. I am invoking jdbc connection from spring's DataSourceUtils class which in turn does all jndi prefix look up. I am getting connection, using it but once it reaches to max pool it throws "No managed connection available within the configured blocking time out [60000] millis"


      My data source configuration looks:


      <datasource jta="true" jndi-name="java:jboss/datasources/MyDS" pool-name="MyDS" enabled="true" use-java-context="true" use-ccm="true">

                          <connection-url>jdbc:oracle:thin:@localhost:1521:instance1</connection-url>

                          <driver>oracle</driver>

                          <new-connection-sql>select * from dual</new-connection-sql>

                          <pool>

                              <min-pool-size>30</min-pool-size>

                              <max-pool-size>100</max-pool-size>

                              <prefill>true</prefill>

                              <use-strict-min>false</use-strict-min>

                              <flush-strategy>IdleConnections</flush-strategy>

                          </pool>

                          <security>

                              <user-name>ravi</user-name>

                              <password>password</password>

                          </security>

                          <validation>

                              <check-valid-connection-sql>select * from dual</check-valid-connection-sql>

                              <background-validation>true</background-validation>

                              <background-validation-millis>10000</background-validation-millis>

                          </validation>

                          <timeout>

                              <idle-timeout-minutes>1</idle-timeout-minutes>

                              <allocation-retry>3</allocation-retry>

                          </timeout>

                      </datasource>

       

       

      My connection invocation code looks like:


      Client.java

      try {

           selectSQL = "SELECT imsi_id FROM MOBILE_REGISTERATION_TABLE WHERE account_no = ?";

           stmt = ConnectionManager.getConnection (DataSourceNames.MyDS).prepareStatement (selectSQL);

           stmt.setInt (1, imsi_id);

           LogUtil.debug (Client.class, "getImsiId","Calling %s with (%d)", selectSQL, imsi_Id);

           rs = stmt.executeQuery ();

      ..........................................................

      }

      catch(SQLException ex){

      ex.printStackTrace();

      }

      finally {

      ConnectionManager.close(stmt,rs(;

      }

       

       

       

      ConnectionManager.java

        public static Connection getConnection (CCBSDatabase db)

        {

          return getConnection (db, null);

        }


        public static Connection getConnection (CCBSDatabase db, Short id)

        {

          return getInstance ().get (db, id);

        }


        public Connection get (CCBSDatabase db, Short id)

        {

          return DataSourceUtils.getConnection (getDataSource (db, id));

        }

        //My connection close code looks like:

       

        public static void close (Statement stmt, ResultSet rs)

        {

          try

          {

            if (rs != null)

            {

              rs.close ();

             

            }

          }

          catch (SQLException ex)

          {

            LogUtil.error (ConnectionManager.class, "close", ex);

          }

          finally

          {

            try

            {

              if (stmt != null)

              {

               stmt.close ();

              }

            }

            catch (SQLException ex)

            {

              LogUtil.error (ConnectionManager.class, "close", ex);

            }

          }

        }

       

       

      Looking for your reply.....

      Thanks,

      Ravi

        • 1. Re: connection pool issue in jboss as 7
          Ravi Patil Newbie

          The stack trace is :

           

          Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (60000 [ms])

                  at

           

          org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:384)

                  at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397)

                  at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365)

                  at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:364)

                  ... 109 more

           

           

           

           

          Am I not releasing the connection?

          • 2. Re: connection pool issue in jboss as 7
            Radim Hanus Expert

            definitely, but you have no reference to the connection object:

            stmt = ConnectionManager.getConnection (DataSourceNames.MyDS).prepareStatement (selectSQL);

            you should hold an instance of connection to close it later in correponding finally block :

            Connection conn;

            try {

                 conn = ConnectionManager.getConnection (DataSourceNames.MyDS)

                 stmt = conn.prepareStatement (selectSQL);

                 ...

            }

            finally {

                 conn.close()

            }

            • 3. Re: connection pool issue in jboss as 7
              Ravi Patil Newbie

              Radim Hanus

              I can get the connection associated with the stmt object. I tried following code. but when I try to close, I get some "connection is not associated with..........." exception, It has to maintain connection pool.

               

              finally

              {

              try

              {

              if (stmt != null)

              {

                  Connection con = stmt.getConnection();

                  stmt.close ();

                  con.close();

              }

              }

              catch (SQLException ex)

              {

              LogUtil.error (ConnectionManager.class, "close", ex);

              }

              }

               

              However spring is also involved in maintaining connection. I am not getting whats wrong with this jdbc connections.

              • 4. Re: connection pool issue in jboss as 7
                Radim Hanus Expert

                then you should use spring framework facilities to return the connection, i.e. methods of DataSourceUtils

                • 5. Re: connection pool issue in jboss as 7
                  Ravi Patil Newbie

                  rhanus

                  I used a method from DataSourceUtils class, doReleaseConnection

                   

                  void org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(Connection arg0, DataSource arg1) throws SQLException

                   

                  But for this also It is throwing "connection is not associated with......" exception

                   

                   

                  finally

                  {

                  try

                  {

                  if (stmt != null)

                  {

                      Connection con = stmt.getConnection();

                      Datasource ds = DataSourceNames.MyDS;

                      stmt.close ();

                      DataSourceUtils.doReleaseConnection(con, arg1);

                   

                  }

                  }

                  catch (SQLException ex)

                  {

                  LogUtil.error (ConnectionManager.class, "close", ex);

                  }