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

[Hibernate-JIRA] Created:...

[Hibernate-JIRA] Updated:...

Manuel Dominguez Sarmiento (JIRA)

Thursday, 26 June 2008 Thu, 26 Jun '08

5:24 p.m.

Custom Oracle Batcher to allow batch updates for versioned data --------------------------------------------------------------- Key: HHH-3360 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360 Project: Hibernate3 Issue Type: Improvement Components: core Affects Versions: 3.3.0.CR1, 3.2.6, 3.2.5, 3.2.4.sp1, 3.2.4, 3.2.3, 3.2.2, 3.2.1, 3.2.0.ga 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, OracleBatchingBatcherFactory.java 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#... 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

Show replies by date

Manuel Dominguez Sarmiento (JIRA)

Thursday, 26 June Thu, 26 Jun

5:24 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-3360) Custom Oracle Batcher to allow batch updates for versioned data

...

Custom Oracle Batcher to allow batch updates for versioned data --------------------------------------------------------------- Key: HHH-3360 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360 Project: Hibernate3 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, 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#... 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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira

Manuel Dominguez Sarmiento (JIRA)

6:27 p.m.

New subject: [Hibernate-JIRA] Updated: (HHH-3360) Custom Oracle Batcher to allow batch updates for versioned data

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] Manuel Dominguez Sarmiento updated HHH-3360: -------------------------------------------- Attachment: OracleBatchingBatcher.java The previously uploaded version of OracleBatchingBatcher.java was uploaded by mistake, it was not the final version. This attachment corrects an important bug regarding batch auto-flush by the Oracle JDBC driver.

...

Custom Oracle Batcher to allow batch updates for versioned data --------------------------------------------------------------- Key: HHH-3360 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360 Project: Hibernate3 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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

Manuel Dominguez Sarmiento (JIRA)

8:28 p.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] Manuel Dominguez Sarmiento commented on HHH-3360: ------------------------------------------------- There's a missing return statement in OracleBatchingBatcher.java .... } else if (expectation == Expectations.NONE) { if (log.isDebugEnabled()) { log.debug("Success of batch update cannot be verified"); } return; <---- HERE } .....

...

Custom Oracle Batcher to allow batch updates for versioned data --------------------------------------------------------------- Key: HHH-3360 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360 Project: Hibernate3 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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

Fabian Depry (JIRA)

Wednesday, 22 July Wed, 22 Jul

9:01 a.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] 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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

Manuel Dominguez Sarmiento (JIRA)

11:12 a.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] Manuel Dominguez Sarmiento commented on HHH-3360: ------------------------------------------------- Hi Fabian, I can vouch for this patch, it has been reliably working in our production servers for over a year without problems. I think what's going on is that having JBoss wrappers in the middle is bringing trouble into the equation. Please refer to the following link (see the second section, "Example of Oracle Update Batching"): http://www.oracle.com/technology/products/oracle9i/daily/jun07.html Explicit flushes are not required, except for the final flush which is performed in doExecuteBatch() - the reason is that the Oracle batching model automatically flushes pending operations on invocations to ps.executeUpdate(). This statement returns 0 while the queue is not full, but when the batch size is reached, pending operations are automatically flushed, and the returned value is then the total number of updated rows (combined value for all DML statements in the flushed batch). For this to work correctly, setExecuteBatch(int batchSize) must be invoked on the corresponding OraclePreparedStatement before the batch begins. This is where it gets tricky. Simply casting the PreparesStatement provided by Hibernate will only work if you are working directly with the Oracle JDBC driver. In our case we use C3P0 connection pooling which wraps Connections and PreparedStatements, but still provides a way to access the underlying "native" OraclePreparedStatement. This is why we have setExecuteBatch() as a protected template method which can be overridden by subclasses. C3P0OracleBatchingBatcher overrides this method (as well as sendBatch) in order to access the native Oracle classes. C3P0 requires doing this using reflection, so it's not pretty, but it works. My guess is that you need to figure out how JBoss is wrapping the PreparedStatements so that you can mirror what C3P0OracleBatchingBatcher is doing, but applying the same concept to JBoss. Hope this helps, - Manuel

...

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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

Fabian Depry (JIRA)

