Programming applications to handle deadlocks

When you configure your system for deadlock and lockwait timeouts and an application could be chosen as a victim when the transaction times out, you should program your application to handle them.

To do this, test for SQLExceptions with SQLStates of 40001 (deadlock timeout) or 40XL1 or 40XL2 (lockwait timeout).

In the case of a deadlock you might want to re-try the transaction that was chosen as a victim. In the case of a lock wait timeout, you probably do not want to do this right away.

The following code is one example of how to handle a deadlock timeout.

/// if this code might encounter a deadlock, 
// put the whole thing in a try/catch block
// then try again if the deadlock victim exception 
// was thrown
try {
    s6.executeUpdate(
         "UPDATE employee " +
         "SET bonus = 625 "
         "WHERE empno='000150'");
    s6.executeUpdate("UPDATE project " +
         "SET respemp = '000150' " +
         "WHERE projno='IF1000'");
} 
// note: do not catch such exceptions in database-side methods; 
// catch such exceptions only at the outermost level of 
// application code. 
// See Database-side JDBC procedures and SQLExceptions.  
catch (SQLException se) { 
    if (se.getSQLState().equals("40001")) { 
        // it was chosen as a victim of a deadlock. 
        // try again at least once at this point. 
        System.out.println( "Will try the transaction again."); 
        s6.executeUpdate("UPDATE employee " + 
        "SET bonus = 625 " + 
        "WHERE empno='000150'"); 
        s6.executeUpdate("UPDATE project " + 
        "SET respemp = 000150 " + 
        "WHERE projno='IF1000'"); 
    } 
    else throw se; 
}