Wednesday, August 4, 2010

hibernate.jdbc.batch_versioned_data can't be set to TRUE for Oracle JDBC driver

Duo to Oracle's popularity, we originally assumed it should be safe to turn on this flag for Oracle JDBC drivers until we saw a Unit testing exception.
Basically the Unit test tried to update a POJO in an optimistic way. It should have failed because the same POJO was just updated by another user before it. However Hibernate (3.3) just silently returned without resulting in any database update.
I traced the code to Hibernate's method checkBatched() in class Expectations$BasicExpections. The rowCounts Oracle returned turns out to be always -2 (Statement.SUCCESS_NO_INFO) for all its version 9i,10g and 11g JDBC drivers.

This returned value "-2" was finally verified by Oracle's JDBC Developer's Guide titled "Update Counts in the Oracle Implementation of Standard Batching" in Chapter 23 "Performance Extensions".
Basically it says:
  • For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown.
  • For a generic statement batch, the array contains the actual update counts indicating the number of rows affected by each operation. The actual update counts can be provided only in the case of generic statements in the Oracle implementation of standard batching.
  • For a callable statement batch, the server always returns the value 1 as the update count, irrespective of the number rows affected by each operation.
The different returned values for different statements can be explained by the following Oracle implementation details:

In Oracle JDBC applications, update batching is intended for use with prepared statements that are being processed repeatedly with different sets of bind values.
The Oracle implementation of standard update batching does not implement true batching for generic statements and callable statements. Even though Oracle JDBC supports the use of standard batching for Statement and CallableStatement objects, you are unlikely to see performance improvement.

But I still have a hard time to believe that the back-end powerful Oracle database even couldn't know  the number of rows affected by each preparedStatement in a batch.

I am equally disappointed with the way Hibernate is handling Statement.SUCCESS_NO_INFO(-2).
This is how it happens: Hibernate uses preparedStatement for batch updates. Because the where clause in the update sql used a staled version number, Oracle just didn't update anything and returned successfully. Finally Hibernate still interprets Statement.SUCCESS_NO_INFO(-2) just as an successful update instead of throwing any optimistic exception. As you know this is not acceptable.

I know Hibernate is in a dilemma in this case because when Oracle returns Statement.SUCCESS_NO_INFO(-2) it either means some rows were updated or no row was udpated.
But I still like Hibernate to throw some exception to remind users of the updating ambiguity.

The good news is batch inserting is still safe as because returning Statement.SUCCESS_NO_INFO(-2) must mean a row was successfully inserted into the database.
So you may have to create a separate datasource for batch inserting only.


  1. Hello Yong,

    Sorry to post in a almost 2 year old thread.
    But i'm having the exact problem you're describing. Is there an elegant solution for this problem. A way you can actually throw an error if someone else modified the row since being retrieved from the database?

    Thanks in advance

  2. I am still facing the same issue with Update by using the Oracle JDBC Driver. Is there any solution that we can provide if oracle return -2 to indicate that the update didn't go through ?