Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Custom Oracle Batcher to allow batch updates for versioned data

Description

We have developed a custom Oracle Batcher which allows batching versioned data. The Oracle JDBC driver does not return update counts when using the standard JDBC 2.0 batching mechanism, however the proprietary Oracle batching mechanism allows obtaining the total batch row update count. The update counts are absolutely necessary to detect stale updates.

Although it is not exactly the same, the total row update count is actually enough information to be able to batch versioned data and still detect stale updates.

We'd like to contribute the attached files. They have a compile time dependency on Oracle JDBC. If this is not acceptable, it could be easily solved by using reflection.

Another Batcher is provided for when the Oracle connection is being managed through c3p0 (a common deployment scenario). This has a compile time dependency on c3p0.

A few "dirty" tricks were necessary to pull this off without patching other classes. Specifically, it was necessary to override Java private semantics to obtain BasicExpectation.expectedRowCount. This could be easily solved by adding an accessor method to the Expectation interface.

There is one issue which we are not completely sure of, however so far we have not found any problems. When the Expectation is NONE, there is no way to check whether the total row count is correct or not, even if other batched updates do have expectations with expected row counts. Our understanding is that actually, since batching requires all statements to be of the same type (since the same PreparedStatement / CallableStatement is being used), then either ALL expectations will be NONE, or all will have an expected row count. We'd welcome comments from the Hibernate team. This could also be probably handled better by improving the Expectation interface.

Oracle JDBC docs that explain the Oracle batching model: http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/oraperf.htm#i1059054

As expected, implementing this solution has resulted in drastical improvement in batch processing.

Attachments

6

Activity

Harsh DadhichNovember 22, 2018 at 9:15 AM

@Manuel Dominguez Sarmiento: I ran into a similar issue recently. While it resolved after upgrading JDBC driver to 12.1 for 1 use case. I resurfaced missing updates issue again (Hibernate should have thrown StaleStateException but it did not)

My setup -
OJDBC Driver : 12.1.0.2
Database Version : 12.1

have you seen anything like this?

Manuel Dominguez SarmientoApril 18, 2014 at 10:10 PM

It seems the 12.1 drivers deal with this issue as long as they are used with a 12c database instance. Testing the 12.1 drivers against a 11gR2 database yields no improvements over older driver releases. See the following post:

https://community.oracle.com/thread/3542899

Manuel Dominguez SarmientoJanuary 5, 2014 at 5:21 PM

I see that I can no longer attach files to this issue, since it's now closed. Please see the following URL for the latest Oracle JDBC driver docs:
http://docs.oracle.com/cd/E16655_01/java.121/e17657.pdf

Manuel Dominguez SarmientoJanuary 5, 2014 at 5:19 PM

For what it's worth, the newest Oracle JDBC drivers (12.1.0.1.0) no longer need dealing with this issue, since the proprietary Oracle batching model has been deprecated in favor of the standard JDBC batch update API. The new implementation correctly returns the update counts for the standard batch updates, so this should no longer be an issue. We've tested this and it seems to work fine.

Take into account that 12.1 drivers should work with 11g and 10g databases just fine. For older Oracle DB versions, you should check the JDBC driver compatibility matrix provided by Oracle.

See section 21 of the attached Oracle JDBC driver docs for version 12.1.0.1.0:
Note: Starting from Oracle Database 12c Release 1 (12.1), Oracle update batching is deprecated. Oracle recommends that you use standard JDBC batching instead of Oracle update batching.

Manuel Dominguez SarmientoNovember 16, 2011 at 10:57 PM

Please add it to the wiki, at least. We've put significant work into this and would appreciate this being made more visible for fellow Oracle users.

Some observations:

1) Cannot these classes be packaged in a companion hibernate-oracle.jar ? I understand that you do not generally rely on driver-specific implementation classes, but given that Oracle is used by so many people, I guess it's worth it. The separate packaging approach is used by many frameworks (e.g. Quartz)

2) We tried to migrate our apps to Hibernate 4.x but found that there is no equivalent hook in order to implement a custom batching strategy. Is this being addressed?

3) This should probably be filed as a separate issue, but while debugging this custom batching implementation, we noticed that internal state should be reset/cleared/cleaned up on closeStatements() as well as in abortBatch(). Our latest implementation takes this into account. However, BatchingBatcher (which is the default everyone uses) does not clean up its internal state in these cases, which may cause strange problems in some edge cases. The following fields are at risk:

private int batchSize;
private Expectation[] expectations;

Rejected

Details

Assignee

Reporter

Original estimate

Time tracking

No time logged0.5h remaining

Components

Priority

Created June 27, 2008 at 12:23 AM
Updated November 22, 2018 at 9:15 AM
Resolved November 16, 2011 at 10:31 PM