Defensive Database Programming with SQL Server : Using TRY...CATCH blocks to Handle Errors

1/10/2011 11:23:33 AM
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.

Listing 1. Altering the ChangeCodeDescription stored procedure so that it retries after a deadlock.

Before we run our test, let's reset the test data in our Codes and CodeDescriptions-ChangeLog tables.

Listing 2. Resetting the test data.

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.

Listing 3. Tab 1, start a transaction against the CodeDescriptionsChangeLog table.

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.

Listing 4. Tab 2, invoke the ChangeCodeDescription stored procedure.

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.

Listing 5. Tab 2, output from execution of the stored procedure.

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.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone