Test for Error Handling and Transaction Locking

SQL Server uses two main mechanisms to determine when to roll back transactions during error handling:

1. SET XACT_ABORT: This is a server configuration setting that controls the transaction behavior on encountering errors.
   • SET XACT_ABORT ON (default): This is the most common setting. Any runtime statement error within a transaction will cause the entire transaction to automatically rollback.
   • SET XACT_ABORT OFF: Errors are reported, but the transaction remains open and must be explicitly rolled back using the `ROLLBACK TRANSACTION` statement. This setting is less common as errors can leave the database in an inconsistent state.
2. TRY...CATCH Blocks (T-SQL): This is a T-SQL construct that allows you to define a code block and specify how to handle errors that might occur within it.
   • TRY Block: Contains the SQL statements you want to execute as part of the transaction.
   • CATCH Block: Executes if an error occurs within the TRY block. You can include logic here to handle the error gracefully, potentially including a `ROLLBACK TRANSACTION` statement to undo changes.

How They Work Together:

• SET XACT_ABORT is a global setting that defines the default behavior for all transactions on the server.
• TRY...CATCH Blocks provide a more granular way to handle errors within specific sections of your code. Even with `SET XACT_ABORT ON`, you can use a `CATCH` block to perform specific actions before the automatic rollback occurs.

Example:

-- Assuming SET XACT_ABORT is ON

BEGIN TRANSACTION;

UPDATE Customers SET Name = 'New Name' WHERE CustomerID = 10;

-- This statement might fail due to a constraint violation
INSERT INTO Orders (CustomerID, OrderDate) VALUES (15, '2023-01-01');

COMMIT TRANSACTION;  -- This won't execute if there's an error

-- Alternatively, handle the error with TRY...CATCH

BEGIN TRANSACTION;

TRY
  UPDATE Customers SET Name = 'New Name' WHERE CustomerID = 10;
  INSERT INTO Orders (CustomerID, OrderDate) VALUES (15, '2023-01-01');
CATCH
  -- Handle the error (e.g., log it or display a message to the user)
  ROLLBACK TRANSACTION; 	
 

Error handling is very important within transactions. Without handling errors, you would not know when to roll back transactions. It is important to know that if any statement in your transaction produces a severe error such that the transaction cannot be completed successfully, SQL Server automatically rolls back the transaction.

Assuming that the transaction was not automatically rolled back, you need to test for errors in your transactions. There are system-level global variables that are populated with values by SQL Server for this purpose. These are the variables that you can use:

1. @@ERROR:Gives the SQL Server error number of the last executed Transact-SQL statement. If there was no error, @@ERROR will be 0.
2. @@TRANCOUNT:Indicates the number of currently open transactions for a session. Every call to BEGIN TRANSACTION increments @@TRANCOUNT by 1. END TRANSACTION decrements @@TRANCOUNT by 1. @@ROLLBACK TRANSACTION resets @@TRANCOUNT to 0, unless a savepoint name is specified. In this case, @@TRANCOUNT is unaffected.
3. @@ROWCOUNT:Indicates the number of rows that are affected by the last executed Transact-SQL statement.

When errors occur, it is a good idea to generate an error message and send it back to the application that started the transaction. This is done by using the RAISERROR Transact-SQL statement. It follows this general syntax:

Applies To: SQL Server 2014, SQL Server 2016 Preview
Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a

TRY CATCH 

construct. New applications should use THROW instead.
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.

Because the RAISERROR Transact-SQL statement takes the last two arguments as the database ID and the database name, you can obtain the current database ID and the current database name by using the DB_ID() and DB_NAME() functions, respectively. However, you cannot use these directly in the RAISERROR Transact-SQL statement, so you must declare variables of the type that are returned by these functions, call those functions, and assign the return values to the variables that you declared. You can do that with this simple code (which you can use consistently in your procedures):

DECLARE @DBID SMALLINT
DECLARE @DBNAME NVARCHAR(128)
SET @DBID = DB_ID()
SET @DBNAME = DB_NAME()

Then you can use the @DBID and @DBNAME variables when needed, like this:

RAISERROR ("Error Text Here", 16, 1, @DBID, @DBNAME)

It is a good idea to implement some form of error handling to test if the individual Transact-SQL statements that are wrapped within the transaction fail. There are as many different ways to do this as you can think of. Here is one example of how you might implement error handling and rollback transactions when errors occur:

DECLARE @DBID SMALLINT
DECLARE @DBNAME NVARCHAR(128)
SET @DBID = DB_ID()
SET @DBNAME = DB_NAME()
BEGIN TRAN
UPDATE Finance
SET Salary = 100000
WHERE EmployeeID = 101
select @@rowcount
select @@trancount
IF @@ROWCOUNT = 0 or @@TRANCOUNT = 0
    BEGIN
 /* an error occurred in the first statement */
 ROLLBACK TRAN
        RAISERROR ("Finance failed - 
  Transaction rolled back",
  16, 1, @DBID, @DBNAME)
        RETURN
    END
UPDATE FinanceHistory
SET LastSalaryUpdate = "07/04/99"
WHERE EmployeeID = 101
IF @@ROWCOUNT = 0 or @@TRANCOUNT = 0
    BEGIN
 /* an error occurred in the second statement */
 ROLLBACK TRAN
        RAISERROR ("FinanceHistory failed - 
  Transaction rolled back", 16, 1, @DBID,
 @DBNAME)
        RETURN
    END
/* everything succeeded - commit the transaction */
COMMIT TRAN

Notice in the code above that not only does the code rollback the transaction if either of the two Transact-SQL statements contained within fail, but a detailed error message is displayed in either case. This way debugging the transaction is easy. This could have been taken one step further by displaying a different error message based on @@ROWCOUNT being 0 (indicating that the condition set in the WHERE clause produced no results) or the @@TRANCOUNT being 0 (indicating that the transaction had already been rolled back or committed by another process). The next lesson reviews the information covered in this module.

Click the Exercise link to practice handling errors within your transactions.
Handling Transaction Errors - Exercise