In this posting I explain a solution I found to circumvent a limitation imposed by the way Oracle implements jdbc 2 batch updating. I will appreciate your comments, suggestions and corrections. The code is still under testing.
A problem has arisen because of the way Oracle implements jdbc 2 batch updates; according to the jdbc 2.1 spec:
Quote:
The Statement.executeBatch() method submits a statement’s batch to the underlying data source for execution. Batch elements are executed serially (at least logically) in the order in which they were added to the batch. When all of the elements in a batch execute successfully, executeBatch() returns an integer array containing one entry for each element in the batch. The entries in the array are ordered according to the order in which the elements were processed (which, again, is the same as the order in which the elements were originally added to the batch). An entry in the array may have the following values:
1. If the value of an array entry is greater than or equal to zero, then the batch element was processed successfully and the value is an update count indicating the number of rows in the database that were effected by the element’s execution.
2. A value of -2 indicates that a element was processed successfully, but that the number of effected rows is unknown.
We are using Oracle database version 10 and the corresponding jdbc driver also made by Oracle. This driver always return -2 as a result of sending the message executeBatch() to a Statement. Hibernate uses the number of affected rows per element in a batch to implement optimistic concurrency control. When the batch of statements must be executed, Hibernate does the following:
Code:
protected void doExecuteBatch(PreparedStatement ps) throws SQLException, HibernateException {
if ( batchSize == 0 ) {
log.debug( "no batched statements to execute" );
}
else {
if ( log.isDebugEnabled() ) {
log.debug( "Executing batch size: " + batchSize );
}
try {
checkRowCounts( ps.executeBatch(), ps );
}
catch (RuntimeException re) {
log.error( "Exception executing batch: ", re );
throw re;
}
finally {
batchSize = 0;
}
}
}
private void checkRowCounts(int[] rowCounts, PreparedStatement ps) throws SQLException, HibernateException {
int numberOfRowCounts = rowCounts.length;
if ( numberOfRowCounts != batchSize ) {
log.warn( "JDBC driver did not return the expected number of row counts" );
}
for ( int i = 0; i < numberOfRowCounts; i++ ) {
expectations[i].verifyOutcome( rowCounts[i], ps, i );
}
}
This code was extracted from org.hibernate.jdbc.BatchingBatcher. The number of rows affected per Statement is used in org.hibernate.jdbc.BasicExpectation:
Code:
public final void verifyOutcome(int rowCount, PreparedStatement statement, int batchPosition) {
rowCount = determineRowCount( rowCount, statement );
if ( batchPosition < 0 ) {
checkNonBatched( rowCount );
}
else {
checkBatched( rowCount, batchPosition );
}
}
private void checkBatched(int rowCount, int batchPosition) {
if ( rowCount == -2 ) {
if ( log.isDebugEnabled() ) {
log.debug( "success of batch update unknown: " + batchPosition );
}
}
else if ( rowCount == -3 ) {
throw new BatchFailedException( "Batch update failed: " + batchPosition );
}
else {
if ( expectedRowCount > rowCount ) {
throw new StaleStateException(
"Batch update returned unexpected row count from update [" + batchPosition +
"]; actual row count: " + rowCount +
"; expected: " + expectedRowCount
);
}
if ( expectedRowCount < rowCount ) {
String msg = "Batch update returned unexpected row count from update [" +
batchPosition + "]; actual row count: " + rowCount +
"; expected: " + expectedRowCount;
throw new BatchedTooManyRowsAffectedException( msg, expectedRowCount, rowCount, batchPosition );
}
}
}
When the number of rows affected per row is -2 (as it is always the case with Oracle's jdbc driver) Hibernate simple ignores it. When the statement updates less rows than expected, Hibernate throws an StateStateException. To illustrate this behavior, suppose there are two database transactions executing at the same time, each started from Hibernate to synchronize the state of two different Sessions.
Database transaction 1 (corresponding to Hibernate Session 1):
Code:
update PERSON set NAME = "Name 1", VERSION = 2 where VERSION = 1;
Database transaction 2 (corresponding to Hibernate Session 2):
Code:
update PERSON set NAME = "Name 2", VERSION = 2 where VERSION = 1;
Depending on the order of execution, one of the transactions will update 1 row, the other will update 0 rows. Hibernate should raise an StateStateException for the Session whose corresponding transaction updated 0 rows, indicating that the data the transaction tried to update was old. But Oracle's jdbc driver always says the number of affected rows was -2, so Hibernate can't raise the exception and the Session thinks it could synchronize it's state with the database successfuly, which is not true.
I found that Oracle's jdbc driver implements a non-official form of batch updates, which they call "Oracle model" of update batching. You can find the differences between the Oracle model and the standard model here:
http://download.oracle.com/docs/cd/A91202_01/901_doc/java.901/a90211/oraperf.htm#1056233.
Oracle model of batching is specified in an extension interface called oracle.jdbc.OraclePreparedStatement (that extends java.sql.PreparedStatement). These are the relevant methods as written in the api javadoc:
Quote:
public int sendBatch()
throws java.sql.SQLException
Send any existing Oracle-style batch.
This API is used in the context of Oracle-style batching. It flushes any batched executions before the batch value is reached.
Executions normally happen when the number of executions reaches the batch value, which is set via the API setExecuteBatch().
It does not do anything if the statement does not use Oracle-style batching.
To use this API, the user must cast the PreparedStatement object to oracle.jdbc.driver.OraclePreparedStatement.
Returns:
the update count.
Throws:
java.sql.SQLException - if an error occurred.
public void setExecuteBatch(int batchValue)
throws java.sql.SQLException
Set the batch value (for Oracle-style batching only).
The Oracle Jdbc driver can "batch" multiple executions of a single statement with different bind variables.
Actual execution happens when the number of batched executions reaches the "batch value", which is set by this API.
The batch value for this prepared statement can be set at any time via this API. Changing the batch value will affect
all future executes.
The batch value will automatically be set to 1 by the driver if any of the bind types is of type stream.
This overrides any value that the user might have set. Binds that were performed before any setXXXStream() is called, and
that have not been executed, will be flushed (i.e. executed).
To use this API, the user must cast the PreparedStatement object to oracle.jdbc.driver.OraclePreparedStatement.
Parameters:
batchValue - batch value to be set. It must be greater than or equal to 1.
The class org.hibernate.jdbc.AbstractBatcher and its subclasses are responsible for the management and execution of prepared statements and batching. Indeed, the interface org.hibernate.jdbc.Batcher specifies the behavior of the batcher that Hibernate will use. org.hibernate.jdbc.AbstractBatcher implements org.hibernate.jdbc.Batcher. To use the Oracle model of batching and overcome the lack of support of the standard model of batching in Oracle's jdbc driver, I subclassified org.hibernate.jdbc.BatchingBatcher:
Code:
public abstract class OracleBatchingBatcher extends BatchingBatcher {
private int batchSize;
public OracleBatchingBatcher(ConnectionManager connectionManager,
Interceptor interceptor) {
super(connectionManager, interceptor);
}
@Override
public PreparedStatement prepareBatchStatement(String sql)
throws SQLException, HibernateException {
PreparedStatement stmt = super.prepareBatchStatement(sql);
log.debug("PreparedStatement implementation: " + stmt);
OraclePreparedStatement oracleStmt = this
.getOraclePreparedStatement(stmt);
int stmtHashcode = stmt.hashCode();
log.debug("Returning statement " + stmt + " (" + stmtHashcode
+ ") with batch size " + oracleStmt.getExecuteBatch());
if (oracleStmt.getExecuteBatch() == 1) {
oracleStmt.setExecuteBatch(this.getFactory().getSettings()
.getJdbcBatchSize());
log.debug("Setting new batch size for statement " + stmt + " ("
+ stmtHashcode + "): " + oracleStmt.getExecuteBatch());
}
return stmt;
}
protected abstract OraclePreparedStatement getOraclePreparedStatement(
PreparedStatement stmt);
@Override
public void addToBatch(Expectation expectation) throws SQLException,
HibernateException {
if (!expectation.canBeBatched()) {
throw new HibernateException(
"attempting to batch an operation which cannot be batched");
}
log.debug("Request to add statement to batch with expectation "
+ expectation);
if (expectation.equals(Expectations.BASIC)) {
batchSize++;
log.debug("Basic expectation " + expectation
+ ". Batch size incremented: " + batchSize);
}
if (batchSize == getFactory().getSettings().getJdbcBatchSize()) {
doExecuteBatch(this.getStatement());
} else {
log.debug("Statement added to batch (" + batchSize + "/"
+ getFactory().getSettings().getJdbcBatchSize() + ")");
this.getStatement().executeUpdate();
}
}
@Override
protected void doExecuteBatch(PreparedStatement ps) throws SQLException,
HibernateException {
if (batchSize == 0) {
log.debug("no batched statements to execute");
} else {
if (log.isDebugEnabled()) {
log.debug("Executing batch size: " + batchSize);
}
try {
checkRowCounts(this.getOraclePreparedStatement(ps).sendBatch());
} catch (RuntimeException re) {
log.error("Exception executing batch: ", re);
throw re;
} finally {
batchSize = 0;
}
}
}
protected void checkRowCounts(int rowCount) {
if (batchSize > rowCount) {
throw new StaleStateException(
"Batch update returned unexpected row count from update; actual row count: "
+ rowCount + "; expected: " + batchSize);
}
if (batchSize < rowCount) {
String msg = "Batch update returned unexpected row count from update; actual row count: "
+ rowCount + "; expected: " + batchSize;
throw new BatchedTooManyRowsAffectedException(msg, batchSize,
rowCount, 0);
}
}
}
The method
Code:
protected OraclePreparedStatement getOraclePreparedStatement(
PreparedStatement stmt) {
return (OraclePreparedStatement) stmt;
}
encapsulates the way in wich the OraclePreparedStatement is obtained. This is because some connection pools return the OraclePreparedStatement wrapped in another java.sql.Statement implemented by the pool itself. This is the case of Jakarta Commons DBCP
http://commons.apache.org/dbcp/, that returns org.apache.commons.dbcp.DelegatingStatement. The default implementation of getOraclePreparedStatement(PreparedStatement stmt) asumes that the statement is not wrapped and returns the statement itself.
The following subclass specializes the behavior in case you are using Jakarta Commons DBCP:
public class DBCPBatchingBatcher extends OracleBatchingBatcher {
Code:
public DBCPBatchingBatcher(ConnectionManager connectionManager,
Interceptor interceptor) {
super(connectionManager, interceptor);
}
@Override
protected OraclePreparedStatement getOraclePreparedStatement(
PreparedStatement stmt) {
return (OraclePreparedStatement) ((DelegatingStatement) stmt)
.getDelegate();
}
}
Another alternative is to resolve getOraclePreparedStatement(PreparedStatement stmt) by composition rather by subclassing.
Hibernate uses a factory to create the Batcher it will use. This is the factory in our case.
Code:
public class DBCPBatchingBatcherFactory implements BatcherFactory {
public Batcher createBatcher(ConnectionManager connectionManager,
Interceptor interceptor) {
return new DBCPBatchingBatcher(connectionManager, interceptor);
}
}
You should set the system property hibernate.jdbc.factory_class to the name of the class implementing BatcherFactory so that hibernate becomes aware of that factory.
This way we end by extending hibernate rather than patching it.
I expect your comments about this.
Hibernate version: 3.2.0.cr3
Oracle database version: 10.1.0.4.0
Oracle jdbc driver version: 10.1.0.4.0[/list]
Login
FAQ
Search