Memory leak with createStatement?

[knightowl]

I may be using the createStatement function incorrectly, however I have
a function that does the following:

I run the following routine 200,000 times and there is a memory leak
that therein. I take the createStatement() out of there and no memory
leak.

---------------------------------------------------------

try
{
s = connection.createStatement();
connection.setAutoCommit(false);
s.executeUpdate("LOCK TABLES counters WRITE;");
s.executeUpdate("UNLOCK TABLES;");
}
catch (SQLException e)
{
System.out.println(e);
try
{
connection.rollback();
}
catch (SQLException ex)
{
System.out.println("THere was a probem rolling back the
transaciton");
ex.printStackTrace();
}
}

finally
{
p.idleConnection(connection);
pm = null;
connection = null;
rs = null;
update = null;
select = null;
p = null;
}

 

[Lee Fesperman]

I may be using the createStatement function incorrectly, however I have
a function that does the following:

I run the following routine 200,000 times and there is a memory leak
that therein. I take the createStatement() out of there and no memory
leak.

---------------------------------------------------------

try
{
s = connection.createStatement();
connection.setAutoCommit(false);
s.executeUpdate("LOCK TABLES counters WRITE;");
s.executeUpdate("UNLOCK TABLES;");
}
catch (SQLException e)
{
System.out.println(e);
try
{
connection.rollback();
}
catch (SQLException ex)
{
System.out.println("THere was a probem rolling back the
transaciton");
ex.printStackTrace();
}
}

finally
{
p.idleConnection(connection);
pm = null;
connection = null;
rs = null;
update = null;
select = null;
p = null;
}

You need to close the statement -- s.close(). Actually, it is good practice to close all
objects created from a JDBC driver ... in a finally block.

We added a finalizer to our implementation of statement because an evaluator did the
same thing and complained that our driver didn't work. We decided to take the hit rather
that lose prospects because of their dumb code. They rarely tell you why they passed on
the product ;^)

 

[Wiseguy]

I may be using the createStatement function incorrectly, however I have
a function that does the following:

I run the following routine 200,000 times and there is a memory leak
that therein. I take the createStatement() out of there and no memory
leak.

You turned autocommit off. Where is your commit? Remember that without
it you are essentially queuing up bunches of transactions.

AS a general rule, I either commit my transactions within the method
where SQL is called, or make that method throw an exception to the caller.
This helps me to remember which operations are atomic within the application.

And do you REALLY want to queue up 200,000 SQL transactions without a commit?

 

[knightowl]

You need to close the statement -- s.close(). Actually, it is good
practice to close all

objects created from a JDBC driver ... in a finally block.

Lee...You're a life saver. I didn't even think about closing the
statement. I written by own Database Connection Pooling object, and I
included creating statements when those pool enties are created. I am
now closing them in a finalize method.

We added a finalizer to our implementation of statement because an

evaluator did the same thing and complained that our driver didn't
work. We decided to take the hit rather that lose prospects because of
their dumb code. They rarely tell you why they passed on the product
;^)

Also, email me directly regarding your database product. How does it
compare to mysql?

HFC

 

[knightowl]

You turned autocommit off. Where is your commit? Remember that without
it you are essentially queuing up bunches of transactions.

AS a general rule, I either commit my transactions within the method
where SQL is called, or make that method throw an exception to the caller.
This helps me to remember which operations are atomic within the application.

And do you REALLY want to queue up 200,000 SQL transactions without a

commit?

Yes I did turn it off. I do have a commit further down the code, I cut
this code out. The .close() has fixed my problem, and further more I
moved the createStatement into my connection polling object.

HFC

 

[Lee Fesperman]

Lee...You're a life saver. I didn't even think about closing the
statement. I written by own Database Connection Pooling object, and I
included creating statements when those pool enties are created. I am
now closing them in a finalize method.

Glad to help ... that's what we're here for. I do have some additional comments.

I assumed there was more going on here because of your finally block which had little
relationship to the prior code. That's why I didn't suggest that you simply reuse the
statement object.

I assume that you are wrapping the JDBC objects. I think you really need to. You should
only use the finalizer for the situation I discussed below, though you may not need it.
When you return the connection to the pool, you should close all JDBC objects because
they can tie up db server resources. Obviously, you won't close the connection and
perhaps not even the statements since you mentioned creating statements when pool
entries are created. However, you should close any resultsets.

evaluator did the same thing and complained that our driver didn't
work. We decided to take the hit rather that lose prospects because of
their dumb code. They rarely tell you why they passed on the product
;^)

Also, email me directly regarding your database product. How does it
compare to mysql?

Thanks. I'll do that.

 

[Lee Fesperman]

Hi HFC,

Info on our product - FirstSQL/J, can be found at http://www.firstsql.com.

We think it compares well with MySQL. It is a robust and standard implementation. Our
SQL is SQL92 with extensions to support objects. It also has strong support for J2EE,
including XA (which MySQL doesn't support).

FirstSQL/J is written in pure Java, thus providing better integration with Java
applications. We have both a server version (like MySQL) -- the Enterprise Edition, and
an embedded version -- the Professional Edition, which runs on the same JVM as your
application. Both editions are fully compatible to the physical database level. Java
gives us wider portability than MySQL. We support using Java objects in the database,
allowing you to call Java methods in SQL. Java is also our stored procedure language.

I will need more details on your application to better answer your query.

Thanks for your interest,

Lee Fesperman, FFE Software, Inc.

 

[knightowl]

I assumed there was more going on here because of your finally block
which had little

relationship to the prior code. That's why I didn't suggest that you simply reuse the
statement object.

It wasn't until I realized that there was something wrong with either
the createStatement function or that I use using it wrong, that I
placed the createStatements in the database pool object.

I assume that you are wrapping the JDBC objects. I think you really need to. You should
only use the finalizer for the situation I discussed below, though

you may not need it.

I have a wrapper setup for all the JDC, although I must admit at this
time I don't have any ResultSet().closes. I will do that.

When you return the connection to the pool, you should close all JDBC objects because
they can tie up db server resources. Obviously, you won't close the connection and
perhaps not even the statements since you mentioned creating statements when pool
entries are created. However, you should close any resultsets.

10-4

 

[Lee Fesperman]

Hi HFC,

Info on our product - FirstSQL/J, can be found at http://www.firstsql.com

My apologies, everyone. I did not mean to send this to the group (blush).

 

[Dimitri Maziuk]

knightowl sez:

It wasn't until I realized that there was something wrong with either
the createStatement function or that I use using it wrong, that I
placed the createStatements in the database pool object.

you may not need it.

I have a wrapper setup for all the JDC, although I must admit at this
time I don't have any ResultSet().closes. I will do that.

Statement.close() will close the result set associated with the
statement (see API docs for ResultSet.close()).

(At least) on Oracle server-side cursors are closed on
Statement.close(), not ResultSet.close(). The latter presumably
only frees the local buffer where data is stored. I close my
result sets explicitly anyway, just to be on the safe side.

Dima