Database-side JDBC routines and SQLExceptions

It is possible to code database-side routines, like application-side methods, to catch SQLExceptions. SQLExceptions that are caught within a routine are hidden from the calling application code.

When such SQLExceptions are of transaction severity (such as deadlocks), this "hiding" of the exception causes unexpected problems.

This is because errors of transaction severity roll back work already done by a transaction (not just the piece executed by the called method) and silently begin a new transaction. When the method execution is complete, Derby detects that the outer statement was invalidated by a deadlock and rolls back any work done in the new transaction as well. This is the expected behavior, because all the statements in between explicit commits should be treated atomically; the new transaction implicitly begun by Derby's rollback was not intended by the application designer.

However, this is not the same behavior that would happen if the method were invoked in the application. In that situation, Derby would roll back the work done by the transaction and silently begin a new transaction. Work in the new transaction would not be rolled back when the method returned. However, coding the application in that way means that the transaction did not end where you expected it to and is probably a programming mistake. Coding in this manner is not recommended.

A method that catches a deadlock exception and then continues is probably making a mistake. Errors of transaction severity should be caught not by nested code, but only by the outermost application code. That is the only way to ensure that transactions begin and end where you expect them to.

Not all database vendors handle nested deadlocks the same way. For this and other reasons, it is not possible to write portable SQL-invoking methods. However, it is possible to write SQL-invoking methods that behave identically regardless of whether you invoke them in the application or as a routine in the database.

In order to ensure identical application- and database-side handling of nested errors, code try-catch blocks to check for the severity of exceptions as follows:

try {
    preparedStatement.execute();
} catch (SQLException se ) {
    String SQLState = se.getSQLState();
    if ( SQLState.equals( "23505" ) ) {
        correctDuplicateKey();
    } else if ( SQLState.equals( "22003" ) ) {    
        correctArithmeticOverflow();
    } else {
        throw se;
    }
}

Of course, users also have the choice of not wrapping SQL statements in try-catch blocks within methods. In that case, SQLExceptions are caught higher up in their applications, which is the desired behavior.