[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3360) Custom Oracle Batcher to allow batch updates for versioned data

[Fabian Depry (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=33652#action_33652 ] 

Fabian Depry commented on HHH-3360:
-----------------------------------

Hi,

we ran into the problem described in this issue (Oracle not using the standard batch model and therefore the versioned entity unable to use batching). We are using Oracle 10.2.4.4 and the latest OJDBC drivers (11.1.0.7.0).

We tried the solution provided here but ran into two issues:

1. The OracleBatchingBatcher uses a private array of expectations; it is initialized in the constructor using the JDBC batch size for the length of the array. But the addToBatch() method does not check the size of that array and never "flushes" the batch. When we tried to use the OracleBatchingBatcher, we had a ArrayOutOfBoundException on the expectation array because Hibernate was calling too many times the addToBatch() methods. If you look at the original BatchingBatcher, it does check for the size of the batch before adding a new statement to it and if the maximum length is reached, it calls doExecuteBatch(). This is also how we fixed our OracleBatchingBatcher, but I just don't understand how the class provided here can work without that fix.

2. We are using a web server (JBoss) and the prepared statement passed to the addToBatch() method is actually a JBoss wrapped prepared statement. It was simple enough to "unwrap" it, but we were surprised to find out that the unwrapped prepared statement is still not an OraclePreparedStatement. It is actually an Oracle wrapped prepared statement, which in turn still doesn't wrap an OraclePreparedStatment. Anyway, we made it work but we had to access some private fields and methods from the OJDBC and that doesn't seem like a good thing. We were wondering if there was a better way of doing that. We realized this question should be asked on the JBoss forums (and it will), but since it's related, we figured we would post it here too.

Thank you!

> Custom Oracle Batcher to allow batch updates for versioned data
> ---------------------------------------------------------------
>
>                 Key: HHH-3360
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360
>             Project: Hibernate Core
>          Issue Type: Improvement
>          Components: core
>    Affects Versions: 3.2.0.ga, 3.2.1, 3.2.2, 3.2.3, 3.2.4, 3.2.4.sp1, 3.2.5, 3.2.6, 3.3.0.CR1
>         Environment: Oracle 10g R1, 10g R2, 11g R1 (have not tried previous Oracle versions), 11g R1 drivers (older drivers should also work)
>            Reporter: Manuel Dominguez Sarmiento
>            Priority: Minor
>         Attachments: C3P0OracleBatchingBatcher.java, C3P0OracleBatchingBatcherFactory.java, OracleBatchingBatcher.java, OracleBatchingBatcher.java, OracleBatchingBatcherFactory.java
>
>   Original Estimate: 30 minutes
>  Remaining Estimate: 30 minutes
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira