We recently faced a performance issue with the insertion of a few thousand records in the database. Instead of the expected few seconds, the insertions took more than a minute! Checking the logs we realised that the problem was caused by the number of statements sent to the server than to any other bottleneck – server load, network bandwidth or other lack of resources.

The solution was to enable batch inserts and we first tried doing it by just setting the attribute hibernate.jdbc.batch_size to a relatively high number, 100 in our case. Looking at the logs though that did not have any result, the number of insert statements sent to the database was still the same as before.

Then, we noticed that all our statements with the database had the autocommit set to true; that was disabling by default the creation of batch statements, therefore we had to change it to false; instead of the simple statement
try {
  for (MyObject object : objectList){
     getSession().saveOrUpdate(object);
  }
}
catch (.. ..){
...
}
we had to code the following
  try {
  autoCommit = getSession().connection().getAutoCommit();
  getSession().connection().setAutoCommit(false);
  Transaction tx = getSession().beginTransaction();
  for (MyObject object : objectList){
     getSession().saveOrUpdate(object);
  }
  tx.commit();
  }
  catch (.. ..){
  ...
  }

Still, the behaviour persisted! The number of insert statements sent to the database server was related to the number of objects to saveorupdate!

Checking again the documentation – and occasionally the source code of hibernate – we realised that batch inserts were disabled if the id column of the table was of type Identity. At first we thought this was not a problem as we are using MySql and the generator type is native! The reality is that native maps to Identity for MySql. Overall, I think the lesson learned is that if hinernate needs to get the value of the introduced record from the server, then batch inserts are disabled – even if you do not need those ids anymore!

Changing the generator to hilo with a corresponding table to hold the generated ids, we seemd to have solved the problem; the number of insert statements was divided by the batch_size parameter, specified above. Be careful: looking at the log4j logs of hibernate the number of Insert statements remains the same, but if you look at the mysql logs, then you see the truth!

Then, we faced the final problem: the batch inserts worked perfectly while running the jUnit test, failed completely when the same code was run in the web application.

The problem we faced was java.sql.SQLException: Can't call commit when autocommit=true

We thought of a problem with jBoss and the wrapping datasource! Changed it to DBCP and the problem remained! Then, by googling we found quotes mentioning a problem with autocommit and Tomcat. Setting the attribute relaxAutoCommit=true in the jdbc url we solved our problem.

jdbc:mysql://dbserver/database?rewriteBatchedStatements=true&relaxAutoCommit=true

The above setting works well with the DBCP pool, but raises the same exception as above when used with a jboss hibernate configuration file, e.g. mysql-ds.xml.