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:

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.