JDBC batch update skipping out on initial set of update statements

Question

We are using JDBC batch update (Statement - void addBatch( String sql ) and int[] executeBatch()) in our Java code. The job is supposed to insert about 27k records in a table and then update about 18k records in a subsequent batch.

When our job runs at 6am, it is missing a few thousand records (we observed this from the database audit logs). We can see from the job logs that the update statements are being generated for all the 18k records. We understand that all the update statements get added in sequence to the batch, However, only records from the beginning of the batch seem to be missing. Also, it is not a fixed number everyday - one day, it skips out on the first 4534 update statements and another day it skips out on the first 8853 records and another day, it skips out on 5648 records.

We initially thought this could be a thread issue but have since moved away from that thought process as the block being skipped out does not always contain the same number of update statements. If we assume that the first few thousand updates are happening even before the insert, then the updates should at least show up in the database audit logs. However, this is not the case.

We are thinking this is due to a memory/heap issue as running the job at any other time picks up all the 18k update statements and they are executed successfully. We reviewed the audit logs from the Oracle database and noticed that the missing update statements are never executed on the table during the 6am run. At any other time, all the update statements are showing up in the database audit logs.

This job was running successfully for almost 3 years now and this behavior started only from a few weeks ago. We tried to look at any changes to the server/environment but nothing jumps out at us.

We are trying to pinpoint why this is happening, specifically, if there are any processes that are using up too much of the JVM heap and as a result, our update statements are getting overwritten/not being executed.

Database: Oracle 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Java: java version "1.6.0_51" Java(TM) SE Runtime Environment (build 1.6.0_51-b11) Java HotSpot(TM) Server VM (build 20.51-b01, mixed mode)

void main()
{
    DataBuffer dataBuffer;//assume that all the selected data to be updated is stored in this object
    List<String> TransformedList = transform(dataBuffer);
    int status = bulkDML(TransformedList);
}

public List<String> transform(DataBuffer i_SourceData) 
{
    //i_SourceData has all the data selected from 
    //the source table, that has to be updated
    List<Row> AllRows = i_SourceData.getAllRows();
    List<String> AllColumns = i_SourceData.getColumnNames();
    List<String> transformedList = new ArrayList<String>();
    for(Row row: AllRows)
    {
        int index = AllColumns.indexOf("unq_idntfr_col");
        String unq_idntfr_val = (String)row.getFieldValues().get(index);
        index = AllColumns.indexOf("col1");
        String val1 = (String)row.getFieldValues().get(index);
        String query = null;
        query =   "UPDATE TABLE SET col1 = " + val1 + " where unq_idntfr_col=" + unq_idntfr_val;//this query is not the issue either - it is parameterized in our code
        transformedList.add(query);
    }
    return transformedList;
}

public int bulkDML(List<String> i_QueryList)
{
    Connection connection = getConnection();
    Statement statement = getStatement(connection);
    try
    {
        connection.setAutoCommit(false);
        for (String Query: i_QueryList)
        {
            statement.addBatch(Query);
        }
        statement.executeBatch();   
        connection.commit();
    }
    //handle various exceptions and all of them return -1
    //not pertinent to the issue at hand
    catch(Exception e)
    {
            return -1;
    }
    CloseResources(connection, statement, null);
    return 0;
}

Any suggestions would be greatly appreciated, thank you.

Answer 1

If you want to execute multiple updates on the same table then I suggest modifying your query to use binds and a PreparedStatement because that's really the only way to do real DML batching with the Oracle Database. For example your query would become:

UPDATE TABLE SET col1=? WHERE unq_idntfr_col=?

and then use JDBC batching with the same PreparedStatement. This change would require to you revisit your bulkDML method to make it take bind values as parameter instead of SQL.

The JDBC pseudo code would then look like this:

     PreparedStatement pstmt = connection.prepareCall("UPDATE TABLE SET col1=? WHERE unq_idntfr_col=?");
     pstmt.setXXX(1, x);
     pstmt.setYYY(2, y);
     pstmt.addBatch();
     pstmt.setXXX(1, x);
     pstmt.setYYY(2, y);
     pstmt.addBatch();
     pstmt.setXXX(1, x);
     pstmt.setYYY(2, y);
     pstmt.addBatch();
     pstmt.executeBatch();