Update in bulk through JDBC batch gives SQLException TransactionImpl

Question

I'm using EJB3 with Oracle database and JDBC.

I'm working on an app where I have to fire 25000 UPDATE queries.

My code is as follows:

public int updateStatus(List<String> idList) {
    Connection connection = getConnection(); // Connection initialized properly for oracle db
    statement = connection.createStatement();
    String sql = null;
    for (String id : idlist) { // idList is properly filled
        sql = "UPDATE TBLTEST SET STATUS = 'FIXED' WHERE ID = '" + id + "'";
        statement.addBatch(sql);
    }
    int[] affectedRecords = statement.executeBatch();
}

Please note, the class in which this method is written, is annotated as

@TransactionManagement(TransactionManagementType.CONTAINER)

This code is working perfectly fine upto 8000 queries. For more ids, it throws the following exception:

org.jboss.util.NestedSQLException: Transaction TransactionImple < ac, BasicAction: 0:ffffc0a80272:1652:56bd6be5:57e status: ActionStatus.ABORTED > cannot proceed STATUS_ROLLEDBACK; - nested throwable: (javax.transaction.RollbackException: Transaction TransactionImple < ac, BasicAction: 0:ffffc0a80272:1652:56bd6be5:57e status: ActionStatus.ABORTED > cannot proceed STATUS_ROLLEDBACK)
    at org.jboss.resource.adapter.jdbc.WrapperDataSource.checkTransactionActive(WrapperDataSource.java:165)
    at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransactionActive(WrappedConnection.java:843)
    at org.jboss.resource.adapter.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:858)
    at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:835)
    at org.jboss.resource.adapter.jdbc.WrappedConnection.createStatement(WrappedConnection.java:183)

Can anyone help with the exception?

A little suggestion to make your code faster and better. Hopefully that will get you off this exception. Use preparedStatement() instead createStatement(). More info on the following link. http://viralpatel.net/blogs/batch-insert-in-java-jdbc/ — justRadojko, Feb 12 '16 at 08:31
@justRadojko, Thanks for reply. But I cannot use `PreparedStatement` as it won't give me exact result in terms of which query updated record successfully, i.e. `affectedRecords` will give me an array full of -2 values. — RAS, Feb 12 '16 at 08:34

score 1 · Answer 1 · answered Feb 12 '16 at 09:15

Best guess: By using individual SQL statements instead of PreparedStatement you force the driver to send all your statements (> 400k of character data) to the DB and the DB to parse all of that 400k characters which will hit a limit at some time and breaks things (Exception is not clear on where or what broke as it hides the causing Exception).

How to fix:

Go for individual batches of "not too many" statements at a time - say... 1000:

public int updateStatus(List<String> idList) {
    List<Integer> affectedRecords = new ArrayList<Integer>(idList.size());
    try(Connection connection = getConnection(); 
        Statement statement = connection.createStatement()) {
        int count = 0;
        for (String id : idList) {          
            statement.addBatch("UPDATE TBLTEST SET STATUS = 'FIXED' WHERE ID = '" + id + "'");
            //Execute after 1000 rows 
            if(++count % 1000 == 0) {
                int[] result = statement.executeBatch();
                //Utility Method - you need to implement to add int[] into the List
                addResults(affectedRecords, result);
                statement.clearBatch();             
            }
        }
        //In need of final execute?
        if(count % 1000 > 0) {
          // For good measure execute once more
          int[] result = statement.executeBatch();
          //Utility Method - you need to implement to add int[] into the List
          addResults(affectedRecords, result);      
        }              
    } catch(SQLException se) {
        se.printStackTrace();
    }
}

Thanks Jan, it helped upto a certain extent. But after reaching a point, this also fails. — RAS, Feb 12 '16 at 14:18

Update in bulk through JDBC batch gives SQLException TransactionImpl

1 Answers1