executeBatch behaviour in case of partial failure

Question

I have a java 1.6 application which use batch insert for inserting records in Oracle db using jdbc driver. As you know on Statement object there is a method called executeBatch() which we use for batch updates. It has a return type of int array which has result of execution for each record in it. But it also throws BatchUpdateException in case of error and we can get result int array from that too. My question is in what error situations I should expect BatchUpdateException and when I should expect there is no exception thrown but for some records I get failure.

Note: Question is spesifically for Oracle JDBC. And to make it more clear, I have seen situations that after executing executeBatch() I did not get BatchUpdateException however some of the insert statements failed. My question was about in what situation that can occur ?

This is the return javadoc of Statement.executeBatch() method. According to the general opinion here when one entry fails, execution throws BatchUpdateException then in which condition we can expect some entries in return array failed.

      * @return an array of update counts, with one entry for each command in the
 *         batch. The elements are ordered according to the order in which
 *         the commands were added to the batch.
 *         <p>
 *         <ol>
 *         <li> If the value of an element is >=0, the corresponding command
 *         completed successfully and the value is the update count for that
 *         command, which is the number of rows in the database affected by
 *         the command.</li>
 *         <li> If the value is SUCCESS_NO_INFO, the command completed
 *         successfully but the number of rows affected is unknown.
 *         <li>
 *         <li> If the value is EXECUTE_FAILED, the command failed.
 *         </ol>
 * @throws SQLException
 *             if an error occurs accessing the database
 */
public int[] executeBatch() throws SQLException;

Answer 1

Let's say that you have 5 batch update statements. The execution of each them is to update 20 records, known in advance.

The execution of the batch of update statements occurs without a BatchUpdateException, or a SQLException being thrown.

If any of the elements in the returned int array is not 20 then you known there has been unexpected behaviour. This could be seen as a failure.

EDIT

From the JavaDoc of the BatchUpdateExcpetion (The highlights are my addition)

After a command in a batch update fails to execute properly and a BatchUpdateException is thrown, the driver may or may not continue to process the remaining commands in the batch. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will have an element for every command in the batch rather than only elements for the commands that executed successfully before the error. In the case where the driver stops [ed] processing commands, the array element for any command that failed is Statement.EXECUTE_FAILED.

My understanding from this is that if any statement in the batch fails then a BatchUpadteException will be thrown.

Answer 2

The Oracle JDBC driver throws a BatchUpdateException if an error occurs in the middle of the batch.

For example let's assume you're sending a batch with 10 entries (10 rows to insert in your case). Entries #0 through #4 are successful. Entry #5 hits an error such as a primary key violation. The execution stops at 5 and the driver throws a BatchUpdateException. If you call getUpdateCounts() you'll get an array of size 10 with 5 SUCCESS_NO_INFO and 5 EXECUTE_FAILED.

Note that starting in 12c (database and driver) you can get an update count for each element of the batch. This is more useful when you're executing updates in a batch. For each element in the batch you can know how many rows have been updated.

Answer 3

I handled the error in this way,hope this helps. We get an integer array in exception by calling getUpdateCounts (-3 value indicates the query has failed or you can check by Statement.EXECUTE_FAILED).So you can loop through the array and find out at which index the query has failed and map that index to your query list. In this way you can find out which queries got failed.

db.withTransaction {
            try {
                db.withBatch () { stmt ->
                    finalQueries.each {
                        log.debug("Query : ${it}")
                        try {
                            stmt.addBatch(it);
                        } catch (Exception e) {
                            log.error("Error while executing query", e.getMessage());
                            errors.add(e.getMessage());
                        }
                    }
                }
            } catch (Exception e) {
                log.error("Error in query ", e.getMessage())
                def res = e.getUpdateCounts();
                res.each {
                    if(it == Statement.EXECUTE_FAILED) {
                        errors.add("Error in query : " + finalQueries[it]);
                    }
                }
                errors.add(e.getMessage());
            }
        

finalQueries contains all the queries.

[
    "set sql_safe_updates=0;",
    "Update users set ACCOUNTLOCKE = 0 where userkey = 27;",
    "Update users set asd = 0 where userkey = 28;",
    "Update users set city = 'solapur' where userkey = 29;",
    "set sql_safe_updates=1;" 
]