Pool empty. Unable to fetch a connection in 10 seconds

Question

After some time of running I'm getting this error when I stress test my servlet with at least 20 browser tabs simultaneously accessing the servlet:

java.sql.SQLException: [tomcat-http--10] Timeout: Pool empty. Unable to fetch a connection in 10 seconds, none available[size:200; busy:200; idle:0; lastwait:10000].

Here is the XML config for this:

<Resource name="jdbc/MyAppHrd"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          testWhileIdle="true"
          testOnBorrow="true"
          testOnReturn="false"
          validationQuery="SELECT 1"
          validationInterval="30000"
          timeBetweenEvictionRunsMillis="30000"
          maxActive="200"
          minIdle="10"
          maxWait="10000"
          initialSize="200"
          removeAbandonedTimeout="120"
          removeAbandoned="true"
          logAbandoned="false"
          minEvictableIdleTimeMillis="30000"
          jmxEnabled="true"
          jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
            org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
          username="sa"
          password="password"
          driverClassName="net.sourceforge.jtds.jdbc.Driver"
          url="jdbc:jtds:sqlserver://192.168.114.130/MyApp"/>

What could be the problem?

Update: Java Code:

public class MyServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;
    private static final Log LOGGER = LogFactory.getLog(MyServlet.class);

   private void doRequest(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {

        CallableStatement stmt = null;
        ResultSet rs = null;

        Connection conn = null;
        try {

            conn = getConnection();

            stmt = conn.prepareCall("{call sp_SomeSPP(?)}");
            stmt.setLong(1, getId());

            rs = stmt.executeQuery();

            // set mime type
            while (rs.next()) {
                if (rs.getInt(1)==someValue()) {
                    doStuff();
                    break;
                }
            }
            stmt = conn.prepareCall("{call sp_SomeSP(?)}");
            stmt.setLong(1, getId());

            rs = stmt.executeQuery();
            if (rs.next()) {
                // do stuff
            }

            RequestDispatcher rd = getServletContext().getRequestDispatcher("/SomeJSP.jsp");
            rd.forward(request, response);
            return;
        } catch (NamingException e) {
            LOGGER.error("Database connection lookup failed", e);
        } catch (SQLException e) {
            LOGGER.error("Query failed", e);
        } catch (IllegalStateException e) {
            LOGGER.error("View failed", e);
        } finally {
            try {
                if (rs!=null && !rs.isClosed()) {
                    rs.close(); 
                }
            } catch (NullPointerException e) {
                LOGGER.error("Result set closing failed", e);
            } catch (SQLException e) {
                LOGGER.error("Result set closing failed", e);
            }
            try {
                if (stmt!=null) stmt.close();
            } catch (NullPointerException e) {
                LOGGER.error("Statement closing failed", e);
            } catch (SQLException e) {
                LOGGER.error("Statement closing failed", e);
            }
            try {
                if (conn != null){
                    conn.close();
                    conn = null;
                }
            } catch (NullPointerException e) {
                LOGGER.error("Database connection closing failed", e);
            } catch (SQLException e) {
                LOGGER.error("Database connection closing failed", e);
            }
        }

   }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doRequest(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doRequest(request, response);
    }

    protected static Connection getConnection() throws NamingException, SQLException {
        InitialContext cxt = new InitialContext();
        String jndiName = "java:/comp/env/jdbc/MyDBHrd";
        ConnectionPoolDataSource dataSource = (ConnectionPoolDataSource) cxt.lookup(jndiName);
        PooledConnection pooledConnection = dataSource.getPooledConnection();
        Connection conn = pooledConnection.getConnection();
        return conn; // Obtain connection from pool
    }   

Answer 1

I suggest you change your getConnection method to the following you might actually be removing the Pooling support by going directly to via the javax.sql.PooledConnection interface

        InitialContext cxt = new InitialContext();
        String jndiName = "java:/comp/env/jdbc/MyDBHrd";
        DataSource dataSource = (DataSource) cxt.lookup(jndiName);
        return dataSource.getConnection();

Also use something like DBUtils#closeQuietly to clean up your connections

Update: You are removing the Pooling support from the Connection. If you run the following and look at the output you will see the connection retrieved directly from the DataSource is a ProxyConnection wrapping a PooledConnection.

public static void main(String[] args) throws Exception {

    Properties properties = new Properties();
    properties.put("username", "sa");
    properties.put("password", "password");
    properties.put("driverClassName", "net.sourceforge.jtds.jdbc.Driver");
    properties.put("url", "jdbc:jtds:sqlserver://192.168.114.130/MyApp");       

    DataSourceFactory dsFactory = new DataSourceFactory();      
    DataSource ds = dsFactory.createDataSource(properties);     
    ConnectionPoolDataSource cpds = (ConnectionPoolDataSource) ds;
    PooledConnection pooledConnection = cpds.getPooledConnection();

    System.out.println("Pooled Connection - [" + ds.getConnection() + "]"); // Close will return to the Pool
    System.out.println("Internal Connection - [" + pooledConnection.getConnection() + "]"); // Close will just close the connection and not return to pool

}

Answer 2

First, you are not closing your Statement and ResultSet objects within the body of your method.

They should be cleaned-up when you call close on the the Connection (according to the JDBC spec), but in a pooled setting, they might not actually get cleaned up.

Second, you are unwrapping the pooled connection and returning the underlying connection which will break everything.

So, modify your code to be like this:

 try {
        conn = getConnection();

        stmt = conn.prepareCall("{call sp_SomeSPP(?)}");
        stmt.setLong(1, getId());

        rs = stmt.executeQuery();

        // set mime type
        while (rs.next()) {
            if (rs.getInt(1)==someValue()) {
                doStuff();
                break;
            }
        }

        // ADD THESE LINES
        rs.close(); rs = null;
        stmt.close(); stmt = null;

        stmt = conn.prepareCall("{call sp_SomeSP(?)}");
        stmt.setLong(1, getId());

        rs = stmt.executeQuery();
        if (rs.next()) {
            // do stuff
        }
}

....

protected static Connection getConnection() throws NamingException, SQLException {
    InitialContext cxt = new InitialContext();
    String jndiName = "java:/comp/env/jdbc/MyDBHrd";
    DataSource dataSource = (DataSource) cxt.lookup(jndiName);
    return dataSource.getPooledConnection();
}   

And, as others have said, you definitely want to clean-up your resources before you do things like forwarding to another page. Otherwise, you keep the connection far longer than necessary. It's a critical resource: treat it like one.

Answer 3

The code you currently provide looks long/complex, but fine.

However, I guess your "doStuff" method might be a candidate for leaking more connections

Answer 4

Close your connection before the following.

RequestDispatcher rd = getServletContext().getRequestDispatcher("/SomeJSP.jsp");
        rd.forward(request, response);
        return;

Also remove return if it is not required.

Answer 5

Probably, you are holding connection for too long.

Make sure that you do not open DB connection when you start processing request and then release it when you finally committed the response.

Typical mistake is:

    @Override
    protected void doGet (
            final HttpServletRequest request,
            final HttpServletResponse response
        ) throws
            ServletException,
            IOException
    {
        Connection conn = myGetConnection( );

        try
        {
            ...
            // some request handling


        }
        finally
        {
            conn.close( )
        }
    }

In this code, database connection lifetime is totally at the mercy of the client connected to your server.

Better pattern would be

    @Override
    protected void doGet (
            final HttpServletRequest request,
            final HttpServletResponse response
        ) throws
            ServletException,
            IOException
    {
        // some request preprocessing
        MyProcessedRequest parsedInputFromRequest =
            getInputFromRequest( request );

        final MyModel model;
        {
           // Model generation
           Connection conn = myGetConnection( );

           try
           {
              model = new MyModel( conn, parsedInputFromRequest );
           }
           finally
           {
              conn.close( );
           }
        }


        generateResponse( response, model );         
    }

Note, that if the bottleneck is in model generation, you still going to run out of connections, but this is now a problem for DBA, that relates to better data management/indexing on the database side.

Answer 6

Check your jdbc connections are closed after completion of process. It may caused by unclosed connections.