connection pool issue in jboss as 7

[ravipatil080]

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

[ravipatil080]

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?

[rhanus]

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()

}

[ravipatil080]

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.

[rhanus]

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

[ravipatil080]

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);

}