Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 11 posts ] 
Author Message
 Post subject: Oracle jdbc 2 batch updating
PostPosted: Wed Aug 22, 2007 8:56 am 
Newbie

Joined: Thu Apr 19, 2007 10:38 am
Posts: 11
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]


Top
 Profile  
 
 Post subject: Oracle jdbc 2 batch updating
PostPosted: Thu Sep 13, 2007 6:10 pm 
Newbie

Joined: Thu Jul 22, 2004 11:23 am
Posts: 14
Hi Godie,

We are having a deeply mysterious problem with StaleStateException and are having a very difficult time figuring out why it is happening.

I've got to hand it to you, that's really taking the problem all the way to the mat... I'm scared to think this may be our problem too, considering what you had to do to fix it but also impressed that you got that far. Have you heard back from anyone in either the Oracle or Hibernate camps?

We are using the same DB (Oracle 10G), the DBCP Pool, Oracle JDBC Driver 14-10.1.0.4 (in jar ojdbc14-10.1.0.4.jar), the scenario looks pretty similar...

How can one tell if a StaleStateException problem is the same situation you are describing?

-=j=-


Top
 Profile  
 
 Post subject:
PostPosted: Sun Sep 07, 2008 11:14 pm 
Newbie

Joined: Sun Sep 07, 2008 11:07 pm
Posts: 17
Hi,
I am working with Oracle 10g and Hibernate 3.2.2
The Oracle driver is returning me -2 for versioned updates and hence Hibernate is not throwing StaleObjectexception.

Can anybody guide me the preferred way to solve this problem?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Nov 03, 2008 3:05 pm 
Newbie

Joined: Mon Nov 03, 2008 3:02 pm
Posts: 1
http://download.oracle.com/docs/cd/B141 ... sthref1835

22.1.3.6 Update Counts in the Oracle Implementation of Standard Batching
..

the feature is still present in oracle 10.1.

a probable fix will be not to batch. batch to a point the flush execute single update...


Top
 Profile  
 
 Post subject:
PostPosted: Thu Dec 04, 2008 8:17 pm 
Newbie

Joined: Thu Apr 19, 2007 10:38 am
Posts: 11
The solution showed in the first post exploits what Oracle calls "Oracle model" of update batching. This model is explained in the page you've linked in the previus post.
In our scenario avoiding batching is prohibitive because of the performance problem.
We've been using that solution in our production system for approximately a year without problems.
Basically, OracleBatchingBatcher uses Oracle's specific interfaces to handle batching instead of using the standard jdbc interfaces.


Top
 Profile  
 
 Post subject: Re: Oracle jdbc 2 batch updating
PostPosted: Wed Dec 10, 2008 3:16 pm 
Newbie

Joined: Thu Apr 19, 2007 10:38 am
Posts: 11
jackalista wrote:
Hi Godie,

How can one tell if a StaleStateException problem is the same situation you are describing?

-=j=-


Hi jackalista, when the Oracle jdbc driver returns -2 as the number of affected rows by a versioned update statement, Hibernate simple ignores it, i.e. no StaleStateException is thrown, and then the transaction is commited. Indeed two things could have happened: the versioned update statements modified the corresponding rows or not. The problem here is that the user owning the commited transaction whose statements didn't modify rows, thinks that the rows were modified.
I'm enhancing the solution I've posted, I'll post it again as soon as I can.


Top
 Profile  
 
 Post subject: Re: Oracle jdbc 2 batch updating
PostPosted: Wed Feb 03, 2010 11:27 pm 
Newbie

Joined: Wed May 06, 2009 10:26 pm
Posts: 2
Hi Godie,

This thread might be dead, but hey it's worth a shot.

I have a feeling OracleBatchingBatcher solution presented here is not totally complete in regards to optimistic locking. Each batch added is treated as a single row update in the scope of the PreparedStatement being executed. If I understand the role of Expectations correctly, given a batch of statements to execute for each statement to be executed there is a one-to-one relationship with the Expectation. This expectation tracks the expected number of database rows to be modified, in addition to encapsulating the logic behind verifying this. The code presented doesn't take this behavior into account and assumes that BasicExpectation.expectedRowCount == 1 at all times.

