Test SQL Server Error Handling and Transaction Locking

SQL Server can roll back work at two different scopes:

1. Statement rollback (default behavior in many cases): SQL Server rolls back only the statement that failed, and the transaction can remain open. If your code ignores the error and continues, you can accidentally commit a partial business operation.
2. Transaction rollback: the entire transaction is rolled back (either automatically for certain errors, or intentionally by your error handling logic). This is what you typically want for “all-or-nothing” business rules.

Two settings/patterns control how deterministic this becomes:

• TRY...CATCH: gives you a controlled place to handle errors, roll back if needed, log details, and rethrow.
• SET XACT_ABORT ON (commonly used in stored procedures): makes many run-time statement errors automatically abort the batch and roll back the current transaction. This reduces the odds of “partial commits,” but you still need a correct CATCH block to clean up and return consistent error output.

A key testing concept is the transaction state after an error. In SQL Server, errors can leave a transaction either: committable (you may still commit) or uncommittable (you must roll back). Your CATCH block should check XACT_STATE() before deciding what to do.

Older T-SQL code often checks global variables immediately after each statement. These still exist, but modern SQL Server code typically prefers TRY...CATCH plus the ERROR_* functions and XACT_STATE().

Legacy global variables (still supported)

1. @@ERROR: error number from the most recently executed statement (0 means “no error”). This value is easy to overwrite accidentally if you run another statement before checking it.
2. @@TRANCOUNT: number of open transactions in the current session. A ROLLBACK (without a savepoint) resets this to 0.
3. @@ROWCOUNT: number of rows affected by the last statement. Capture it immediately after the statement you are testing.

Modern tools used in testing (recommended)

• TRY...CATCH with ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), and ERROR_PROCEDURE() in the CATCH block.
• XACT_STATE() to determine whether the transaction can be committed (1), is uncommittable and must be rolled back (-1), or there is no active transaction (0).
• THROW to raise or rethrow an error consistently (preferred for new development).

In production systems, tests should also validate that transactions do not remain open after an exception. Open transactions are a common cause of blocking, lock escalation surprises, and “mystery” performance incidents.

RAISERROR (message_ID | message_string [, severity, state]
    [, argument [,...n]])
    [WITH option [,...n]];

RAISERROR (message_ID | message_string [, severity, state]
    [, argument [,...n]])
    [WITH option [,...n]];

A frequent misunderstanding in legacy examples is treating the extra arguments to RAISERROR as special parameters (such as “database ID and database name”). They are not special parameters. They are formatting arguments that populate placeholders in the message text.

If you want database context in the message, capture it and format it explicitly:

DECLARE @DbId INT = DB_ID();
DECLARE @DbName SYSNAME = DB_NAME();

RAISERROR (N'Database %d (%s): business rule failed.', 16, 1, @DbId, @DbName);

In new development, you can also build a message and use THROW:

DECLARE @Msg NVARCHAR(4000) =
  CONCAT(N'Database ', DB_NAME(), N': business rule failed.');

THROW 50001, @Msg, 1;

The following template is a practical baseline for testing transaction behavior. It demonstrates: (1) deterministic rollback, (2) correct rowcount checks, (3) rethrowing the original error, and (4) avoiding open transactions that cause blocking.

SET NOCOUNT ON;
SET XACT_ABORT ON;  -- common in stored procedures

BEGIN TRY
    BEGIN TRAN;

    UPDATE dbo.Finance
    SET Salary = 100000
    WHERE EmployeeID = 101;

    IF @@ROWCOUNT = 0
        THROW 50010, 'Finance update failed: EmployeeID not found.', 1;

    UPDATE dbo.FinanceHistory
    SET LastSalaryUpdate = CONVERT(date, '1999-07-04')
    WHERE EmployeeID = 101;

    IF @@ROWCOUNT = 0
        THROW 50011, 'FinanceHistory update failed: EmployeeID not found.', 1;

    COMMIT TRAN;
END TRY
BEGIN CATCH
    -- If a transaction exists, roll it back safely.
    IF XACT_STATE() <> 0
        ROLLBACK TRAN;

    -- Option A: rethrow the original error details.
    THROW;

    -- Option B (legacy): rethrow with RAISERROR using ERROR_*() functions.
    -- DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
    -- DECLARE @ErrSeverity INT = ERROR_SEVERITY();
    -- DECLARE @ErrState INT = ERROR_STATE();
    -- RAISERROR (@ErrMsg, @ErrSeverity, @ErrState);
END CATCH;

Testing guidance:

• Force a predictable failure (for example, use an EmployeeID that does not exist) and confirm the transaction rolls back and no locks remain.
• Validate the error returned to the application includes enough context to troubleshoot (message, procedure, and line number).
• If you are testing concurrency and locking, keep one session open in a transaction and verify blocking behavior from a second session. Make sure you always roll back/commit at the end of the test to release locks.

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