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

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.