The most simple example I can think of is a single-threaded case, with a single batched update statement which will effect more than 1 row. In this case, a BatchedTooManyRowsAffectedException should be thrown. My thinking is that the Oracle sendBatch() method returns the total number of rows affected, which is greater than the batch size in this case.

I'm thinking a solution could be to sum expected row counts, and compare this value to the return value of sendBatch(). But from glancing at the code in BasicExpectation, it doesn't look like this value is exposed...

I'd appreciate any feedback on this.

Thanks!


Top
 Profile  
 
 Post subject: Re: Oracle jdbc 2 batch updating
PostPosted: Fri Feb 05, 2010 4:05 pm 
Newbie

Joined: Thu Apr 19, 2007 10:38 am
Posts: 11
akilman wrote:
Hi Godie,

This thread might be dead, but hey it's worth a shot.

I have a feeling OracleBatchingBatcher solution presented here is not totally complete in regards to optimistic locking. Each batch added is treated as a single row update in the scope of the PreparedStatement being executed. If I understand the role of Expectations correctly, given a batch of statements to execute for each statement to be executed there is a one-to-one relationship with the Expectation. This expectation tracks the expected number of database rows to be modified, in addition to encapsulating the logic behind verifying this. The code presented doesn't take this behavior into account and assumes that BasicExpectation.expectedRowCount == 1 at all times.

The most simple example I can think of is a single-threaded case, with a single batched update statement which will effect more than 1 row. In this case, a BatchedTooManyRowsAffectedException should be thrown. My thinking is that the Oracle sendBatch() method returns the total number of rows affected, which is greater than the batch size in this case.

I'm thinking a solution could be to sum expected row counts, and compare this value to the return value of sendBatch(). But from glancing at the code in BasicExpectation, it doesn't look like this value is exposed...

I'd appreciate any feedback on this.

Thanks!


Top
 Profile  
 
 Post subject: Re: Oracle jdbc 2 batch updating
PostPosted: Fri Feb 05, 2010 4:05 pm 
Newbie

Joined: Thu Apr 19, 2007 10:38 am
Posts: 11
Hi akilman,
akilman wrote:
The code presented doesn't take this behavior into account and assumes that BasicExpectation.expectedRowCount == 1 at all times.

Yes, it's true, based on the assumption that Hibernate obtains a BasicExpectation through the Expectations class:
Code:
public static final int USUAL_EXPECTED_COUNT = 1;

public static final Expectation BASIC = new BasicExpectation( USUAL_EXPECTED_COUNT );


akilman wrote:
I'm thinking a solution could be to sum expected row counts, and compare this value to the return value of sendBatch(). But from glancing at the code in BasicExpectation, it doesn't look like this value is exposed...


This is what OracleBatchingBatcher does instead of delegating to the set of BasicExpectation's.

This design has some flaws:
- it works only with BasicExpectation, leaving out BasicParamExpectation and the kind of Null-Expectation realized in the inner class in Expectations:
Code:
   public static final Expectation NONE = new Expectation() {
      public void verifyOutcome(int rowCount, PreparedStatement statement, int batchPosition) {
         // explicitly perform no checking...
      }

      public int prepare(PreparedStatement statement) {
         return 0;
      }

      public boolean canBeBatched() {
         return true;
      }
   };


- replicates the verification code which originally resides in BasicExpectation

Tell me what you think. I'll try to devise a better solution and will post it here.


Top
 Profile  
 
 Post subject: Re: Oracle jdbc 2 batch updating
PostPosted: Tue Mar 30, 2010 5:33 pm 
Newbie

Joined: Fri Aug 18, 2006 12:26 pm
Posts: 4
I think the solution posted here:

http://opensource.atlassian.com/project ... e/HHH-3360

obtains the expectedRowCount from the expectation and uses it.


Top
 Profile  
 
 Post subject: Re: Oracle jdbc 2 batch updating
PostPosted: Mon Apr 05, 2010 9:16 am 
Newbie

Joined: Thu Apr 19, 2007 10:38 am
Posts: 11
We've just posted our solution here:
http://opensource.atlassian.com/projects/hibernate/browse/HHH-5070
Comments are welcome


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.