Programming4us
         
 
 
SQL Server

Defensive Error Handling : Using Transactions and XACT_ABORT to Handle Errors

11/18/2010 7:27:38 PM
In many cases, we do not need sophisticated error handling. Quite frequently, all we need to do in case of an error, is roll back all the changes and throw an exception, so that the client knows that there is a problem and will handle it. In such situations, a perfectly reasonable approach is to make use of the XACT_ABORT setting.

By default, in SQL Server this setting is OFF, which means that in some circumstances SQL Server can continue processing when a T-SQL statement causes a run-time error. In other words, for less severe errors, it may be possible to roll back only the statement that caused the error, and to continue processing other statements in the transaction.

If XACT_ABORT is turned on, SQL Server stops processing as soon as a T-SQL run-time error occurs, and the entire transaction is rolled back. When handling unexpected, unanticipated errors, there is often little choice but to cease execution and roll back to a point where there system is in a "known state." Otherwise, you risk seeing partially completed transactions persisted to your database, and so compromising data integrity. In dealing with such cases, it makes sense to have XACT_ABORT turned ON.

Data modifications via OLE DB

Note that, in some cases, XACT_ABORT is already set to ON by default. For example, OLE DB will do that for you. However, it is usually preferable to explicitly set it, because we do not know in which context our code will be used later.


Listing 1 illustrates a basic error-handling approach, whereby our modifications take place within an explicit transaction, having set XACT_ABORT to ON. The PRINT commands in the procedure are for demonstration purposes only; we would not need them in production code.

Listing 1. Using the XACT_ABORT setting and an explicit transaction.

Note that, although we want to roll back all the changes if an error occurs, we do not need to explicitly determine if there are any errors, and we do not need to explicitly invoke ROLLBACK in our code; when XACT_ABORT is set to ON, it all happens automatically. Listing 2 tests our altered stored procedure.

Listing 2. Testing the altered stored procedure.

As we have seen, the stored procedure worked perfectly well. Of course, this is just the first in a series of tests we should perform on our stored procedure.

Complete testing would include:

  • making sure that, if both the modification of the Codes table and the INSERT into the CodeDescriptionsChangeLog table succeed, then the transaction commits and both changes persist

  • verifying that, if an UPDATE of the Codes table fails, then the transaction rolls back. To reproduce a failure, we can use a similar technique; a CHECK constraint that makes sure all UPDATEs against the Codes table fail

  • invoking the stored procedure without an outstanding transaction, when @@TRANCOUNT is 0. In that case, we shall have to explicitly drop the CHECK constraint which we create in our test.

I encourage you to tweak Listing 2 and try out these other tests. In many cases, this simple approach of setting XACT_ABORT to ON and using an explicit transaction for modifications gets the job done without much effort. We should use this simple and robust approach unless we really need more sophisticated functionality from our error handling.

If we really want to do some more complex error handling on the server, using T-SQL, then we should use TRY...CATCH blocks, which are available in SQL Server 2005 and upwards.

Other -----------------
- Managing Security Within the Database Engine : Securables
- Managing Security Within the Database Engine : Database Security
- Managing Security Within the Database Engine : Creating SQL Server Principals
- SQL Server 2008 : Performance Tuning - Locks, Blocking, and Deadlocks
- SQL Server 2008 : Performance Tuning - Tracing
- SQL Server 2008 : Implementing Error Handling - Managing and Raising User-Defined Errors
- SQL Server 2008 : Implementing Error Handling - Understanding Errors
- Implementing SQL Server Objects Using Managed Code (part 2)
- Implementing SQL Server Objects Using Managed Code (part 1)
- Encryption Catalog Views
- Built-In Cryptographic Functions
- SQL server 2008 : Managing Security - Permissions
- SQL server 2008 : Managing Security - Schemas
- SQL server 2008 : Managing Security - Users
- SQL server 2008 : Managing Security - Roles
- SQL Server 2008 : Managing Remote Servers
- Linked Servers
- Adding, Dropping, and Configuring Linked Servers
- Mapping Local Logins to Logins on Linked Servers
- Obtaining General Information About Linked Servers
 
 
Most View
- Windows 7 : Thwarting Spam with Windows Live Mail’s Junk Filter (part 1)
- Performing On-Demand Exchange Server 2003 Monitoring and Maintenance
- SharePoint 2010 : Site Administration - Adding a column with the property of Managed Metadata
- Microsoft Visual Studio 2010 : Reports and Debugging - Using the Parallel Tasks Window
- Windows 7 : Getting Older Programs to Run - Using the Program Compatibility Wizard
- SQL Server 2012 : T-SQL Enhancements - The MERGE Statement (part 1)
- Windows Phone 7: Finding a Contact
- Windows 7 : Controlling and Customizing Your Website (part 2) - Setting the Website’s Default Document
- SharePoint 2010 : Authoring Pages - Create a New Page (part 1)
- jQuery 1.3 : Headline rotator (part 2) - Retrieving the feed
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS