Resolving connection pool causes

Question

I am experiencing (I think) problems with my connection pool. In particular, a message is displayed in my logs:

org.apache.tomcat.dbcp.pool2.impl.DefaultPooledObject $ AbandonedObjectCreatedException: A merged object created [time] by the following code does not return to the pool

I checked the methods listed in the stack trace that the logs show, but could not find the culprit (I always close the ResultSet , PreparedStatement and Connection at the end of each method).

I have a method that executes two requests, maybe I am not executing it correctly.

It looks like this:

 ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; PreparedStatement rowsPs = null; ResultSet rs = null; ResultSet rowsRs = null; String query = "SELECT SQL_CALC_FOUND_ROWS ..."; String totalRowsQuery = "SELECT FOUND_ROWS() AS RowCount"; try { ps = connection.prepareStatement(query); [set ps params] rs = ps.executeQuery(); [process rs] rowsPs = connection.prepareStatement(totalRowsQuery); rowsRs = rowsPs.executeQuery(); [process rowsRs] } catch (SQLException e) { [handle e] } finally { DBUtil.closeResultSet(rs); [close rowsRs] [close ps] [close rowsPs] [close connection] } 

Where is an example DBUtils method:

 public static void closeResultSet(ResultSet rs) { try { if (rs != null) rs.close(); } catch (SQLException sqle) { sqle.printStackTrace(); } } 

Is the general layout of this method compatible? Should I handle the connection differently? Or is it some other method that causes errors to be logged?

Thanks.

Additional Information

I also get a SQLException :

java.sql.SQLException: connection com.mysql.jdbc.JDBC4Connection @ [some number] closed

in line: rowsPs = connection.prepareStatement(totalRowsQuery);

Meaning that somewhere earlier, the connection is closed. I in no way close the connection. Is it possible that some other data access method, which is called, somehow closes the connection in this method? ( pool.getConnection() calls dataSource.getConnection() )

Update : I tried using try-with-resources as suggested, but the problem persists.

The ConnectionPool class referenced by the first code snippet above:

 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([jdbc/dbName]); } catch (Exception e) { e.printStackTrace(); } } public Connection getConnection() { try { return dataSource.getConnection(); } catch (SQLException sqle) { sqle.printStackTrace(); return null; } } public void freeConnection(Connection c) { try { c.close(); } catch (SQLException sqle) { sqle.printStackTrace(); } } } 

Source : Pool Resource Element:

 <Resource auth="Container" driverClassName="com.mysql.jdbc.Driver" logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000" removeAbandonedOnBorrow="true" removeAbandonedTimeout="60" type="javax.sql.DataSource" testWhileIdle="true" testOnBorrow="true" validationQuery="SELECT 1 AS dbcp_connection_test"/> 

Update: I turned on the slow query log, but although Exception is fired again, the slow query log does not log anything (the request does not take more than 10 seconds).

So it looks like this is not the case, that the request takes more than 60 seconds.

It is still unknown what causes this.

Answer 1

As a general rule when working with JDBC federated joins:

• Do not leave multiple ResultSet instances for each connection at the same time. Close the first ResultSet and its associated statement before opening the second.

• Always close resources in the order in which you created them: create (s1) → execute (r1) → close (r1) → close (s1) → create (s2) → execute (r2) → close (r2) → close (s2 )

• Make sure that long-term processing of requests does not exceed the maximum connection lifetime allowed by the pool.

• Even if you do not exceed the maximum connection lifetime, the connection may be lost due to small interruptions in the network link.

Answer 2

Does mysql server delay connections? It can also be the core, if the connection is idle for a long time, the basic tcp connection can be killed.

Answer 3

Try reducing the removeAbandonedTimeout value to about 20 or 15. Your maxWait is 10 seconds, but if the connections are canceled, you will wait 60 seconds before returning them.

Please note that this is not a solution, but just a test to check if your connections are really hanging.

Answer 4

These 2 lines take 60+ seconds, so the connection pool decides that your connection is abandoned and closes it. Your code later tries to use the connection, but it is already disabled by the connection pool.

 rs = ps.executeQuery(); [process rs] 

Since you are sure that it is not long executeQuery (), do a debug print with the connection / thread / request identifier and time for the [process rs] part. If you see something like 50 seconds for this part, you should optimize [process rs] or read the data and store it in memory before processing.