programming4us
programming4us
DATABASE

Defensive Database Programming with SQL Server : TRY...CATCH Gotchas (part 2) - TRY...CATCH blocks cannot catch all errors

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
1/15/2011 9:15:41 AM

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.

Listing 5. TRY...CATCH behavior when a timeout occurs.

The execution stops immediately, without executing the CATCH block. Listing 6 demonstrates that the connection is still in the middle of an outstanding transaction.

Listing 6. The connection is 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.

Listing 7. Sometimes a CATCH block is bypassed when an error occurs.

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.

Listing 8. A transaction is doomed after a trivial error such as a conversion error.

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.

Listing 9. Using xact_state to determine if our transaction is committable or doomed.

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.

Other  
  •  SQL Server 2008 : Working with DML Queries - Using the INSERT Statement (part 2)
  •  SQL Server 2008 : Working with DML Queries - Using the INSERT Statement (part 1) - Using the INSERT Statement with the VALUES Clause
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 7) - Nested Tables
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 6) - Validating and Comparing Mining Models
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 5) - Viewing Mining Models
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 4) - Deploying and Processing Data Mining Objects
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 3) - Editing and Adding Mining Models
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 2) - Creating a Mining Model
  •  Programming Microsoft SQL Server 2005: Using the Data Mining Wizard and Data Mining Designer (part 1) - Creating a Mining Structure
  •  Microsoft SQL Server 2005 : Report Management
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

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

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