3:49 p.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] Fabian Depry commented on HHH-3360: ----------------------------------- Thank you for your reply. It makes sense. I was actually never able to get to the underlying OraclePreparedStatement from the JBoss wrapped prepared statement. The best I could get was an Oracle-specific class that also has the "setExecuteBatch(int)" and the "sendBatch()" methods so I could call those methods the same way it was done in the posted files. But I realize trying to use their proprietary class instead of the public OraclePreparedStatement is not an acceptable solution anyway. I already added a post in the JBoss forum on how to access the underlying prepared statement (http://www.jboss.org/index.html?module=bb&op=viewtopic&t=158861), but I don't have much hope since JBoss does provide a method to get the underlying prepared statement; it's just that it is not an OraclePreparedStatement like expected but some kind of Oracle-specific wrapper. Maybe I should ask the Oracle folks instead of the JBoss folks... Anyway, thanks again for the answer, that did help :-) Fabian

...

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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

Diego del Río (JIRA)

Wednesday, 29 July Wed, 29 Jul

3:10 p.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] Diego del Río commented on HHH-3360: ------------------------------------ Hi Fabian, see this post https://forum.hibernate.org/viewtopic.php?f=1&t=929931&p=2401306#... We developed a solution very similiar to the one posted here. Also have a look at how Spring manages the issue of accesing native jdbc interfaces at http://static.springframework.org/spring/docs/2.0.x/api/org/springframewo... We should include something very similar to NativeJdbcExtractor in our Batcher to make it generic and manage different jdbc object wrappers.

...

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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

Manuel Dominguez Sarmiento (JIRA)

Wednesday, 5 October Wed, 5 Oct

7:38 a.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] Manuel Dominguez Sarmiento commented on HHH-3360: ------------------------------------------------- This does not work as-is anymore for the Hibernate 4.x branch. Anyone know what the new hooks are to implement this same functionality?

...

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: C3P0OracleBatchingBatcherFactory.java, C3P0OracleBatchingBatcher.java, OracleBatchingBatcherFactory.java, OracleBatchingBatcher.java, OracleBatchingBatcher.java Original Estimate: 0.5h Remaining Estimate: 0.5h 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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

-- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

Manuel Dominguez Sarmiento (JIRA)

Wednesday, 16 November Wed, 16 Nov

10:51 a.m.

New subject: [Hibernate-JIRA] Updated: (HHH-3360) Custom Oracle Batcher to allow batch updates for versioned data

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] Manuel Dominguez Sarmiento updated HHH-3360: -------------------------------------------- Attachment: OracleBatchingBatcher.java This updated version (attached) accounts for possible cross-transaction issues that might be caused by aborting/cancelling a transaction. The original version did not reset its internal state in these scenarios.

...

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: C3P0OracleBatchingBatcherFactory.java, C3P0OracleBatchingBatcher.java, OracleBatchingBatcherFactory.java, OracleBatchingBatcher.java, OracleBatchingBatcher.java, OracleBatchingBatcher.java Original Estimate: 0.5h Remaining Estimate: 0.5h 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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

-- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

Steve Ebersole (JIRA)

2:32 p.m.

New subject: [Hibernate-JIRA] Closed: (HHH-3360) Custom Oracle Batcher to allow batch updates for versioned data

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] Steve Ebersole closed HHH-3360. ------------------------------- Resolution: Rejected Assignee: Steve Ebersole Nice work and good to have available for fellow Oracle users, but we do not incorporate solutions into the Hibernate codebase that rely on specific driver implementation classes as a general rule. I can totally see this as an article on the wiki though with these classes attached.

...

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 Assignee: Steve Ebersole Priority: Minor Attachments: C3P0OracleBatchingBatcherFactory.java, C3P0OracleBatchingBatcher.java, OracleBatchingBatcherFactory.java, OracleBatchingBatcher.java, OracleBatchingBatcher.java, OracleBatchingBatcher.java Original Estimate: 0.5h Remaining Estimate: 0.5h 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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

-- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

Manuel Dominguez Sarmiento (JIRA)

2:59 p.m.

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

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3360?page=c... ] Manuel Dominguez Sarmiento commented on HHH-3360: ------------------------------------------------- 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;

...

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 Assignee: Steve Ebersole Priority: Minor Attachments: C3P0OracleBatchingBatcherFactory.java, C3P0OracleBatchingBatcher.java, OracleBatchingBatcherFactory.java, OracleBatchingBatcher.java, OracleBatchingBatcher.java, OracleBatchingBatcher.java Original Estimate: 0.5h Remaining Estimate: 0.5h 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#... As expected, implementing this solution has resulted in drastical improvement in batch processing.

-- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

4569

days inactive

5807

days old

hibernate-issues@lists.jboss.org

Manage subscription

11 comments

4 participants

tags (0)

participants (4)

Diego del Río (JIRA)
Fabian Depry (JIRA)
Manuel Dominguez Sarmiento (JIRA)
Steve Ebersole (JIRA)

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

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