HSQL and Hibernate - Out Of Memory error on many Delete Statements

Question

We're using on our java project Hibernate 3.6.10.Final with HSQL 2.3.2. I have a reproducible Out of memory error.

When reading the heap dumps I see that the object that takes all the memory is org.hsqldb.Session and specifically in it rowActionList which contains hundreds of thousands of RowAction objects.

• All the RowAction objects are of type 2 which is a delete statement.
• The HSQL properties hsqldb.cache_rows and hsqldb.cache_size are set to default which I believe are 50,000 and 10,000 respectively.

Since asking the question we have discovered that this OOM occurs on the following scenario:

1. We run the application on a large set of data which causes a lot of insert statements (hundreds of thousands) and we shut down the application with an improper shutdown of the DB (we cannot properly shutdown because we have a very limited time to close down). This is the DB after the first run:

2. We then run the application a second time and I think that as soon as we open a hibernate session to the DB the memory starts to inflate in a manner of seconds even if the second run doesn't execute any delete statements. After the second run: We now believe this is caused by a checkpoint done by HSQLDB which occurs because of the improper shutdown.

Are we correct and if so why does the checkpoint process takes so much memory?

The delete statements I was talking about in the original question are most likely irrelevant:

• All these delete statements are done under the same hibernate session.

• For each delete we perform:

      transaction = session.beginTransaction();
      session.delete(file);
      transaction.commit();
  

Thanks

Answer 1

We finally have discovered the cause for the DELETE statements that were taking up all the memory. It turns out in our second run we were trying to truncate the tables created in the first run using TRUNCATE TABLE statement.

The TRUNCATE TABLE command is done in a single transaction and therefore the state prior to the truncate is saved in memory as is explained here : Truncating takes too much time hsqldb

When switching to TRUNCATE TABLE tableName AND COMMIT the OOM doesn't occur.