Can I re-use a PreparedStatement if it hits a transient exception?

Question

I have a block of code which updates records in a database. Reduced example:

...
statement = connection.prepareStatement(
    "INSERT INTO thistable (name) VALUES (?)",
    PreparedStatement.RETURN_GENERATED_KEYS
);
statement.setString( 1, "Fred" );
statement.addBatch( );
statement.setString( 2, "Joe" );
statement.addBatch( );
statement.executeBatch( );
...

This is part of some code which is processing a lot of records, and the code is running a lot of threads for speed. That's all fine, but as the load increases on the live environment, I've been noticing a few SQLTransientExceptions being thrown. It seems I can't do anything about these except retry the transaction.

My question is: could the batch have been cleared even though the statement failed? Am I OK to simply retry the executeBatch line, or do I need to re-create the entire batch? And is this the same for non-batch statements?

---

In short, and more generally, is this a good way to handle transient exceptions?

statement.setString( 1, "Fred" );
statement.addBatch( );
statement.setString( 2, "Joe" );
statement.addBatch( );
for( int attempt = 0; ; attempt ++ ) {
    try {
        statement.executeBatch( );
        break;
    } catch( SQLTransientException ex ) {
        if( attempt >= 3 ) {
            throw ex;
        }
        try {
            Thread.sleep( attempt * attempt * 100 );
        } catch( InterruptedException ex2 ) {
            throw ex;
        }
    }
}

Answer 1

Since the API does not provide much information about the behavior when SQLTransientException happens apart from below

 when the driver has determined that the timeout value that was specified by the 
 setQueryTimeout method has been exceeded and has at least attempted
 to cancel the currently running Statement

Verified the Mysql JDBC4 Implementation .

In my observation, I could see there is one possible SQLTimeoutException (i.e SQLTransientException ) can happen if the timed out.

And while timed out , it is not just clearing the last statements , it is clearing entire Batch ( at least in MYSQL Implementation )

// we timeout the entire batch, not individual statements

So that part is clear.

And as per JDBC Specification

The statement’s batch is reset to empty once executeBatch returns

Irrespective of executeBatch throws exception / succeed it would clear the Batch .

Specifically Mysql Prepared Statement clears the batch at finaly block in executeBatch() method

finally {
        this.statementExecuting.set(false);
        clearBatch();
        }

So your logic will not work, since you are not adding the statements into batch again.

So add the statements back to batch and re-execute . there are highly possible you might end up in Timeout again. So first find the time out . if possible write some information about cause / state in the execution logs.