Hibernate saves stale data with hibernate.jdbc.batch_versioned_data

Question

Environment

Hibernate 4.2

ojdbc6 - Oracle 11.2.0.3.0 JDBC 4.0

Oracle Database 11g

The issue

We followed many recommendations to configure our Hibernate batching the following way:

<property name="hibernate.jdbc.batch_size">100</property>
<property name="hibernate.order_inserts">true</property>
<property name="hibernate.order_updates">true</property>
<property name="hibernate.jdbc.batch_versioned_data">true</property>

We checked our logs, and we saw that generated SQL statements are batched. However, if two transactions modify the same versioned entity rows concurrently, Hibernate will successfully commit both of them, resulting in loosing conflicting updates in the transaction that committed last (non-conflicting data are saved in both transactions, thus the last transaction leaves the database in an inconsistent state).

Surprisingly, there is very little documentation on this behavior. Hibernate official documentation says:

hibernate.jdbc.batch_versioned_data

Set this property to true if your JDBC driver returns correct row counts from executeBatch(). It is usually safe to turn this option on. Hibernate will then use batched DML for automatically versioned data. Defaults to false.

Usually safe? We almost sent this to production before noticing that the entire versioning is broken.

We googled out a blog posted five years ago that describes this weirdness; apparently Hibernate has not done anything regarding this for a long time.

Is there any reason why Hibernate behaves like this? It gets the information from the jdbc driver that the count of updated rows is unknown, why does it not throw an exception to indicate it, but rather leaves the impression that version check has passed successfully?

Answer 1

The oracle driver should return correct row counts. I will be surprised if this is not the case. Were you able to confirm that the results from the driver is correct? You can turn on Hibernate logging to check this.

Couple of things to check:

1. Log the actual SQL being sent to the DB and check to see that the version column is mentioned in the where clause. Not sure if the SQLs are logged by Hibernate logging with batching on, you might have to resort to a different way of logging the SQLs then (e.g., p6spy)

2. If the row counts are being returned correctly during concurrent updates, then the application is working fine. Confirm this by checking that the values of the version column is updated corrected.

Update According to the following link, this issue has been present with the Oracle driver until 11g and fixed in version 12c

https://hibernate.atlassian.net/browse/HHH-3360

For prior Oracle versions there is some additional information that should be useful, i.e., a custom solution is provided.

Additional resources: https://hibernate.atlassian.net/browse/HHH-5070