OpenJPA
  1. OpenJPA
  2. OPENJPA-530

Statement batching incorrectly re-arranged order of SQL statements organized by update managers

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.0
    • Fix Version/s: 1.1.0
    • Component/s: jdbc
    • Labels:
      None

      Description

      BatchingPreparedStatementManagerImpl implements the core SQL statement execution function of the Statement Batching features.

      The current implementation of this class holds a cache that maps SQL prepared statements to a collection of parameters of batched requests for that prepared statement. When update manager finishes batching its requests, BatchingPreparedStatementManagerImpl.flush() method is called to submit the batch elements in the cache to the jdbc driver.

      Essentially, this implementation discards all the ordering of statements being dispatched to the statement batching function by the update manager. This caused improper order of SQL statements to be sent to the data store. As a result, the underlying database may throw exception indicates the unexpected data condition. E.g.

      com.ibm.db2.jcc.b.SqlException: Error for batch element #2: DB2 SQL error: SQLCODE: -530, SQLSTATE: 23503, SQLERRMC: LEEALBER.EMPBEAN.SQL080226132752530
      SQL0530N: The insert or update value of the FOREIGN KEY constraint-name is not equal to any value of the parent key of the parent table.

        Activity

        Hide
        Albert Lee added a comment -

        Reworked the batching processing and still maintain the order of statements dispatched by update manager using these rules:

        1) When a row is requested to be executed, it is saved in a cache and associated to the sql string.
        2) Any subsequent requests of the same sql statement continue to append to the cache.
        3) If a request is different than the one currently in cache, the current cache is dispatched to the jdbc driver first and the current request is processed as in step 1).
        4) When the cache is processed, normal preparedStatement.executeUpdate() is used to dispatch a single request. If there are more than one element in the batch, preparedStatement.addBatch()/executeBatch() is used to dispatch the requests.

        Albert Lee.

        Show
        Albert Lee added a comment - Reworked the batching processing and still maintain the order of statements dispatched by update manager using these rules: 1) When a row is requested to be executed, it is saved in a cache and associated to the sql string. 2) Any subsequent requests of the same sql statement continue to append to the cache. 3) If a request is different than the one currently in cache, the current cache is dispatched to the jdbc driver first and the current request is processed as in step 1). 4) When the cache is processed, normal preparedStatement.executeUpdate() is used to dispatch a single request. If there are more than one element in the batch, preparedStatement.addBatch()/executeBatch() is used to dispatch the requests. Albert Lee.
        Hide
        Bogdan Stroe added a comment -

        This problem appears in OpenJPA 1.0.2 also.
        As a work-around (that loses the batch performance improvement as no batch is done at all) one can set this property in persistence.xml:
        <property name="openjpa.jdbc.UpdateManager" value="operation-order"/>

        Show
        Bogdan Stroe added a comment - This problem appears in OpenJPA 1.0.2 also. As a work-around (that loses the batch performance improvement as no batch is done at all) one can set this property in persistence.xml: <property name="openjpa.jdbc.UpdateManager" value="operation-order"/>

          People

          • Assignee:
            Albert Lee
            Reporter:
            Albert Lee
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development