jdbc potential memory leak

[mhanna]

Describe the bug
Using a yourkit to capture memory used by java application that uses thousands of prepared statements and non prepared statements to sqlite, i can see that the NativeDB's safeStmt set is much more than the statements is should track.. see screenshot

To Reproduce
Maybe run many prepare/non-prepare statements

Expected behavior
A clear and concise description of what you expected to happen.

Logs

Environment (please complete the following information):

• OS: Centos
• CPU architecture: x86_64]
• sqlite-jdbc version [e.g. 3.39.2.0]

Do you see any issue here?

[mhanna]

openjdk version "17" 2021-09-14 OpenJDK Runtime Environment (build 17+35-2724) OpenJDK 64-Bit Server VM (build 17+35-2724, mixed mode, sharing)

…

________________________________ From: Rhys Williams ***@***.***> Sent: Tuesday, July 18, 2023 11:02:01 AM To: xerial/sqlite-jdbc ***@***.***> Cc: Mhanna AbuTareef ***@***.***>; Author ***@***.***> Subject: Re: [xerial/sqlite-jdbc] jdbc potential memory leak (Issue #938) I am potentially experiencing a similar issue on Java 11.0.19, but not Java 11.0.16. Which version of Java are you running? — Reply to this email directly, view it on GitHub<#938 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ABAZ4TA3EXWS7WKILN43YFLXQY7HTANCNFSM6AAAAAA2I4TA2U>. You are receiving this because you authored the thread.Message ID: ***@***.***> * CAUTION: This email originated from outside of K2view *

[mhanna]

@gotson can anyone shed light on this?

[gotson]

@gotson can anyone shed light on this?

Someone would need to provide evidence of where the leak happens.

[noahboegli]

@mhanna I had exactly the same problem as you. A program running for 6+ hours doing requests and requests to an SQLite db.

The debian box has 6 GB of RAM and a variant of the program that does not make use of SQLite runs with less than 512MB.

After inspecting with VisualVM I found out that the instances of SafeStmtPtr were through the roof.

Simply closing the Statement calling close() solved my issue. I believe you should also be able to set them to automatically close on complete, although I haven't tested this.

I assume not closing the statement leaves some memory out of the JVM to remain allocated and when the SafeStmtPtr is collected the memory is leaked. Although this is a wild guess and I don't really know about JDBC's or SQLite's internals.

[jdev-2020]

Can you share the code you used?

Try using try-with-resources to have your statements always automatically closed.
https://stackoverflow.com/questions/22671697/try-try-with-resources-and-connection-statement-and-resultset-closing

[noahboegli]

On my end it was less practical to use a try-with-resources due to a catch and error forwarding nightmare, there was a close() call in the catch but not if the statement was successful.

But the JavaDoc for Statement/Prepared's close method is confusing

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.

So technically, while not a good practice, it should not leak memory if not done.

I have no proof of causality, but adding the close no matter what the outcome of the execution eliminated the memory leak.

Example based on the code that caused the leak. It's not ideal but it's the code I have to work with. The try-catch is removed for clarity.

// columns is a String[], value is Map<String, String>, query is String 
PreparedStatement pStmt = connection.prepareStatement(query);
for (int i = 1; i <= columns.length; ++i) {
    pStmt.setString(i, this.values.get(columns[i - 1]));
}
pStmt.execute();

// This line fixes it
pStmt.close();

To give a bit of context, this code is called in average 270 per file we process and we process around 4000 per run. A run lasts 8 hours. So over the course of those 8 hours, a little bit over a million times. The leak will likely not be noticeable for normal loads and only becomes a problem in large scale situations.

[jeseibel]

I can confirm that try-resource blocks (specifically around statements and ResultSets) are necessary to prevent native code memory leaks. Java never went over the max heap allocation in my testing, however in Task Manager I saw the JVM's total memory steadily rise as queries were run.

Here is a commit I did that fixed the memory leak for my project:
https://gitlab.com/jeseibel/distant-horizons-core/-/commit/b38b33e87e0bc25837220046767d732dba4e0053

[jdev-2020]

But the JavaDoc for Statement/Prepared's close method is confusing

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.

The question is: when are the statement objects closed?
Was their close method called by the time of the memory measurement?

[noahboegli]

Based on the observation that it's not Statement objects remaining in memory but SafeStmtPtr objects, I assume the Statement object is collected and leaves the SafeStmtPtr that should normally be properly "destroyed" when closing the Statement

If you look at the Statement class of the jTDS driver (which is used in the same codebase I have sqlite in, and does not leak even without proper closing):

protected void finalize() throws Throwable {
    super.finalize();
    try {
        close();
    } catch (SQLException e) {
        // Ignore errors
    }
}

I was not able to find such usage of the finalize() in the sqlite driver. I've only searched in the github web UI so if I missed it my bad.

However since finalization is deprecated in release 18 through JEP 421 I don't know what would be the proper way of cleaning underlying resources upon garbage collection of the Statement objects. The documentation mentions the Cleaner class but I've never actually used it.

The JEP 421 also mentions the use of try-with-resources and all others mechanisms of properly handling closable resources and it should be used, but it does not address the memory leak.

What's interesting is that I've seen numerous example in internal codebases but also other projects where JDBC statements are not closed (the one I talk here being one of them), and the documentation does not seem to state that it's as important as properly closing filesystem I/O for example. In fact back when I had a Java course we were never told about the need to close JDBC statements.

[hbobenicio]

Connection and Statement are AutoCloseable. So we should really care about closing them in order to avoid leaks (ideally with try-with-resources).

Most of this confusion about not closing Statements really comes mostly from examples in the docs and code that completely forget to close them (like in readme.md and in demo/Sample.java).

I strongly believe we should update every example in the project to also correctly close Statements (not only Connections) so then they would not induce or mislead into leaking issues.

[gotson]

Most of this confusion about not closing Statements really comes mostly from examples in the docs and code that completely forget to close them (like in readme.md and in demo/Sample.java).

I strongly believe we should update every example in the project to also correctly close Statements (not only Connections) so then they would not induce or mislead into leaking issues.

as always, PRs are welcome :)

[hbobenicio]

Submitted PR #1073 .
What do you think?

[gotson]

Submitted PR #1073 . What do you think?

will have a look when time allows, thanks !

[github-actions]

🎉 This issue has been resolved in 3.45.2.0 (Release Notes)