Statement and result set left open
A medium Java puzzle in the resource leaks family. One line below contains a real bug. Try to spot it first, then read why it fails and how to fix it.
Highlighted: line 10. Play this one as a puzzle without the answer.
What is wrong
The method returns without closing the ResultSet, Statement or Connection. With a pooled DataSource the connection is never returned, so after pool-size calls every request waits and times out. Garbage collection does not return pooled connections.
The fix
How to avoid it next time
Declare Connection, Statement and ResultSet in one try-with-resources header.
Want the background? Read JDBC connections, DataSource and pools in the Java concepts library.
About resource leaks
Files, streams, sockets, connections and statements hold operating system handles that the garbage collector will not release promptly. A leak often shows up only in production after hours of load as too many open files or an exhausted connection pool.
In code review: Every constructor of a closeable type should sit in a try-with-resources header. Check the exceptional path as well as the normal one.
More puzzles in this family
Frequently asked questions
What is the bug in "Statement and result set left open"?
The method returns without closing the ResultSet, Statement or Connection. With a pooled DataSource the connection is never returned, so after pool-size calls every request waits and times out. Garbage collection does not return pooled connections. The faulty line is line 10: return rs.getInt(1);
How do I fix it?
Replace it with: try (Connection c = ds.getConnection(); Statement st = c.createStatement(); ResultSet rs = st.executeQuery("select count(*) from users")) { rs.next(); return rs.getInt(1); }. Declare Connection, Statement and ResultSet in one try-with-resources header.
How do I catch resource leaks in code review?
Every constructor of a closeable type should sit in a try-with-resources header. Check the exceptional path as well as the normal one.
Is there a new puzzle every day?
Yes. The daily Spot the Bug puzzle picks one of these snippets for each UTC day, with the difficulty set by the weekday. Your streak is stored only in your browser.