What is the reason for OutOfMemoryError: Java heap space in the following case?

Question

The following code sample is inside a for loop that runs about 2 million times.

List<String> parameters = new LinkedList<String>();
stmt2 = null;
rs2= null;

//This is line 472
stmt2 = con.prepareStatement("select NAME from TABLE_NAME where FIELD="+ strId);
rs2 = stmt2.executeQuery();

while (rs2.next()) {
    parameters.add(rs2.getString("NAME"));
}

stack trace:

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:437)
    at com.mysql.jdbc.Connection.clientPrepareStatement(Connection.java:2185)
    at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4782)
    at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4687)
    at consistencyCheck.ConsistencyCheck.parameterCheck(ConsistencyCheck.java:472)
    at consistencyCheck.ConsistencyCheck.performConsistencyCheck(ConsistencyCheck.java:316)
    at consistencyCheck.ConsistencyCheck.main(ConsistencyCheck.java:198)

Please let me know if more information is required.

Thank you.

Thanks everyone for the answers. I will accept BalusC's answer since he answered first. Unfortunately I cant upvote any other answers due to not enough reputation :(

Just a side note to all who suggested to increase memory heap. Increasing the memory heap is something you should never do unless you are 100 % sure that is the only solution to your problem. For example in my problem increasing the heap might 'solve' the problem, but the underlying blunder still remains.

**Definitely** not. The underlying resources will then still be open until you close the connection which created the statements. Better is to close each statement (and resultset) individually. — BalusC, Nov 18 '11 at 21:32

score 13 · Accepted Answer · edited May 23 '17 at 12:33

13

Based on the comments, you seem to be creating the Statement and ResultSet inside the loop but never closing them. You need to close them in the loop as well. This will free up internal resources.

Also, you are not really taking benefit of the DB cache of the prepared statement. Right now you are string-concatenating the parameter in the SQL string which causes 2M String objects being created instead of 1 String object. Better prepare the statement before the loop.

try {
    // ...
    statement = connection.prepareStatement("select NAME from TABLE_NAME where FIELD=?");

    for ( /* 2M times? */ ) {
        statement.setInt(1, id);

        try {
            resultSet = statement.executeQuery();
            // ...
        } finally {
            if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {}
        }
    }
} finally {
    if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
}

Alternatively, you can also consider to use an IN clause instead. E.g.

WHERE field IN (1,2,3,4,5);

This is however trickier with placeholders. See also: What is the best approach using JDBC for parameterizing an IN clause?

Or as a completely different alternative, if necessary with help of a more experienced DB admin / SQL ninja, rewrite the entire thing so that you get exactly the results you need with only one SQL query. Ask if necessary a separate question about that on sql here on SO.

edited May 23 '17 at 12:33

Community

1
1

answered Nov 18 '11 at 21:31

BalusC

1,082,665
372
3,610
3,555

the connection still remains alive after I close Statement and ResultSet? – rana Nov 18 '11 at 21:44
Sure. It's only closed when you *explicitly* call `close()` on it, or when the DB decides that it's been hold open for too long and then reclaims it. – BalusC Nov 18 '11 at 21:46
Also, If I close Statement and ResultSet I do not need to assign them to null anymore. The reason I did this in the first place was to try to fix the memory heap exception. @BalusC – rana Nov 18 '11 at 22:11
If you declare them inside the loop and the loop ends, they're eligible for GC already. Setting to `null` makes no sense then. – BalusC Nov 18 '11 at 22:13
I declare them out side the loop. Inside the loop I use them multiple times. Is it enough If I just close them after each use? – rana Nov 18 '11 at 22:19
Why are they declared outside the loop if they are never used outside the loop? – BalusC Nov 18 '11 at 22:23
oops, that was my bad. I fixed it now. Thanks a lot for the help. – rana Nov 18 '11 at 22:55
sry to bother you again. Now I get an error outofmemory: GC overhead limit exceeded. Error points to same line. Any suggestions? – rana Nov 18 '11 at 23:43
You can get it when there are **too many** GC-eligible objects that over 98% of CPU time is spent to GC instead of the actual process. Are you explicitly calling `System.gc()`? If so, remove it. If not, I'd consider rethinking the approach. Right now you are not really taking benefit of the prepared statement by string-concatenating the value. You need to prepare the statement with `FIELD=?` outside the loop and then do `setInt(1, id)` inside the loop. – BalusC Nov 19 '11 at 00:18

score 5 · Answer 2 · edited Nov 18 '11 at 21:35

5

After you are done with while loop a rs2.close() & stmt2.close() would help.

edited Nov 18 '11 at 21:35

ffriend

27,562
13
91
132

answered Nov 18 '11 at 21:32

Vishal Biyani

4,297
28
55

score 1 · Answer 3 · answered May 16 '13 at 10:09

There are another resolve method, if you don't want to upping your JVM heap size.

First, your MySQL version should newer than 5.0.

Second, Statement.getResultSetType() should be TYPE_FORWARD_ONLY and ResultSetConcurrency should be CONCUR_READ_ONLY(default).

Third, include ONE of these lines: 1).statement.setFetchSize(Integer.MIN_VALUE); 2).((com.mysql.jdbc.Statement)stat).enableStreamingResults();

now you will fetch result rows one by one

score 1 · Answer 4 · answered Nov 19 '11 at 04:40

I wish to clarify some things concerning JDBC resources using.

The best practice for all resourses to close them in finally block. Youv'e been giving examples with ResultSet and PreparedStatement already.
If your application does not use connection pool and you invoke connecion.close(), actually it should close both ResultSet and PreparedStatement objects. Connection implementations as a rule keep all these objects inside and even if you create them in the loop, they will be closed.
If connection pool is used, then, when you invoke connection.close() you don't close physical connection itself, you just release it back to the pool. As a result ResultSet and PreparedStatement objects are not closed. I strongly recommend you to read the following article about it Plug memory leaks in enterprise Java applications
In Java7 both Statement and ResultSet extend AutoCloseable, so you also may not worry about closing them. (Again, if you use connection pools, I don' think Java 7 will be able to close them automatically)

What is the reason for OutOfMemoryError: Java heap space in the following case?

4 Answers4