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
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8