Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

closing PreparedStatement does not close ResultSet [JDBC634] #664

Closed
firebird-automations opened this issue Nov 11, 2020 · 8 comments
Closed

closing PreparedStatement does not close ResultSet [JDBC634] #664

firebird-automations opened this issue Nov 11, 2020 · 8 comments
Assignees
@mrotteveel
Labels
affect-version: Jaybird 3.0.9 component: jdbc driver priority: major resolution: cannot reproduce type: bug

Comments

@firebird-automations
Copy link

Submitted by: Martin Sulak (martinsulak)

Closing PreparedStatement either explicitly by close method or by using AutoCloseable interface probably does not close underlying ResultSet.
If ResultSet is not explicitly closed, transaction number freeze, indicating resource leak
It worked fine with JayBird 2.2.3.

illustration :

int getRow ( Connection connection, int b ) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
// rs.close(); is required here or transaction counter stops but rs should be closed automatically when execution leaves try block
return rs.getInt(1);
}
}

or:

int getRow ( Connection connection, int b ) throws SQLException {
PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
int val = rs.getInt(1);
// rs.close(); is required here or transaction counter stops but rs should be closed in ps.close
ps.close();
return val;
}
@firebird-automations
Copy link
Author

Modified by: Martin Sulak (martinsulak)

description: When PreparedStatement is declared in try with resources and its ResultSet is not explicitly closed, transaction number freeze, indicating resource leak

illustration :

int getRow ( Connection connection, int b ) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
// rs.close(); is required here or transaction counter stops but is should be close automatically when execution leaves try block
return rs.getInt(1);
}
}

=>

When PreparedStatement is declared in try with resources and its ResultSet is not explicitly closed, transaction number freeze, indicating resource leak

illustration :

int getRow ( Connection connection, int b ) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
// rs.close(); is required here or transaction counter stops but is should be close automatically when execution leaves try block
return rs.getInt(1);
}
}

AutoCloseable interface on PreparedStatement works fine in JayBird 2.2.3.

@firebird-automations
Copy link
Author

Modified by: Martin Sulak (martinsulak)

description: When PreparedStatement is declared in try with resources and its ResultSet is not explicitly closed, transaction number freeze, indicating resource leak

illustration :

int getRow ( Connection connection, int b ) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
// rs.close(); is required here or transaction counter stops but is should be close automatically when execution leaves try block
return rs.getInt(1);
}
}

AutoCloseable interface on PreparedStatement works fine in JayBird 2.2.3.

=>

Closing PreparedStatement either explicitly by close method or by using AutoCloseable interface probably does not close underlying ResultSet.
If ResultSet is not explicitly closed, transaction number freeze, indicating resource leak
It worked fine with JayBird 2.2.3.

illustration :

int getRow ( Connection connection, int b ) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
// rs.close(); is required here or transaction counter stops but is should be close automatically when execution leaves try block
return rs.getInt(1);
}
}

or:

int getRow ( Connection connection, int b ) throws SQLException {
PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
int val = rs.getInt(1);
// rs.close(); is required here or transaction counter stops but is should be close automatically when execution leaves try block
ps.close();
return val;
}

summary: autoclosing PreparedStatement does not close ResultSet => closing PreparedStatement does not close ResultSet

@firebird-automations
Copy link
Author

Modified by: Martin Sulak (martinsulak)

description: Closing PreparedStatement either explicitly by close method or by using AutoCloseable interface probably does not close underlying ResultSet.
If ResultSet is not explicitly closed, transaction number freeze, indicating resource leak
It worked fine with JayBird 2.2.3.

illustration :

int getRow ( Connection connection, int b ) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
// rs.close(); is required here or transaction counter stops but is should be close automatically when execution leaves try block
return rs.getInt(1);
}
}

or:

int getRow ( Connection connection, int b ) throws SQLException {
PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
int val = rs.getInt(1);
// rs.close(); is required here or transaction counter stops but is should be close automatically when execution leaves try block
ps.close();
return val;
}

=>

Closing PreparedStatement either explicitly by close method or by using AutoCloseable interface probably does not close underlying ResultSet.
If ResultSet is not explicitly closed, transaction number freeze, indicating resource leak
It worked fine with JayBird 2.2.3.

illustration :

int getRow ( Connection connection, int b ) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
// rs.close(); is required here or transaction counter stops but is should be closed automatically when execution leaves try block
return rs.getInt(1);
}
}

or:

int getRow ( Connection connection, int b ) throws SQLException {
PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
int val = rs.getInt(1);
// rs.close(); is required here or transaction counter stops but is should be closed in ps.close
ps.close();
return val;
}

@firebird-automations
Copy link
Author

Modified by: Martin Sulak (martinsulak)

description: Closing PreparedStatement either explicitly by close method or by using AutoCloseable interface probably does not close underlying ResultSet.
If ResultSet is not explicitly closed, transaction number freeze, indicating resource leak
It worked fine with JayBird 2.2.3.

illustration :

int getRow ( Connection connection, int b ) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
// rs.close(); is required here or transaction counter stops but is should be closed automatically when execution leaves try block
return rs.getInt(1);
}
}

or:

int getRow ( Connection connection, int b ) throws SQLException {
PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
int val = rs.getInt(1);
// rs.close(); is required here or transaction counter stops but is should be closed in ps.close
ps.close();
return val;
}

=>

Closing PreparedStatement either explicitly by close method or by using AutoCloseable interface probably does not close underlying ResultSet.
If ResultSet is not explicitly closed, transaction number freeze, indicating resource leak
It worked fine with JayBird 2.2.3.

illustration :

int getRow ( Connection connection, int b ) throws SQLException {
try (PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
// rs.close(); is required here or transaction counter stops but rs should be closed automatically when execution leaves try block
return rs.getInt(1);
}
}

or:

int getRow ( Connection connection, int b ) throws SQLException {
PreparedStatement ps = connection.prepareStatement("select A from MYTAB where B=?")) {
ps.setInt(1, b);
ResultSet rs = ps.executeQuery();
rs.next();
int val = rs.getInt(1);
// rs.close(); is required here or transaction counter stops but rs should be closed in ps.close
ps.close();
return val;
}

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

I can't reproduce this. Please provide a minimal, complete and verifiable example. Especially show how you verify the transaction count, and how you create the connection. For example, the following code will show that transactions are ended properly, and that the result set is closed:

@Test
public void checkResourceLeak\(\) throws Exception \{
    try \(Connection connection = getConnectionViaDriverManager\(\)\) \{
        printTransactionInfo\(connection\);

        for \(int i = 0; i < 10; i\+\+\) \{
            ResultSet rs;
            try \(PreparedStatement pstmt = connection\.prepareStatement\("select \* from rdb$relations"\)\) \{
                rs = pstmt\.executeQuery\(\);
                rs\.next\(\);
            \}

            System\.out\.println\("is closed: " \+ rs\.isClosed\(\)\);
        \}

        printTransactionInfo\(connection\);
    \}
\}

private static void printTransactionInfo\(Connection connection\) throws SQLException  \{
    StatisticsManager\.DatabaseTransactionInfo transactionInfo =
            FBStatisticsManager\.getDatabaseTransactionInfo\(connection\);

    System\.out\.printf\("Oldest: %d%nOldest Active: %d%nOldest Snapshot: %d%nNext: %d%nActive Count: %d%n",
            transactionInfo\.getOldestTransaction\(\), transactionInfo\.getOldestActiveTransaction\(\),
            transactionInfo\.getOldestSnapshotTransaction\(\), transactionInfo\.getNextTransaction\(\),
            transactionInfo\.getActiveTransactionCount\(\)\);
\}

(where getConnectionViaDriverManager() uses DriverManager.getConnection(String, Properties))

This prints - as expected:

Oldest: 1
Oldest Active: 0
Oldest Snapshot: 0
Next: 0
Active Count: 0
is closed: true
is closed: true
is closed: true
is closed: true
is closed: true
is closed: true
is closed: true
is closed: true
is closed: true
is closed: true
Oldest: 10
Oldest Active: 11
Oldest Snapshot: 11
Next: 11
Active Count: 0

Enabling debug logging also shows that the transaction is committed as expected.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

status: Open [ 1 ] => Closed [ 6 ]

resolution: Cannot Reproduce [ 5 ]

@firebird-automations
Copy link
Author

Commented by: Martin Sulak (martinsulak)

Hello,
For transaction count I am using Oldest transaction and Next transaction from fbstat -header.
What should minimal example contain a where can I it post ?
I think it should contain compiled java classes, jdbc driver itself and database file (or maybe rdb$ tables should suffice).
Or just java source here in comment ?

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

You can add a Java source file as an attachment. And the minimal example should contain everything I need to run, and verify things (either as a set of instructions, or as part of the Java program). However, if you currently use gstat -h to check the values, I would recommend that you also try what I do in printTransactionInfo(Connection), IIRC the statistics in the database header page are not always updated immediately, so can lag behind.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@mrotteveel mrotteveel

Labels
affect-version: Jaybird 3.0.9 component: jdbc driver priority: major resolution: cannot reproduce type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@mrotteveel @firebird-automations