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);

Kempe

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").

Eckhart

When I asked Is the exception only thrown after I call connection.commit()?, I meant specifically for the exception thrown when a deadlock occurs. Can a deadlock error only happen once I've called connection.commit() or can it happen the second I execute an update or query, even though I have autocommit disabled? – Kempe

Yes, a lock may be required at any time during the course of a transaction (eg. SELECT stuff FROM mytable FOR UPDATE blocks until an exclusive lock is acquired on mytable). Therefore a deadlock may happen at any time. More information here. – Eckhart

Would you say this is more or less on the right track? paste2.org/30txAnkX I work with limited threads so I don't really want to hold one hostage with a thread sleep call. So besides the wait, does this seem correct? – Kempe

Your code looks okay. I strongly advise a slight wait between attempts, because if a deadlock happens, then it is likely that it happens again in the near future. But you know better than me the typical duration of a transaction in your application. – Eckhart

Actually it's usually the opposite (if my understanding is correct) as it's essentially 2 transactions in a deadlock. One gets to continue as normal, the other suffers a deadlock exception. Therefor the transaction that suffered the deadlock is safe to retry because the winning transaction already got the lock it was wanting. Anyway, thank you very much for the help! Enjoy the bounty. – Kempe

Yes, I had lock-wait time-out in mind. Less likely to apply to a dead lock you are right. – Eckhart

I am worried if there is any impact of closing the statement and result only after the Thread.sleep, do you know if it's safe? – Ugo