Retry the preparedstatement batches if an exception (SQLException | BatchUpdateException) occurs - Java

Question

I had a situation where my code was getting hit by the deadlock issue with SQL Server for some transactions. So, I implemented a retry logic to overcome the same. Now, I'm facing a new problem. The problem is, whenever it retries, the batch which was tried to execute will be empty/cleared after recovering from the exception. This is causing missing data inserts/updates. Please help me with this.

Summary: Retry the preparedstatement batches if an exception (SQLException | BatchUpdateException) occurs

Current Implementation:

do {
        try {
            if (isInsert) {
                int[] insertRows = psInsert.executeBatch();
                psInsert.clearBatch();
                System.out.println("insertRowsSuccess:" + Arrays.toString(insertRows));
            } else {
                int[] updateRows = psUpdate.executeBatch();
                psUpdate.clearBatch();
                System.out.println("updateRowsSuccess:" + Arrays.toString(updateRows));
            }
            break;
        } catch (BatchUpdateException e) {
            conn.rollback();
            if (++count == maxTries) {
                System.out.println(e.getMessage());
                getFailedRecords(e, operation);
            }
        } catch (SQLException e) {
            if (++count == maxTries) {
                System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
            }
        }
        System.out.println("Tries:" + count);
    } while (true);
    

private static void getFailedRecords(BatchUpdateException ex, String operation) {

    int[] updateCount = ex.getUpdateCounts();
    ArrayList<Integer> failedRecsList = new ArrayList<Integer>();
    int failCount = 0;

    for (int i = 0; i < updateCount.length; i++) {
        if (updateCount[i] == Statement.EXECUTE_FAILED) {
            failCount++;
            failedRecsList.add(i);
        }

    }

    System.out.println(operation + " Failed Count: " + failCount);
    System.out.println(operation + " FailedRecordsIndex:" + failedRecsList);

}       

Answer 1

After execute, irrespective of success or failure of the batch, the batch will be cleared. You will need to repopulate the batch before you can retry.