Hibernate PostgreSQL Batch Update Problem

Overview

A JavaEE 5 project using Hibernate 3.2 with Annotations and EntityManager support and PostgreSQL 8.2 is being developed. For fast data inserts, batch updates are being used. A snippet of the update code looks as follows:

	    int count = 0;

            for (int j = 0; j < data.size(); j++) {

                UnitData ud = data.get(j);

                em.persist(ud);

	        count++;

                if (j != 0 && (j % 5000) == 0) {

                    em.flush();

                    em.clear();

	            count = 0;

                }

            }

            if (count > 0) {

                em.flush();

                em.clear();

            }

The UnitData code is correct.

Note: Flushing every 5000 elements is usually not the greatest idea, but this code was written for stress testing the system.

Problem

When persisting the data, a org.hibernate.exception.GenericJDBCException is sometimes thrown. A more complete message is org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update

This is a tricky error. It only seems to occur under heavy load, and the heavier the load, the more likely it is to occur. The batch update error does not always occur in the same place. A stack trace shows that, for example, sometimes there was a problem inserting row 6, sometimes row 0, sometimes row 58 etc. Furthermore, looking through the stack trace seems to indicate the problem is at the JDBC level, pointing to a defective JDBC Driver

Another strange feature of the problem is that it does not affect all persistent classes. Although not shown in the code snippet above, another class is also regularly persisted to the database and it has never produced the this error.

Solution

Upgrade from Hibernate 3.2/3.3 to Hibernate 3.4. I’m currently using Hibernate-3.4.CR1 which is currently in development but fixes the problem (and I hope doesn’t introduce any others). Other steps I’ve taken which have not worked include

1. Upgrading the PostgreSQL driver

2. Disabling batch inserts for Hibernate in persistence.xml

3. Altering the code to manually flush after every persist() call

4. Changing the data types in the persisted Entity bean from byte[] to something else since I’ve picked up a problem with @Lob and byte[] support in Hibernate.

5. Changed from java.util.Date to java.sql.Timestamp thinking that the problem could somehow be related to this as this was the only field aside from the primary key that was changing.

The solution became evident when finally, I switched from using Hibernate to Toplink as my JPA Provider and the problem stopped. The problem has not re-surfaces with Hibernate 3.4.

Other

The problem is tricky. After much googling last night, I came across just one post – a bug report filed with Hibernate — that covers this problem – or something very similar. If I recall, the poster revoked the bug report as the problem could not be accurately simulated.