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;

Depart

Define "for some records I get failure". For example if you do an update, then no rows affected is not a failure, it simply means no rows were updated. – Jamboree

Here I have just Insert statements. So I dont see some of the records in db but I dont see any exception trace either. – Depart

@cacert, do your inserts have a where clause that prevents double insertion? I'm thinking something like WHERE NOT EXISTS (...) – Kalfas

good point, but insert statements doesnt have this. INSERT INTO FTX.ESI_FLTR_02_ (TB_TRANSFERSEQUENCE,TB_LINENUMBER,TB_CONTENT,TB_STATUS,TB_LINESEQUENCE) " + "VALUES (?,?,CONCAT(?,?),?,?) – Depart

What is your autocommit state during batch inserts? – Kalfas

Are there any triggers on the target table? – Kalfas

autocommit=true, no trigger. – Depart

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.

Kalfas

the 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. What you have stated in the answer is already in the question though not explicitly. – Apocope

@Blip, add more to my answer. – Kalfas

"My understanding from this is that if any statement in the batch fails then a BatchUpadteException will be thrown." Actually I was expecting this too, and today I get an error and learned that is not the case. I was expecting some clarification about what kind of failure will not cause exception.Today I become sure that there is such cases. – Depart

This type of behaviour may be dependant upon JDBC Driver/DB Vendor. It goes beyond the original intent as documented in the JDBC API. – Kalfas

@Depart could you write a different answer about the cases where no BatchUpdateException is thrown yet there are failed cases – Apocope

@BrettWalker the question is regarding Oracle JDBC – Apocope

@BrettWalker ok I made question more clear. thanks for the efforts. – Depart

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.

Leeuwarden

as I stated in question, my problem is although I dont get anyexception, I cant see some records are not inserted. May be executeUpdate() returns error for not inserted records but code does not handle that. My question is in what situation it throws error, and in what situation it does not throw error instead flag record as failed. – Depart

executeBatch() always throws an exception if one of the elements in the batch fails to execute. That's per the JDBC spec. – Leeuwarden

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;" 
]

Criseyde