Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

Transaction Abort Handling

Error handling in previous versions of SQL Server has always been seen as somewhat arcane, compared with other procedural languages. You had to have error handling code after each statement, and to have centralized handling of errors, you need GOTO statements and labels. SQL Server 2005 introduces a modern error handling mechanism with TRY/CATCH blocks. The syntax follows.

BEGIN TRY 
  { sql_statement | statement_block }
END TRY
BEGIN CATCH TRAN_ABORT
  { sql_statement | statement_block }
END CATCH

The code you want to execute is placed within a TRY block. The TRY block must be immediately followed by a CATCH block in which you place the error handling code. The CATCH block can only handle transaction abort errors, so the XACT_ABORT setting needs to be on in order for any errors with a severity level less than 21 to be handled as transaction abort errors. Errors with a severity level of 21 or higher are considered fatal and cause SQL Server to stop executing the code and sever the connection.

When a transaction abort error occurs within the scope of a TRY block, the execution of the code in the TRY block terminates and an exception is thrown. The control is shifted to the associated CATCH block. When the code in the CATCH block has executed, the control goes to the statement after the CATCH block. TRY/CATCH constructs can be nested, so to handle exceptions within a CATCH block, write a TRY/CATCH block inside the CATCH. The following code shows a simple example of the TRY/CATCH block.

—make sure we catch all errors
SET XACT_ABORT ON
BEGIN TRY
 —start the tran
 BEGIN TRAN
 —do something here
 COMMIT TRAN
END TRY
BEGIN CATCH TRAN_ABORT
 ROLLBACK
 —cleanup code
END CATCH 

Notice how the first statement in the CATCH block is the ROLLBACK. It is necessary to do the ROLLBACK before any other statements that require a transaction. This is because SQL Server 2005 has a new transactional state: "failed" or "doomed." The doomed transaction acts like a read-only transaction. Reads may be done, but any statement that would result in a write to the transaction log will fail with error 3930:

Transaction is doomed and cannot make forward progress. Rollback Transaction.

However, work is not reversed and locks are not released until the transaction is rolled back.

We mentioned previously that errors had to be transactional abort errors in order to be caught. This raises the question: What about errors created through the RAISERROR syntax—in other words, errors that you raise yourself? In SQL Server 2005, RAISERROR has a new option called TRAN_ABORT, which tags the raised error as a transactional abort error, which therefore will be handled in the CATCH block.

  • + Share This
  • 🔖 Save To Your Account