How to properly handle InnoDB deadlocks in Java/JDBC?

Question

I am working on a theory basis here, I want to make sure all my bases are covered.

I have read quite a bit into InnoDB with Java and how deadlocks can occur no matter what query you are running. Although I am pretty clued in with the theory and best practices, I am pretty much clueless on how to implement the catch all mechanism of reissuing transactions when a deadlock occurs.

Are there specific exceptions to listen out for? Is the exception only thrown after I call connection.commit() or can it occur as soon as I execute a PreparedStatement? Should things be running in a loop with a limit to how many times the loop runs?

I essentially just need a bare bones Java code example of how this thing is generally handled. As I am not sure where things factor in such as, do I re-instantiate PreparedStatement objects or close them first etc etc, it's all very confusing. Same goes for ResultSet objects too.

Edit: I should probably mention that I am working with transactions, setting auto commit to 0 etc.

Edit 2: Am I on the right track with this pseudo code? I have no clue

do
{
    deadlock = false

    try
    {
        // auto commit = 0
        // select query
        // update query
        // delete query
        // commit transaction
    }
    catch (DeadLockSpecificException e)
    {
        deadlock = true
    }
    finally
    {
        // close resources? statement.close(), resultset.close() etc?
        // or do I reuse them somehow and close them after the do/while loop?
        // this stuff confuses me a lot too
    }
}
while (deadlock == true);

Answer 1

Your code is essentially correct. The exception raised when a dead lock occurs is a SQLException. The exception's getSQLState() method provides returns an error code that provides additional information about the actual error.

You should also wait a short amount of time between attemps, so as not to load your server too much.

As you cleverly guessed, set a maximum number of attempts, or you might end-up in an infinite loop.

The final code could look like this:

boolean oops;
int retries = 5;
Connection c = null;
Statement s = null;
ResultSet rs = null;    

do
{
    oops = false;
    c = null;
    s = null;
    rs = null;
    try
    {
        c = openConnection();
        s = c.createStatement();
        rs = s.executeQuery("SELECT stuff FROM mytable");
        fiddleWith(rs);
    }
    catch (SQLException sqlex)
    {
        oops = true;
        switch(sqlex.getErrorCode()())
        {
            case MysqlErrorNumbers.ER_LOCK_DEADLOCK:
                // deadlock or lock-wait time-out occured
                break;
            ...
        }
        Thread.sleep(1000); // short delay before retry
    }
    finally
    {
        if (rs != null) try {
            rs.close();
        } catch (SQLException e) {
            // some error handler here
        }

        if (s != null) try {
            s.close();
        } catch (SQLException e) {
            // some error handler here
        }

        if (c != null) try {
            c.close();
        } catch (SQLException e) {
            // some error handler here
        }

    }
}
while (oops == true && retries-- > 0);

Obviously the above code is sub optimal. You may want to differentiate errors taking place at connection time and errors at execution time. You could also detect that after some errors, there is little hope that another attempt will work (eg. wrong credentials or SQL syntax error).

You asked a lot of questions, but I will try to answer them all:

Are there specific exceptions to listen out for?

Yes, see above: SQLException's are the ones, with more information provided by getErrorCode() or getSQLState().

Is the exception only thrown after I call connection.commit()?

A SQLException could be thrown by virtually all methods of all classes from the java.sql package.

Should things be running in a loop with a limit to how many times the loop runs?

Yes, see above.

Do I [need to] re-instantiate PreparedStatement objects?

Obviously you must not re-create a PreparedStatement between two queries. You just need to set new values to your parameters before calling executeQuery() again. Of course if you need to execute another query, then a new PreparedStatement is required.

Same goes for ResultSet objects too

A (new) ResultSet object is returned by Statement.executeQuery(), which represents the result of the query. You never create such an object yourself. Ideally you will call ResultSet.close() as soon as possible to release memory.

I strongly advise you to follow the second chapter of this tutorial ("Processing SQL Statements").