TRY...CATCH blocks cannot catch all errors
Interestingly enough, sometimes TRY...CATCH
blocks just do not catch errors. This sometimes represents "expected
behavior;" in other words, the behavior is documented and the reason why
the error is not caught, for example when a connection fails, is
intuitive. However, in some other cases the behavior, while still
documented, can be quite surprising.
In either case, however, it
means that we cannot assume that all errors originating in the database
can, or will, be handled in a TRY...CATCH.
Whenever we issue an SQL statement from the client, we need to be aware
that it can generate an exception, and we need to be ready to handle it
on the client, in case the TRY...CATCH blocks that we use in our T-SQL code don't catch it.
Killed connections and timeouts
In some cases, it is the expected behavior that errors cannot be caught by TRY...CATCH blocks. For example, if your connection is killed, it is documented and well known that your CATCH block will not catch and handle it.
Also, we need to be aware of "attentions," also known as "timeouts," as they also cannot be caught by TRY...CATCH blocks, and this is also the expected behavior. To demonstrate this, start the script in Listing 5, but cancel its execution immediately by pressing the Cancel Executing Query button.
The execution stops immediately, without executing the CATCH block. Listing 6 demonstrates that the connection is still in the middle of an outstanding transaction.
If the client initiates a
timeout, the behavior is exactly the same: the execution stops
immediately, the outstanding transaction is neither committed nor rolled
back, and an unhandled exception is sent to the client. This is simply
how timeouts work, and the only way to avoid this behavior is to turn it
off altogether. For instance, we can turn off timeouts in ADO.NET by
setting the CommandTimeout property to 0. Of course, we can turn XACT_ABORT on, in which case at least the transaction will be rolled back. The CATCH block, however, will still be bypassed.
Problems with TRY...CATCH scope
In some cases, the behavior in TRY...CATCH is documented, but will be surprising to developers used to error handling in languages such as C#.
Listing 7 demonstrates a simple case of a query, wrapped in a TRY...CATCH, which tries to use a temporary table that does not exist. However, the CATCH block is not executed, and we get an unhandled exception.
Even more surprising for
object-oriented developers is that this is not a bug; it is just the way
SQL Server works in this case. According to MSDN for SQL Server 2008:
"Errors that occur during statement-level recompilation...are not handled by a CATCH block when they occur at the same level of execution as theTRY...CATCH construct."
The issue here is that
compilation errors that occur at run time (as a result of deferred name
resolution) abort the rest of the scope, which is equal to the batch in
directly submitted SQL, but only equal to the rest of the procedure in a
stored procedure or function. So a TRY...CATCH at the same scope will not intercept these errors, but a TRY...CATCH on a different scope (regardless of being nested or not) will catch it.
My point here is simple: SQL
Server does not always handle errors in a way object-oriented languages
do. If we choose to use the error handling provided by SQL Server, we
really need to learn it in detail or we will be in for some unpleasant
surprises.
Doomed transactions
There is another serious problem with T-SQL TRY...CATCH blocks: in some cases an error that occurred inside a TRY
block is considered so severe that the whole transaction is doomed, or,
in other words, it cannot be committed. Theoretically, the concept of
doomed transactions makes perfect sense. Unfortunately, some really
trivial errors, such as conversion errors, render transactions doomed if
we use TRY...CATCH provided by T-SQL. For example, consider the transactions shown in Listing 8.
The first attempts to perform a 1/0 calculation, and the second, to
convert a strong to an integer. We do not want to roll back the whole
transaction if an error occurs, so we set XACT_ABORT to OFF.
As the output
demonstrates, we can commit a transaction after a divide by zero, but a
conversion error renders the transaction doomed, and therefore
uncommitable. The latter case demonstrates that even a seemingly trivial
conversion error is considered severe enough to override the XACT_ABORT setting, and the whole transaction is automatically rolled back.
To determine whether or not our transaction is committable, within TRY...CATCH, we can use the XACT_STATE() function, as demonstrated in Listing 9.
Clearly, there are situations
where the concept of a doomed transaction makes sense. For example, if
the server runs out of disk space while running a transaction, there is
no way the transaction could complete. Unfortunately, the current
implementation of SQL Server sometimes dooms transactions for very
trivial reasons. In all too many cases, this peculiar behavior of SQL
Server makes it impossible to develop feature-rich error handling in
T-SQL because, if a transaction is doomed, we have no choice other than
to roll it back.
We will not cover any examples here, but this can also cause problems when attempting to use SAVEPOINTs. Consider the following, very common, requirement:
"If
our stored procedure is invoked in the middle of an outstanding
transaction, and if any command in our stored procedure fails, undo only
the changes made by the stored procedure. Do not make any decisions
regarding the changes done outside of our stored procedure."
Unfortunately, there is no robust way to implement such requirements in T-SQL using a SAVEPOINT. While it will work in most cases, it will not work as intended when a transaction is doomed.
