This chapter is from the book
This chapter is from the book
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.