CODEWITHSUNDEEP
javajdbchsqldb
user967710
user967710

Reputation: 2007

Hsqldb - a possible prepare statement memory leak

I am using hsqldb version 2.3.1 or 2.3.2 (found problem in both).

I have code in which I preform a lot of inserts to the database, and I clean all resources I possibly can - but I've noticed that calling execute on statements, causes memory to never go away - even though, I close the prepared-statement, the connection etc...

Here is code that you can run on your machine and validate (run it on java default heap size and the OutOfMemory should appear in less than 5 minutes):

Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:a");
PreparedStatement ps = c.prepareStatement("set database sql syntax ora true");
ps.execute();
ps.close();

ps = c.prepareStatement("create table t (x varchar2(1000 char))");
ps.execute();
ps.close();
c.close();
String str = "abcdefghijklmnopqrstuvwxyz";
str = str + str + str + str + str; //just a long string to rapid the outcome
String x = String.format("insert into t values('%s')",str);
for(int i=0;i<1000*1000*1000;i++){
   c = DriverManager.getConnection("jdbc:hsqldb:mem:a");
   ps = c.prepareStatement(x);
   ps.executeUpdate(); //if I remove this line, there is no memory leak
   ps.clearParameters();//I really try to clean every possible thing
   ps.clearWarnings();//really try
   ps.cancel();//cancel it even
   ps.close();//close it of course
   ps = null;//nullify to hint the GC
   c.rollback(); //Rollback so that memory isn't even used inside the hsqldb
   c.close();//close the connection even

   if(i%100000 == 0){//Every once in a while a report about heap condition
 System.gc();//perform gc
     System.out.println(Runtime.getRuntime().freeMemory()/1024/1024);//in MB
   }
 }

If you run the code, the periodic prints will print the avilable free memory - which will decrease gradually up until OutOfMemory.

As you can see, I tried closing, rollback, nullifying etc. - nothing seems to work.

Using JvisualVM it appears most of the memory is byte[].

If I am right and it is a bug, I am looking for a workaround. Any suggestions? Am I doing something wrong?

Thank you

Upvotes: 0

Views: 1262

Answers (1)

wumpz
wumpz

Reputation: 9141

Read here http://hsqldb.org/doc/guide/dbproperties-chapt.html about shutdown.

If not specified so, even if you are closing the last connection to an HSQL in memory database, the database itself is not destroyed and stays in memory. You are adding with every insert new rows in this database and it grows.

If the database would not stay, then you could never insert, because after you created your database and your table t you close the connection and then after a reopen, the table should be gone, shouldnt it.

You could force the shutdown of the database after the last connection is closed, if you open it for the first time with:

Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:a;shutdown=true");

But now you cannot insert anymore, because your table is not there after a new getConnection.

Another possibility to shutdown your database and free all the memory is the SQL command SHUTDOWN of HSQL:

c.createStatement().execute("SHUTDOWN");

If you are dealing with bigger data your first option should be to create a HSQL database within the filesystem with disc-based CACHED tables. Then its memory footprint would not grow with the data like it does within memory.

Upvotes: 3

Related Questions