To handle errors in T-SQL modules, in SQL Server 2005 and upwards, we can use TRY...CATCH blocks. If any command inside the TRY block raises an error, the execution of the TRY block terminates immediately, which is similar to the behavior under the XACT_ABORT setting. But, unlike with XACT_ABORT, where the whole batch terminates, only the execution of the code inside the TRY block terminates, and the CATCH block begins to execute.
In cases where you are aware that a certain specific error could occur,
your error-handling strategy can be different. You may attempt to add
code to your CATCH block that corrects the error, or at least allows processing to continue. In these cases, it makes more sense to have XACT_ABORT set to OFF, so that you can handle the errors, and inform the calling client of what happened, without rolling back the entire batch.
As will become clear as we
progress, my current philosophy is that all but the simplest error
handling should ideally be implemented in a client-side language where
the error handling is more robust and feature rich than it is in SQL
Server TRY...CATCH.
My goal here is, therefore, not to cover TRY...CATCH
in full detail, but to set out, with examples, some of the reasons why
error handling in T-SQL can be complex and a little bewildering. I
really want to encourage you to either fully understand all the ins and
outs of T-SQL error handling, or to avoid using it at all, except in the
simplest cases.
Erland Sommarskog's website, HTTP://WWW.SOMMARSKOG.SE/, is an excellent source of information on error handling. The book entitled Expert SQL Server 2005 Development by Adam Machanic, Hugo Kornelis, and Lara Rubbelke is another great resource.
Finally, note that I do not cover "old-style" error handling using @@ERROR at all in this chapter. Use of @@ERROR
has some well-known problems, such as the inability to handle errors
raised by triggers, and the fact that sometimes SQL Server simply fails
to set its value correctly. In general, my advice would be to upgrade
from @@ERROR to TRY...CATCH or, even better, to client-side error handling for all but the simplest cases, as soon as possible.
A TRY...CATCH example: retrying after deadlocks
Sometimes, it may make sense to use TRY...CATCH blocks to retry the execution of a statement after a deadlock. One must exercise caution when doing so, as retrying an UPDATE statement in this manner may lead to lost updates, as we discuss in detail in Chapter 10, Surviving Concurrent Modifications.
The defensive programmer must take all possible measures to ensure that
the possibility of deadlocks is minimized but, in some cases, it may be
deemed acceptable, in the short term at least, to automatically retry
after a deadlock.
In order to provide an example that you can run on your server, we'll alter our ChangeCodeDescription stored procedure, as shown in Listing 1,
so that it is highly likely to be chosen as a deadlock victim, if it
embraces in a deadlock with a competing session. Our goal here is not to
demonstrate how to develop stored procedures that are unlikely to
embrace in deadlocks, but to see how to use a TRY...CATCH block to retry after a deadlock.
If processing switches to our CATCH
block, we will attempt to re-execute our transaction once more, in
response to a deadlock; otherwise we will simply re-throw the error so
that the calling client is notified and can respond.
Before we run our test, let's reset the test data in our Codes and CodeDescriptions-ChangeLog tables.
We're now ready to run the test. From one tab in SSMS, we'll start a SERIALIZABLE transaction against the CodeDescriptionsChangeLog table, as shown in Listing 3.
From a second tab, invoke our stored procedure, as shown in Listing 4. The session will "hang" in lock waiting mode, due to our SERIALIZABLE transaction accessing the CodeDescriptionsChangeLog table.
Now return to Tab 1, and execute the commented UPDATE against the Codes table, from Listing 3, including the COMMIT.
As soon as this code tries to execute, a deadlock is detected. SQL
Server chooses our stored procedure execution from Tab 2 as the deadlock
victim, since we deliberately contrived for this to be the case. The
transaction in our TRY block is rolled back, but then our CATCH
block is executed and we try to execute our stored procedure again.
This time, since Tab 1 has now committed, the modification succeeds. The
output from Tab 2 is shown in Listing 5.
Note also, however, that the UPDATE we execute from Tab 1 is "lost;" its changes were overwritten when the retry succeeded.
From these examples, we have learned the following:
if several
modifications must succeed or fail together, use transactions, and roll
the modification back, as a unit, if any one of them fails
always anticipate that any modification may fail; use XACT_ABORT to ensure that transactions roll back after a failure; alternatively, we can wrap our transactions in TRY blocks, and roll them back in CATCH blocks.
Unfortunately, there are a few problems with using TRY...CATCH error handling that we need to discuss. In the next section, we'll look at some ways in which TRY...CATCH
error handling is limited and its behavior surprising. We'll then see
what we can achieve when using C# for error handling, instead of T-SQL.
