CODEWITHSUNDEEP
javatomcatconnection-pooling
Abhinav
Abhinav

Reputation: 3428

Why this error - Cannot get a connection, pool error Timeout waiting for idle object

I am having a webapp on production, which runs fine for 7-8 days then suddenly my website is down and logs reproduce this error:

java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object

And all operations to database starts failing. I have read across several other questions and blog but not able to find any definite solution. I am using connection pool, and I am really not sure where the problem is.

Is it because of my code which I have written or pool configuration? I am providing code here for one of method which gets data from database and also the pool configuration. Please have a look and let me know if I am doing something wrong.

public CartItem getCustomerCartItem(int customerId, int productId, int productOfCityId) {
    Connection con = ConnectionPool.getInstance().getConnection();
    PreparedStatement st = null;
    ResultSet rs = null;
    CartItem ci = null;

    CityProduct cityProduct = productService.getCityProduct(productId, productOfCityId);
    if (cityProduct == null) {
        return null;
    }

    String query = "SELECT ci.* FROM customer_cart_item_mapping cim INNER JOIN cart_item ci ON ci.Id = cim.CartItemId WHERE "
            + "cim.CustomerId = ? AND ci.ProductOfCityId = ?";

    try {
        st = con.prepareCall(query);

        st.setInt(1, customerId);
        st.setInt(2, productOfCityId);

        rs = st.executeQuery();

        if (rs.first()) {
            ci = new CartItem();
            ci.setId(rs.getInt("Id"));
            ci.setCityProduct(cityProduct);
            ci.setQuantity(rs.getInt("Quantity"));
            ci.setCreatedDate(rs.getDate("CreatedDate"));
            ci.setUpdatedDate(rs.getDate("UpdatedDate"));
        }
    } catch (SQLException ex) {
        Logger lgr = Logger.getLogger(CartItemDaoImpl.class.getName());
        lgr.log(Level.SEVERE, ex.getMessage(), ex);

    } finally {
        DBUtil.close(con, st, rs);
    }

    return ci;
}

Below is DBUtil class used to close connections:

public class DBUtil {

public static void close(Connection c, Statement s, ResultSet r) {
    try {
        if (r != null) {
            r.close();
        }
        if (s != null) {
            s.close();
        }
        if (c != null) {
            ConnectionPool.getInstance().freeConnection(c);
        }

    } catch (SQLException ex) {
        Logger.getLogger(DBUtil.class.getName()).log(Level.SEVERE, null, ex);
    }
}

}

Following is ConnectionPool class

public class ConnectionPool {
private static ConnectionPool pool=null;
private static DataSource dataSource = null;

public synchronized static ConnectionPool getInstance(){
    if (pool == null){
        pool = new ConnectionPool();
    }
    return pool;
}

private ConnectionPool(){
    try{
        InitialContext ic = new InitialContext();
        dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/prod_db");
    }
    catch(NamingException e){
        System.out.println(e);
    }
}

public Connection getConnection(){
    try{
        return dataSource.getConnection();
    }
    catch (SQLException sqle){
        System.err.println(sqle);
        return null;
    }
}

public void freeConnection(Connection c){
    try{
        c.close();
    }
    catch (SQLException sqle){
        System.err.println(sqle);
    }
}

}

Below is connection pool configuration

<Context antiJARLocking="true" path="/">
  <Resource auth="Container" connectionProperties="useEncoding=true;" driverClassName="com.mysql.jdbc.Driver" initialSize="2"
        logAbandoned="true" maxIdle="40" maxTotal="70" maxWaitMillis="1000" minEvictableIdleTimeMillis="600000" minIdle="2" name="jdbc/winni_prime_db"
        password="password" removeAbandoned="true" removeAbandonedTimeout="90" testWhileIdle="true" timeBetweenEvictionRunsMillis="90000"
        type="javax.sql.DataSource" url="jdbc:mysql://localhost:3306/prod_db"
        username="sqladmin" validationQuery="SELECT 1"/>

This problem has been persistent from last several months. Only solution is like to restart tomcat every night. Please take a look at this code and give me an expert opinion where I am doing wrong. I am using MySQL

Upvotes: 0

Views: 3944

Answers (1)

Stan
Stan

Reputation: 1430

You can potentially have lick here:

if (cityProduct == null) {
    return null;
}

You already got connection before this code, but you can exit function without closing connection.

Upvotes: 0

Related Questions