OutOfMemoryError caused by PreparedStatements held in Connection

[Steven Ostrowski]

Hello,

I'm experiencing OutOfMemoryError in my application, and it's almost always caused by calling one query too many times. For some reason, the statements executed by that query do not get garbage collected. Note: it is not one instance of this query that causes OutOfMemoryError, it is a memory leak that builds up until the application runs out of memory.

Here's the setup:
- using JBoss connection pooling with Oracle oracle.jdbc.OracleDriver
- default statement cache size
- non-scrollable and non-updatable result sets
- standard query access: build the statement, execute the statement, create a new object, populate the object with data that is retrieved from the result set, close the result set, close the statement, release the connection

I've tried many things to narrow down the cause, none of which changed the problem:
- reverted to my custom connection pooling (this ruled out JBoss connection pooling as the cause)
- set the statement cache size to 0 (this ruled out statement caching as the cause)
- verified that the result sets are non-scrollable and non-updatable (this ruled out Oracle caching large amounts of data on the client)

I've posted a screenshot of a heap dump that shows the object allocation here:

Heap Dump Screenshot

I do not know what the JVMPI_GC_ROOT_MONITOR_USED means, but others have mentioned that it probably means the GC thinks these objects are definitely referenced, so it will not garbage collect them. I've verified that other queries in my application do not cause this memory leak.

Any help would be greatly appreciated!

[Shailesh Chandra]

seems you have done enough efforts. I have only one suggesion that check if connection is being closed properly and query have valid joins in your query.

[Rene Smith]

can you post a snippit of your code to show where you are opening the connection all the way through where you are closing your connection?

Thanks,
Rene

[Steven Ostrowski]

Here's generally what is happening:

In JBoss, I have a datasource file:
<datasources>
<local-tx-datasource>
<jndi-name>jdbc/MyDS</jndi-name>
...


Then, I have my connection retrieval code in its own class that is used to start a transaction:
InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("java:/jdbc/MyDS"));
Connection conn = ds.getConnection();
conn.setAutoCommit(false);
...
return conn;

Then, the code that uses the connection:

>>> builds up SQL statement by appending clauses into a StringBuffer
...
buffer.append("SELECT ...?..?.");
...

>>> prepares statement, and sets values, etc.
...
PreparedStatement stmt = conn.prepareStatement(buffer.toString());
...
ResultSet rs = stmt.executeQuery();

while (rs.next()) {
MyObject obj = new MyObject();
obj.setX(rs.getString("X"));
...
aList.add(obj);
}

finally {
rs.close();
stmt.close();
}



Then, when I end the transaction:
...
conn.commit();
conn.close();
...





Hope that is enough snippets for you! =)

[Rene Smith]

The code looks good to me. The only thing you could do is set the obj = null, rs = null, statement = null, and connection = null at the end. I don't thing any of those would cause a memory leak though....

[Shailesh Chandra]

even the code sounds good for me

can you tell us that how many rows resultset contains generally .

It may be possibile that you have too many rows in resultset and multile access to code causes lack in heap memory.

[Steven Ostrowski]

Originally posted by Rene Smith:
The code looks good to me. The only thing you could do is set the obj = null, rs = null, statement = null, and connection = null at the end. I don't thing any of those would cause a memory leak though....

Yep, tried that, didn't do anything. I also think the GC is guaranteed to run before you get an OutOfMemoryError anyway.

[Steven Ostrowski]

btw - I tried this out with the Oracle 10g drivers as well, which are a very different implementation of the JDBC driver.

The screenshot of the new heap dumps are interesting in that it looks more like a deep recursion issue now:

http://www.dealraider.com/heapDump10a.jpg
http://www.dealraider.com/heapDump10b.jpg

I still haven't gotten a good definition anywhere of what JVMPI_GC_ROOT_MONITOR_USED means. However, like another poster said, it does seem to appear that this is an object the GC believes cannot be garbage collected. I've verified with -verbosegc on that even after a full GC, these objects do not get deleted.

Thanks

[Steven Ostrowski]

Sure enough, this turned out to be one of our developers not closing ResultSet and PreparedStatement. They have since been fired (j/k).